creating many dropdown lists with MySQL and PHP

[AdventDeo]

Hello,

I have a MySQL database and I'm trying to make a dropdown list using PHP based on the value stored in my DB.

So far I have this (copied, pasted, and edited from another forum):

<form action="maintest2.php" method="POST">
<table border="0" cellpadding="2" width="100%">
<tr>
<td><b>Main Category:</b></td>
<td>
<select name="Name">
<option value="0">Select Country

<?php

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

$cate = mysql_result($result, 0);

$result=mysql_query("SELECT c.`Name` FROM clientconfiguration.country c ORDER BY `Name`") or exit

(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
    echo '<option value="' . $row['Name'] . '"';
    if ($row['Name'] == $cate) {echo ' selected';} echo '>' . $row['Name'];    
}

include 'closedb.php';

?>

</select>
</td>
</tr>
<tr><td colspan=2><input type="submit" name="submit" value="submit"></td></tr>
</table>
</form>

It does show the name of the countries I want, but i don't know how to move on from here.

I want a second dropdown list based on the query:

SELECT cl.`Name`
FROM `client` cl, country co
WHERE co.`Country ID` = cl.`Country ID`
AND co.`Name` = 'Whatever the value is selected from the first dropdown list';

And then below the second dropdown list there will be 3 fixed option:

Production, DR, Test

And based on the 2nd and 3rd option i need to run this query:

SELECT sy.`Label`
FROM `system` sy, `environment` en, `client` cl, `environmenttype` ent
WHERE cl.`Client ID` = en.`client ID`
AND en.`Environment ID` = sy.`Environment ID`
AND ent.`ID` = en.`Type`
AND cl.`Name` = 'drop down list 2 value'
AND ent.`Type` = '3rd option selection';

and finally a two-columned box to view the final result:

SELECT pd.`Name`, vd.`Version`
FROM `system` sy, `product` pd, `versiondetail` vd
WHERE vd.`System ID` = sy.`System ID`
AND vd.`Product ID` = pd.`Product ID`
AND sy.`Label` = 'the above query's selection';

I don't expect anyone to write me the whole code (tho that'll be awesome) but even a small help on this will be great! Been browsing different forums for some clue for 3 days but I'm really lost now!

Tobie.

[humbug]

If you want to update the page without reloading it you will have to use client side scripts such as Javascript. (PHP is a server side language and so all the php is executed before the page is sent to the clients browser.

You will have to do some research and tutorials in Javascript.

EDIT: AJAX is used in conjunction with Javascript to make calls back to the server. (This is needed if you want to actually get more information instead of just manipulating stuff that is already on the page).

[AdventDeo]

If that's the case, do you have any suggestions of how I can just stick with using PHP (which is hard enough for me)?
Maybe with using submit buttons and next and back buttons? =S

[noelthefish]

ok this is really simple just follow these rules.

1. you need to set up the selects.
2. you need a trigger. in this case you would use onchange() as this detects when the value of a dropdown has changed.
3. you need to call the javascript/AJAX to run the next query.
4. PHP to create the new dropdown.
5. Display the results in a new dropdown box.

It really is that simple.

Therefore

Step 1. Create the trigger. Change your current dropdown to include the onchange(); Add an ID to the <select>. Setup your other selects though they will be blank at the moment.

<select name="Name" id="firstname">
<option value="0">Select Country

$cate = mysql_result($result, 0);

$result=mysql_query("SELECT c.`Name` FROM clientconfiguration.country c ORDER BY `Name`") or exit

(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
    echo '<option value="' . $row['Name'] . '" id="first" onchange="doSecond()';
    if ($row['Name'] == $cate) {echo ' selected';} echo '>' . $row['Name'];    
}

<div id="second">
The HTML setup up here will depend upon how you are going to display the dropdown boxes but I have just setup a DIV to illustrate what you can do.
</div>
etc

etc

etc

Step 3. The Javascript/AJAX call. The setSecond() function in this bit of code covers Step 5.

<script language="javascript" type="text/javascript">
function getHTTPObject(){
      if (window.ActiveXObject) return new ActiveXObject("Microsoft.XMLHTTP");
      else if (window.XMLHttpRequest) return new XMLHttpRequest();
      else {
      alert("Your browser does not support AJAX.");
      return null;
      }
}

function setSecond(){
      if(httpObject.readyState == 4){

      document.getElementById('second').innerHTML = httpObject.responseText;
**** This sets up the new HTML in the DIV where the ID = second ****

      }

}


function doSecond(){
**** This function takes the value from your first dropdown which I gave the ID of 'firstname' (check the code above) and passes this to the PHP program which will create the new HTML code ****
	httpObject = getHTTPObject();

    if (httpObject != null) {
		var testing = httpObject.open("GET", "seconddrop.php?firstname=" + document.getElementById('firstname').value, true);
      	httpObject.send(null);
      	httpObject.onreadystatechange = function() { setSecond(); }
**** the above line looks strange but it forces the program to wait till there is a return from the PHP program before it them tries to create the new HTML ****
	}
}

var httpObject = null;

</script>

Step 4. Create a PHP program to populate the dropdown box.

$name = $_GET['firstname'];
$returncode = '<select name="second" id="second">';
SELECT cl.`Name`
FROM `client` cl, country co
WHERE co.`Country ID` = cl.`Country ID`
AND co.`Name` = '$firstname';

Obviously use proper code for the SQL statement im just doing this quickly.

(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
    $returncode .= '<option value="' . $row['Name'] . '"';
    if ($row['Name'] == $cate) 
   {
        $returncode .=  ' selected';
   } 
   $returncode .= '>' . $row['Name'];    
}
$returncode .= '</select>';
echo $returncode;

I have done this quickly so there maybe small errors in it but I am sure that if there are someone will point them out quickly.
If you have any problems just email me and though I wont have time to do all the code for you I might have time to help.

[AdventDeo]

Oh my thank u so much! This looks good but I'm not even sure what to do with it.. =S
Hmm, if it's okay with you, is it okay to go more in depth with the first two lists I need? the select country and select client drop down lists. the rest of the lists I think I can do once I learn how to do these first two.
like, I'm not even sure if i need to put your codes in separate files or i can just write everything in one...
I'm really new to all this.

thank you so much in advance!

oh btw, u forgot one " at: '" id="first" onchange="doSecond()';
should be: '" id="first" onchange="doSecond()"';

haha.. that's all I managed to discover.. x)

[Josh Connerty]

Basicly, just use your orignal document. Make a submit button that will go to another document.

In this document you will need to handle the posted data. You will need to make queries dependant on the value passed. For instance if it is UK then you need to select the stuff from other tables that relate to the UK.

[AdventDeo]

Omg someone gave me a link and i managed to do the first two dropdown lists!! Let me show u guys:

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";
?>

<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)\">\n";
  echo "<option value='0'>==== choose 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' >\n";
  echo "<option value='0'>====choose 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'") or exit(mysql_error());
  while(list($id, $name)=mysql_fetch_array($result)){
       echo "<option value=\"$id\" >$name</option> \n" ;
  }
}
echo "</select>\n";
?>

It works perfectly fine... so i guess now i just need to follow NoelTheFish's steps and figure out how to make my 3rd and 4th droplist and my final result list...

Any help on these would be very much appreciated! =)

[AdventDeo]

Okay guys thanks alot alot alot for the helps... I got it working real good... lemme show u:

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";
?>

Thank you once again! =D