1.11M Members

Multiple Drop Down with Dynamic List (PHP, MySql, AJAX)

 
0
 

I have tried many different scripts I have found but cannot find one that does exactly what I am looking for.

I have a table that has parts with multiple characteristics. Part Number, Height, Width, Capacity, Price, Etc. I would like to have a drop downs for all of these values, when a user selects one of these values it will filter the results into a list.

Ex. user selects a part that has a Height of 6 and width of 10 it will only list those parts.

As I said earlier, I have a script that will give me all the parts, however I am not able to make the onchange list parts with multiple values.

Any help and code examples would be greatly appreciated.

 
0
 

Pass each selection as an argument to your SQL query.

When any of the dropdownlists selections change, run the query and bind the results to the list.

 
0
 

Rook,

Client-side (javascript & HTML) aspects are pretty trivial especially if you use jQuery, which I recommend for an application like this. jQuery will make the code much more concise and (ultimately) more readable.

Making a request to the server will be a simple matter of sending (via ajax) the serialized form, containing name|value pairs for your drop down menus. On ajax success, simply replace the innerHTML of a <tbody> element with the HTML response from the server-side script.

The challenges are twofold:

  • Obviating the need to do a round trip to the server every time a menu changes. It should be possible, under some circumstances, to manipulate the current list (show/hide rows) rather than ask the server to regenerate. This will make the interface more responsive. I can help with this.
  • Writing the server-side code

NOTE: Only client-side aspects here. For server-side, ask in appropriate Daniweb forum (eg. PHP).

Airshow

 
0
 

Thanks for the replies. It gives me a place to start. I really have not done anything with jQuery or AJAX, but I will look in to it.

As for hits to the server, I am not concerned with this as there will probably ever be only one user at a time using this page.

I know I am asking the world here, but if one of you could write a quick mock-up of such a script that would be extremely helpful. As I said earlier, I really have no jQuery or AJAX experience.

Once more, thanks again.

 
0
 

Rook,

The emphasis in the point about obviating round trips to the server is on interface responsiveness, not server load, though this will also benefit.

Here's a draft demo:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Multi-criterion search :: by Airshow</title>
<style>
</style>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>
<script type='text/javascript'>
$(function(){

	/*
	 * Rules for constructing search result in php :
	 *   returned result must be a string comprising a set of HTML table rows (<tr>), with cells (<td>) containing data.
	 *   each cell's class must be identical to its corresponding menu id.
	 *   each cell's contents must be idential to its corresponding menu value (without additional markup).
	 */

	var $selectionForm = $("#selectionForm");
	var $menus = $selectionForm.find("select");
	var $itemList = $("tbody#itemList");//should be a tbody not a whole table.
	var $sorry = $("tbody#sorry");
	var wildcard = '*';//Make sure each menu has an "Any" option with value="*"
	var maxResults = 100;//the maximum number of results the server will return.
	
	function constrainList($rows){
		//Show all current rows
		$rows.show();
		//Selectively hide rows if they don't match any of the current menu selections.
		$rows.each(function(i){
			var $row = $(this);
			$menus.each(function(j){
				var $m = $(this);
				var id = $m.attr('id');
				if( $row.find("td."+id).html() !== $m.val() ){
					$row.hide();//row doesn't match current selection, so hide it.
					return false;//no need to look further; break out of inner loop.
				}
			});
		});
	}
	
	function setList(){
		//Scan menus to test whether current selections are compatible with last served list.
		var match = true;
		$.each($menus, function(i){
			var $m = $(this);
			var lastServed = $m.data('lastServed');
			match = match && lastServed && ( lastServed===wildcard || lastServed===$m.val() );
		});
		var $rows = $itemList.find("tr");
		if(match && $rows.length > 0 && $rows.length < maxResults){
			//manipulate last served list to show/hide items as apprioriate
			constrainList($rows);
		}
		else{
			//go to server (ajax) for new list
			$.ajax({
				url: 'xxxx.php',
				type: 'GET',
				cache: false,
				data: $("#selectionForm").serialize(),
				dataType: 'html',//if 'xml', 'json' or 'jsonp' then success function needs to be modified
				success: function(data, textStatus, jqXHR){
					//replace old list with new
					$itemList.html(data);
					$sorry.css($itemList.find("tr").length ? '' : 'none');
					//remember current menu values
					$.each($menus, function(i){
						var $m = $(this);
						$m.data('lastServed', $m.val());
					});
				},
				error: function(jqXHR, textStatus, errorThrown){},
				complete: function(jqXHR, textStatus){}
			});
		}
		return false;//suppress conventional form submission
	}
	
	$selectionForm.submit(setList);
});
</script>
</head>

<body>

<h1>Multi-criterion search</h1>
<h2>A hybrid approach by Airshow</h2>

<form id="selectionForm" style="margin:10px 0;">
	<table border>
	<thead>
	<tr>
	<th>Factor 1</th>
	<th>Factor 2</th>
	<th>Factor 3</th>
	</tr>
	</thead>
	<tbody>
	<tr>
	<td><select id="factor_0">
		<option value="*">Any</option>
		<option value="Item 0_0">Item 0_0</option>
		<option value="Item 0_1">Item 0_1</option>
		<option value="Item 0_2">Item 0_2</option>
	</select></td>
	<td><select id="factor_1">
		<option value="*">Any</option>
		<option value="Item 1_0">Item 1_0</option>
		<option value="Item 1_1">Item 1_1</option>
		<option value="Item 1_2">Item 1_2</option>
	</select></td>
	<td><select id="factor_0">
		<option value="*">Any</option>
		<option value="Item 2_0">Item 2_0</option>
		<option value="Item 2_1">Item 2_1</option>
		<option value="Item 2_2">Item 2_2</option>
	</select></td>
	</tr>
	</tbody>
	</table>
