Dynamically populate 3 chained dropdowns SQL statement errors

PHP
1

I have a script which will dynamically populate 3 chained dropdowns.
I can get this to work but I’m now trying to manipulate it and I’ve got something wrong with the code.

I can’t seem to get the value from the first dropdown to go into my subsequent SQL queries correctly. Can anyone tell me where I’m going wrong with this please? I think it’s somthing simple but I just can’t see what is wrong wth the code!

Any help much appreciated. The code is below:

index.php:


<?php 
  include('db.php');
  include('func.php');
?><!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Chained Select Boxes using PHP, MySQL and jQuery</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"></script>

<script type="text/javascript">
$(document).ready(function() {
	$('#wait_1').hide();
	$('#search_type').change(function(){
	  $('#wait_1').show();
	  $('#result_1').hide();
      $.get("func.php", {
		func: "search_type",
		drop_var: $('#search_type').val()
      }, function(response){
        $('#result_1').fadeOut();
        setTimeout("finishAjax('result_1', '"+escape(response)+"')", 400);
      });
    	return false;
	});
});

function finishAjax(id, response) {
  $('#wait_1').hide();
  $('#'+id).html(unescape(response));
  $('#'+id).fadeIn();
}
function finishAjax_tier_three(id, response) {
  $('#wait_2').hide();
  $('#'+id).html(unescape(response));
  $('#'+id).fadeIn();
}
</script>
</head>

<body>
<p>
<form action="" method="post">
  
    <select name="search_type" id="search_type">
    
      <option value="" selected="selected" disabled="disabled">Select a Category</option>
      
      <?php getTierOne(); ?>
    
    </select> 
    
    <span id="wait_1" style="display: none;">
    <img alt="Please Wait" src="ajax-loader.gif"/>
    </span>
    <span id="result_1" style="display: none;"></span>
    <span id="wait_2" style="display: none;">
    <img alt="Please Wait" src="ajax-loader.gif"/>
    </span>
    <span id="result_2" style="display: none;"></span> 
  
</form>
</p>
<p>
<?php if(isset($_POST['submit'])){
	$search_type = $_POST['search_type'];
	$search_make = $_POST['search_make'];
	$search_model = $_POST['search_model'];
	echo "You selected a ";
	echo $search_type." ".$search_make." ".$search_model;
}
?>
</body>
</html>

func.php:


<?php
//**************************************
//     Page load dropdown results     //
//**************************************
function getTierOne()
{
		   echo '<option value="Used_Stock">Used Cars</option>';
		   echo '<option value="New_Offers">New Cars</option>';
		   echo '<option value="Used_Stock">Nearly New Cars</option>';
		   echo '<option value="Used_Stock">Used Vans</option>';
		   echo '<option value="Used Stock">Used Bikes</option>';
		   echo '<option value="Motability">Motability</option>';

}

//**************************************
//     First selection results     //
//**************************************
if($_GET['func'] == "search_type" && isset($_GET['func'])) { 
   search_type($_GET['drop_var']); 
}

function search_type($drop_var)
{  
    include_once('db.php');
	$result = mysql_query("SELECT DISTINCT Make FROM ".$search_type."") 
	or die(mysql_error());
	
	echo '<select name="search_make" id="search_make">
	      <option value=" " disabled="disabled" selected="selected">Choose one</option>';

		   while($search_make = mysql_fetch_array( $result )) 
			{
			  echo '<option value="'.$search_make['Make'].'">'.$search_make['Make'].'</option>';
			}
	
	echo '</select>';
	echo "<script type=\\"text/javascript\\">
$('#wait_2').hide();
	$('#search_make').change(function(){
	  $('#wait_2').show();
	  $('#result_2').hide();
      $.get(\\"func.php\\", {
		func: \\"search_make\\",
		drop_var: $('#search_make').val()
      }, function(response){
        $('#result_2').fadeOut();
        setTimeout(\\"finishAjax_tier_three('result_2', '\\"+escape(response)+\\"')\\", 400);
      });
    	return false;
	});
</script>";
}


//**************************************
//     Second selection results     //
//**************************************
if($_GET['func'] == "search_make" && isset($_GET['func'])) { 
   search_make($_GET['drop_var']); 
}

function search_make($drop_var)
{  
    include_once('db.php');
	$result = mysql_query("SELECT DISTINCT Model FROM $search_type WHERE Make='$drop_var'") 
	or die(mysql_error());
	
	echo '<select name="search_model" id="search_model">
	      <option value=" " disabled="disabled" selected="selected">Choose one</option>';

		   while($search_model = mysql_fetch_array( $result )) 
			{
			  echo '<option value="'.$search_model['Model'].'">'.$search_model['Model'].'</option>';
			}
	
	echo '</select> ';
    echo '<input type="submit" name="submit" value="Submit" />';
}
?>
2

Having just done this, I can offer you a guide as to how I handled it.

  1. The value for ALL dropdowns are sent to the php script by javascript each time there is a selection made.

  2. Selections for dropdowns always include a “Please Choose” with empty value.

  3. php script first checks if dropdown #1 has a value that is not empty, and if so, it queries the database for dropdown #2 content. If dropdown #1 is empty, then all dropdowns have content reset.

  4. If dropdown #2 has content, then php script queries database for dropdown #3 content.

  5. The php script actually creates all of the html that goes inside the select tags. The html is stored in an array, then json encoded.

  6. The response from the ajax request repopulates the dropdowns.

It’s pretty easy stuff. Just make sure to analyze ALL of the dropdowns every time a selection is made (except for the last dropdown of course).

Sorry, I’m not at liberty to post my code, because of a legal obligation I have to my employer. Plus, since I’m using a framework, you probably wouldn’t understand what I was doing anyways.