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

Question

rook0316 0 Newbie Poster

13 Years Ago

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.

html-css javascript mysql php

7 Contributors
14 Replies
8K Views
1 Year Discussion Span
Latest Post 11 Years Ago Latest Post by pankaj12290

stbuchok

13 Years Ago

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.

Airshow 416 WiFi Lounge Lizard

13 Years Ago

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

Airshow 416 WiFi Lounge Lizard

13 Years Ago

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

Airshow 416 WiFi Lounge Lizard

13 Years Ago

I've made a few tweeks.

Latest version attached.

Airshow

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

index_(1).zip (1.91 KB)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

rook0316 0 Newbie Poster · Answer 1 · 2011-08-03T06:28:02+00:00

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.

rook0316 0 Newbie Poster · Answer 2 · 2011-08-03T21:00:17+00:00

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.

Airshow 416 WiFi Lounge Lizard Team Colleague · Answer 3 · 2011-08-03T22:16:43+00:00

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

drpudding 0 Newbie Poster · Answer 4 · 2011-08-05T11:54:10+00:00

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!

Airshow 416 WiFi Lounge Lizard Team Colleague · Answer 5 · 2011-08-05T15:12:11+00:00

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

dbludvl 0 Newbie Poster · Answer 6 · 2012-04-03T14:43:57+00:00

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/

Airshow 416 WiFi Lounge Lizard Team Colleague · Answer 7 · 2012-04-05T05:54:05+00:00

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.

shallir 0 Newbie Poster · Answer 8 · 2012-04-15T20:39:59+00:00

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?

Airshow 416 WiFi Lounge Lizard Team Colleague · Answer 9 · 2012-04-16T16:37:21+00:00

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

pankaj12290 0 Newbie Poster · Answer 10 · 2013-06-15T10:44:01+00:00

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>