</form>

<table border cellpadding="3">
<thead>
<tr>
<th>Column 1</th>
<th>Column 2</th>
<th>Column 3</th>
</tr>
<thead>
<tbody id="sorry">
<td colspan="3">No items</td>
</tbody>
<tbody id="itemList"></tbody>
</table>

</body>
</html>

Comments in code give rules as to how the server's search script should construct it's output to be compatible with this client-side code.

Must rush - work beckons.

Airshow

 
0
 

WOW! Airshow! That is way more that what I hoped for. I totally did not expect you to write 141 lines of code for me. Very much appreciated.

I will test this code tonight.

Thanks again.

 
0
 

Rook,

It's untested apart from checking for syntax errors.

You will need at least ...

  • to add a submit button to the form. I forgot it.
  • to set the url in the ajax function to point to your server-side script (eg. 'presents_search.php'), or to a hand-coded .html page to get yourself started. Whether it's hand-coded or script-generated, ensure the HTML obeys the rules I outlined as a js comment.
  • The third select menu should be id="factor_2"

The code is reasonably tricky in places so I doubt it will work first time.

Once you have generated some HTML, maybe you could post it here so (a) I can check it and (b) it will save me the trouble of writing a test file myself.

Not php though, I don't have the means of running it, nor a database.

Airshow

 
1
 

I've made a few tweeks.

Latest version attached.

Airshow

Attachments index_(1).zip (1.91KB)
 
0
 

I happened upon this thread while investigating doing something similar. I commonly return search result sets that I need to filter with multiple menus and have, to date, relied on using dynamic javascript menus with page reloads. My data sets aren't huge so it has been fine.

I will definitely give this code a good look and see if I can get it working.

BTW -- I am new to Ajax an jQuery and wonder if you can recommend any good tutorials or resources that discuss this very type of functionality. I have built a variety of CMS's in PHP/MySQL (mostly CRUD functionality) and really want to dig into Ajax and jQuery for CMS applications.

Thanks!

 
0
 

drP,

My updated version above appears to work. It just needs the server-side stuff to make it useful.

I don't know of any tutorials that address this type of functionality specifically but the web abounds with good material on ajax in general (with & without jQuery).

Airshow

 
0
 

Hi Airshow

Saw this helpful code of yours but can't seem to make it work with what I have, I think the code written for my form is quite inefficient. Any idea how I can adapt your code to my form?

http://www.autoindustriya.com/car/id/511/

 
0
 

dbludvl,

I don't think my code is applicable to your type of search.

This is all about contraining, where possisble, a previously served list of search results, in preference to requesting another set of search results, thus economising on the number of round trips to the server.

If I understand corectly, your search only ever returns a single result (ie a particular vehicle) and is thus not amenable to being constrained.

 
0
 

Hi Airshow

Just wondering if you could advise me on this as I have looked at your code and want to use it in my site but because i am new to coding slightly confused

I have built a triple dropdown menu for country county and city and would like that the list only shows the country county and cities which have a result depending on the user choice via the drop down menus and not all country county and cities like it does now

I used http://roshanbh.com.np/2008/01/populate-triple-drop-down-list-change-options-value-from-database-using-ajax-and-php.html

however i would like to add your code so I can limit the results.

Question is does this has to be done via php and mysql easily or does it have to de done via ajax?

 
0
 

To everyone,

What the code above does:
In response to selections made in one or more dropdown menus which collectively define the criteria for a server-side search, it constrains, where possisble, a previously served list of search results, in preference to requesting another set of search results, thus improving on client-side responsiveness and economising on the number of round trips to the server.

What the code above does not do:
It does not affect the options in dropdown menus. Thus it is not a solution for "cascading dropdows", which is the subject of one of the most commonly asked javascript/dhtml/jQuery questions here on Daniweb and elsewhere. A Google/Alta Vista/Yahoo etc serach should reveal more than enough erudite discusion and worked examples to get you going with cascading dropdows.

Airshow

 
0
 

I have 3 dropdown for selecting country,state and city .im using code igniter framework.im able to select country state but dropdown is not populating city dropdown can u please help what is the problem in jquery.

<?php
$this->load->helper('html'); 
?>

<html>
<head>
    <title>Buscador</title>
    <script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $('#my_select').change(function () {
                var selectVal = $('#my_select :selected').val();
                alert(selectVal);
                console.log(selectVal);
                $.ajax({
                    url: "welcome/drpst",
                    async: false,
                    type: "POST",
                    data: "stt="+selectVal,
                    dataType: "html",

                    success: function(data) {
                        $('#st').html(data);
                    }
                })
            });
        });
    </script>
    <script type="text/javascript">
        $(document).ready(function () {
            $('#my_select2').change(function () {
                var selectVal2 = $('#my_select2 :selected').val();
                alert(selectVal2);
                console.log(selectVal2);
                $.ajax({
                    url: "welcome/drpct",
                    async: false,
                    type: "POST",
                    data: "ctt="+selectVal2,
                    dataType: "html",

                    success: function(data) {
                        $('#ct').html(data);
                    }
                })
            });
        });
    </script>
</head>

<form action="drpdwn" method="post">


<select id="my_select"><option value='India'>India</option><option value='USA'>USA</option><option value='China'>China</option><option value='Australia'>Australia</option></select>


<div id="st"><select id="my_select2"><option value=''></option></select></div>
<div id="ct"><select><option value=''></option></select></div>
</form>
</body>
</html>
You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: