How to populate table based on value from dropboxes? (MySQL, PHP, AJAX)

[AdventDeo]

Hello it's me again!

So I've managed to create 4 linked drop down boxes like:

Country : [===Country===|v|]
Client : [===Client===|v|]
Environment : [===Environment===|v|]
System: [===System===|v|]

And it's working fine with the codes written at the end of this post.

Next step is I wanted a Table to contain 2 columns with header "Product Name" and "Version" below the drop boxes on the same page.

When the user selects CLIENT, I want the Table to be filled with values based on the query:

SELECT pd.`Name`, vd.`Version`
FROM product pd, versiondetail vd, `client` cl, environment en, system sy
WHERE vd.`Product ID` = pd.`Product ID`
AND vd.`System ID` = sy.`System ID`
AND sy.`Environment ID` = en.`Environment ID`
AND en.`Client ID` = cl.`Client ID`
AND cl.`Client ID` = '[B]The ID of the selected client from the drop down box[/B]'

But when user selects the Environment, I want the Table to contain values only from the query:

SELECT pd.`Name`, vd.`Version`
FROM product pd, versiondetail vd, environment en, system sy
WHERE vd.`Product ID` = pd.`Product ID`
AND vd.`System ID` = sy.`System ID`
AND sy.`Environment ID` = en.`Environment ID`
AND en.`Environment ID`= '[B]The ID of the selected environment from the drop down box[/B]'

And finally if the user selects system, the table will only contain:

SELECT pd.`Name`, vd.`Version`
FROM product pd, versiondetail vd, system sy
WHERE vd.`Product ID` = pd.`Product ID`
AND vd.`System ID` = sy.`System ID`
AND sy.`System ID` = '[B]The ID of the selected system from the drop down box[/B]'

Question: How to achieve this?

Following are my current codes for the linked dropboxes:

country_dropdown.php:

<?    
     echo "<form name=sel>\n";

     echo "Country : <font id=country><select>\n";
     echo "<option value='0'>============</option> \n" ;
     echo "</select></font>\n";
     
     echo "Client : <font id=client><select>\n";
     echo "<option value='0'>=== none ===</option> \n" ;
     echo "</select></font>\n";
     
     echo "Environment : <font id=environment><select>\n";
     echo "<option value='0'>=== none ===</option> \n" ;
     echo "</select></font>\n";
     
     echo "System : <font id=system><select>\n";
     echo "<option value='0'>=== none ===</option> \n" ;
     echo "</select></font>\n";

?>

<script language=Javascript>
function Inint_AJAX() {
try { return new ActiveXObject("Msxml2.XMLHTTP");  } catch(e) {}
try { return new ActiveXObject("Microsoft.XMLHTTP"); } catch(e) {}
try { return new XMLHttpRequest();          } catch(e) {}
alert("XMLHttpRequest not supported");
return null;
};

function dochange(src, val) {
var req = Inint_AJAX();
req.onreadystatechange = function () {
 if (req.readyState==4) {
      if (req.status==200) {
           document.getElementById(src).innerHTML=req.responseText;
      }
 }
};
req.open("GET", "country.php?data="+src+"&val="+val);
req.setRequestHeader("Content-Type", "application/x-www-form-urlencoded;charset=iso-8859-1");
req.send(null);
}

window.onLoad=dochange('country', -1);
</script>

country.php:

<?
header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");

header("content-type: application/x-javascript; charset=tis-620");

$data=$_GET['data'];
$val=$_GET['val'];

include 'config.php';
include 'opendb.php';

if ($data=='country') {
  echo "<select name='country' 
  onChange=\"dochange('client', this.value),
  dochange('environment', this.value),
  dochange('system', this.value)\">\n";

  echo "<option value='0'>====State====</option>\n";

  $result=mysql_query("SELECT c.`Country ID`, c.`Name` 
		       FROM clientconfiguration.country c 
                       ORDER BY `Name`") 
		       or exit (mysql_error());

  while(list($id, $name)=mysql_fetch_array($result)){
       echo "<option value=\"$id\" >$name</option> \n" ;
  }
} else if ($data=='client') {
  echo "<select name='client' 
  onChange=\"dochange('environment', this.value),
  dochange('system', this.value)\">\n";

  echo "<option value='0'>====Client====</option>\n";    
               
  $result=mysql_query("SELECT cl.`Client ID`, cl.`Name` 
		       FROM `client` cl, country co 
		       WHERE cl.`Country ID` = co.`Country ID`
		       AND cl.`Country ID` = '$val'
		       ORDER BY cl.`Name`") 
		       or exit(mysql_error());

  while(list($id, $name)=mysql_fetch_array($result)){
       echo "<option value=\"$id\" >$name</option> \n" ;
  }
} else if ($data=='environment') {
  echo "<select name='environment' 
  onChange=\"dochange('system', this.value)\">\n";
  
  echo "<option value='0'>===Environment===</option>\n";         
          
  $result=mysql_query("SELECT en.`Environment ID`, env.`Type` 
		       FROM `environment` en, `client` cl, `environmenttype` env
		       WHERE cl.`Client ID` = en.`Client ID`
		       AND env.`ID` = en.`Type`
		       AND en.`Client ID` = '$val'") 
		       or exit(mysql_error());

  while(list($id, $name)=mysql_fetch_array($result)){
       echo "<option value=\"$id\" >$name</option> \n" ;
  }
} else if ($data=='system') {
  echo "<select name='system' >\n";

  echo "<option value='0'>===System===</option>\n";         
          
  $result=mysql_query("SELECT sy.`System ID`, sy.`Label` 
		       FROM system sy, environment en
		       WHERE en.`Environment ID` = sy.`Environment ID`
		       AND sy.`Environment ID` = '$val'") 
		       or exit(mysql_error());

  while(list($id, $name)=mysql_fetch_array($result)){
       echo "<option value=\"$id\" >$name</option> \n" ;
  }
}

echo "</select>\n";
?>

[noelthefish]

Hi again,

Ok the last time you got the idea so quick that this time you will kick yourself for not thinking of it.
so follow these steps.

1. add an extra parameter to the Javascript in country_dropdown.php call it something like dropdown. Change the call to country.php within the javascript to include another variable called dropdown.

2. Change country.php so that the selects pass this extra parameter. EG change

onChange=\"dochange('environment', this.value),
  dochange('system', this.value)\">\n";

to

onChange=\"dochange('environment', this.value,'client'),
  dochange('system', this.value,'')\">\n";

3. In the country.php add another function called something like. newTable($dropdown).

4. In here just do what you have done before.

EG

at the top do

$data=$_GET['data'];
$val=$_GET['val'];
$dropdown = $_GET['dropdown'];
..
.
.
.
.
.
function newTable($dropdown)
{
if ($dropdown == 'client')
{
SELECT pd.`Name`, vd.`Version`
FROM product pd, versiondetail vd, `client` cl, environment en, system sy
WHERE vd.`Product ID` = pd.`Product ID`
AND vd.`System ID` = sy.`System ID`
AND sy.`Environment ID` = en.`Environment ID`
AND en.`Client ID` = cl.`Client ID`
AND cl.`Client ID` = 'The ID of the selected client from the drop down box'

etc.......
}

if ($dropdown == 'environment')
{
}

if $dropdown == 'system')
{
}
}

echo the html code as before and you are done.

obviously this is a quick quide but you have intelligence so you will make this fit what you want like you did before but if you hit any problems just email me.

[AdventDeo]

Hello! Thank you so much for helping me again... But I definitely got something wrong with the codes.. Can't get it to work... Instead of:

Country: [===select country===|v|] Client: [===select client===|v|] Environment: [===select environment===|v|] System: [===select system===|v|]

It became:

Country: [=========|v|] Client: [===none===|v|] Environment: [===none===|v|] System: [===none===|v|]

Maybe I didn't do it quite right but see my codes:

country_dropdown2.php

<?    
     echo "<form name=sel>\n";

     echo "Country : <font id=country><select>\n";
     echo "<option value='0'>============</option> \n" ;
     echo "</select></font>\n";
....
....
....
?>

<script language=Javascript>
....
};

function dochange(src, val,[B] dropdown[/B]) {
var req = Inint_AJAX();
req.onreadystatechange = function () {
 if (req.readyState==4) {
      if (req.status==200) {
           document.getElementById(src).innerHTML=req.responseText;
      }
 }
};
req.open("GET", "country2.php?data="+src+"&val="+val+[B]"&dropdown="+dropdown[/B]);
...
}

window.onLoad=dochange('country', -1,[B]"[/B]);
</script>

country 2.php:

<?
....
$data=$_GET['data'];
$val=$_GET['val'];
[B]$dropdown=$_GET['dropdown'];[/B]

include 'config.php';
include 'opendb.php';

if ($data=='country') {
.....
  }
} else if ($data=='client') {
  echo "<select name='client' 
  onChange=\"dochange('environment', this.value),
  dochange('system', this.value,[B]"[/B])\">\n";

  echo "<option value='0'>====Client====</option>\n";    
               
  $result=mysql_query("SELECT cl.`Client ID`, cl.`Name` 
		       FROM `client` cl, country co 
		       WHERE cl.`Country ID` = co.`Country ID`
		       AND cl.`Country ID` = '$val'
		       ORDER BY cl.`Name`") 
		       or exit(mysql_error());

  while(list($id, $name)=mysql_fetch_array($result)){
       echo "<option value=\"$id\" >$name</option> \n" ;
  }
} else if ($data=='environment') {
  ....
  }
} else if ($data=='system') {
 ....
  }
}
echo "</select>\n";

[B]function newTable($dropdown)[/B]
{
if ($dropdown == 'client')
{
 $result2 = mysql_query("SELECT pd.`Name`, vd.`Version`
 			FROM product pd, versiondetail vd, 
 			`client` cl, environment en, system sy
			WHERE vd.`Product ID` = pd.`Product ID`
			AND vd.`System ID` = sy.`System ID`
			AND sy.`Environment ID` = en.`Environment ID`
			AND en.`Client ID` = cl.`Client ID`
			AND cl.`Client ID` = '$val'");
  if (!$result2) {die("Query to show fields from table failed");}

  $fields_num = mysql_num_fields($result2);

  echo "<table border='1'><tr>";
  for($i=0; $i<$fields_num; $i++)
    {
     $field = mysql_fetch_field($result2);
     echo "<td>{$field->name}</td>";
    }
  echo "</tr>\n";
  while($row = mysql_fetch_row($result2))
    {
     echo "<tr>";
     foreach($row as $cell)
     echo "<td>$cell</td>";
     echo "</tr>\n";
    }
  mysql_free_result($result2);
}

if ($dropdown == 'environment')
{
}

if $dropdown == 'system')
{
}
}

?>

And I got the codes for the original country_dropdown and country phps from a forum and I just change the values, I am really still clueless about PHP... =S

[Josh Connerty]

Okay here is a little tutorial on the workings of what you are trying to do:

1. Add an onChange event to the pervious select field, this is so that when the user changes this it will trigger the next select box to run the query.

2. In your JavaScript file (I prefer jQuery so bare with me) you need to use ajax to call on a seperate PHP document that will be the middle man between the JavaScript and the MySql server. You can do this using the jQuery.ajax function.

// Get the data from the field
var src = $("select#previous").val();

jQuery.ajax{(
url: "location of the document, can be a url",
type: "POST/GET",
timeout: "number of [b]milliseconds[/b] untill JavaScript must give up trying to cantact the document and wait for a response.",
error: function(){
alert("We are experiencing issues with our system, we will now refresh the page for you.");
history.go(0);
},
beforeSend: function() {
$("#loading-bar").css("display","block");
},
data: "src="+src,
success: function(success) {
$("div#container").html(success);
$("#loading-bar").css("display","none");
}
)}

Something allong those lines etc. (I dunno)

3. You need the PHP document to run the queries (the really overcomplicated ones) and then return a full set of results I.E:

<select name="something or other" id="something or other else">
<?php

$query = mysql_query("SELECT * FROM `other_table` WHERE `old_table_id` = '".$_POST['src']."'");

while( $row = mysql_fetch_array( $query ) ) {
echo '<option value="'.$row['id'].'">'.$row['Product Name etc.'].'</option>';
}

?>
</select>

You should get the drift, it is easier to do it yourself then if something goes wrong you know were the problem lies, plud eveidently they are easier to expand.