AJAX dropdown + PHP + MySQL

[LTT]

Hello!

Im new to PHP and have a need for what I have found out is called AJAX.
I prefer to code in PHP for my DB access.
I need to create a dropdown list using AJAX that updates it self depending on what is slected in the parent dropdown list.
To make matters worse.. It needs to be reliant on a database.

Here is an example of what I would like to achieve.

Ill try to be as clear as I can be.

EX:

CARS

STATE                            
                                      |                              
                                      |                              
                                      |                              
                                  Condition                          
                                      |                              
                _________________________________________________    
                |             |              |                  |    
               NEW          USED          CLASSIC          COMMERICAL
                |             |              |                  |    
                |             |              |                  |    
                |             |              |                  |    
              Types         Types          Types              Types  
                |             |              |                  |    
             -------       -------        -------            ------- 
            |       |     |       |      |       |          |       |
           GM      FORD   GM     FORD    GM     FORD       GM    FORD
            |       |     |       |      |       |          |       |
            |       |     |       |      |       |          |       |
            |       |     |       |      |       |          |       |
            |       |     |       |      |       |          |       |
         YEAR 
         MAKE 
         MODEL

I would like the dropdown to flow in this order.. "Example of a user using the dropdown."

User selects from the dropdown the State of choice from the list available.(The "State" dropdown is populated using data from the SQL DB.. example states available "New York", "Florida", and "Vermont")

User selects "New York"(all available "Conditions" in "New York" are loaded into the next dropdown)

User then selects the condition from the next dropdown.. user selects "New"(database populates new car "Types" available)

User then selects the "Type" from the next dropdown.. user selects "GM"(database then populates a list located below the dropdown boxes that contains all vehicles in the database that fit the criteria.)

example of a car in the GM list
-------------------
Year: 1929
Model: Piece
Color: Blue
Link: URL to the cars page.(clickable link)
--------------------
-------------------
Year: 1948
Model: Thing
Color: Red
Link: URL to the cars page.(clickable link)
--------------------
-------------------
Year: 1994
Model: Prettygood
Color: Yellow
Link: URL to the cars page.(clickable link)
--------------------

I guess what I really need is a full walkthrough on this.. Code for everything and the DB Tables and fields that would need to be created to make at least the example work.. From there ill be able to figure out how to manipulate the example to include more items.

I would very much appreciate ANY and ALL help on this matter..

Thank you so much to anyone who takes the time to look at this..

-LTT

[_developit]

I'll try to be clear as well - The only thing I'm not going to post is an AJAX library. You can find one by searching Google for "AJAX function". Once you have your AJAX library included, everything else is nearly copy+paste. You will have to change the AJAX interaction I have written below to match whatever functions you choose to use. The AJAX will call on a PHP file (in the example below, it's named "getoptions.php"), and it will send the selected value as a parameter to that script. The script should simply output the desired <select> dropdown. Once the request is completed, your response function should then insert the HTML from your PHP script into an empty <div> (in the example it is called "secondbox_wrap").

For the JavaScript (AJAX), when the user changes your first <select> dropdown, the "onchange" event is fired, which you can set to call your AJAX function:

<select id="firstselectbox">
<option name="" value="">text</option>
<option name="" value="">text</option>
<option name="" value="">text</option>
</select>

<div id="secondbox_wrap"></div>

<script type="text/javascript"><![CDATA[
// <!--
document.getElementById("firstselectbox").onchange=function() {
  // Get the selected name/value:
  var selected = null;
  for(var x=0; x<this.childNodes.length; x++) {
    if( (this.childNodes[x].nodeName+"").toLowerCase()=="option" && 
        (this.childNodes[x].selected=="selected" || this.childNodes[x].selected==true) ) {
      selected = this.childNodes[x];
    }
  }
  
  // Make sure you have a value:
  if(!selected || !selected.value)
    return false;
  
  // call your AJAX function, whatever you find from your Google search
  // You can send the selected option to PHP as a parameter.
  // ex:
  var request = HttpRequest.Create("getoptions.php?selected="+selected.value,{}, function(){
    // The request's onComplete function
    // Your PHP script sends back the HTML code for a <select> dropdown.
    // Now, just put that code into the extra <div>:
    document.getElementById("secondbox_wrap").innerHTML = this.responseText;
  });
};
// -->
]]></script>

[LTT]

Ok I have been playing around with some code and have found this example that does most of what I would like.

state.php
------------------------
<?
     //set IE read from page only not read from cache
     header ("Expires: Sun, 03 Feb 1997 04:22: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'];
     
     //set database
$dbhost = "localhost";
$dbuser = "****";
$dbpass = "****";
$dbname    = "****";
mysql_pconnect($dbhost,$dbuser,$dbpass) or die ("Unable to connect to MySQL server");  
     
     if ($data=='states') {  // first dropdown
          echo "<select name='states' onChange=\"dochange('cities', this.value)\">\n";
          echo "<option value='0'>==== choose state ====</option>\n";
          $result=mysql_db_query($dbname,"select `id`, `state` from states order by `state`");
          while(list($id, $name)=mysql_fetch_array($result)){
               echo "<option value=\"$id\" >$name</option> \n" ;
          }
     } else if ($data=='cities') { // second dropdown
          echo "<select name='cities' >\n";
          echo "<option value='0'>====choose cities ====</option>\n";                           
          $result=mysql_db_query($dbname,"SELECT `id`, `city` FROM cities WHERE `state_id` = '$val' ORDER BY `city` ");
          while(list($id, $name)=mysql_fetch_array($result)){       
               echo "<option value=\"$id\" >$name</option> \n" ;
          }
     }
     echo "</select>\n";
?>

state_dropdown.php
---------------------------------
<?     
     echo "<form name=sel>\n";
     echo "States : <font id=states><select>\n";
     echo "<option value='0'>============</option> \n" ;
     echo "</select></font>\n";
     
     echo "Cities : <font id=cities><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) {} //IE
   try { return new ActiveXObject("Microsoft.XMLHTTP"); } catch(e) {} //IE
   try { return new XMLHttpRequest();          } catch(e) {} //Native Javascript
   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; //retuen value
               } 
          }
     };
     req.open("GET", "state.php?data="+src+"&val="+val); //make connection
     req.setRequestHeader("Content-Type", "application/x-www-form-urlencoded;charset=iso-8859-1"); // set Header
     req.send(null); //send value
}

window.onLoad=dochange('states', -1);         // value in first dropdown
</script>

Tables to create

CREATE TABLE `cities` (
  `id` int(11) NOT NULL auto_increment,
  `state_id` int(11) NOT NULL default '0',
  `city` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;

CREATE TABLE `states` (
  `id` int(11) NOT NULL auto_increment,
  `state` varchar(255) NOT NULL default '',
  `abb` char(2) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;

This example has 2 drop down boxes where the second box is dependent on the first box. I would like to know how to add a 3rd box to this code and have it be dependent on the second dropdown.

after that I would like to have a list displayed below the dropdown boxes with details of results found in the database.

example.. the 3rd dropdown contains names of DB tables "Cars, Apples, Birds"

CREATE TABLE `cars` (
  `id` int(11) NOT NULL auto_increment,
  `state_id` int(11) NOT NULL default '0',
  `city_id` int(11) NOT NULL default '0',
  `details` varchar(255) NOT NULL default '',
  `moredetails` varchar(255) NOT NULL default '',
  `evenmoredetails` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;

When "cars" is selected from the dropdown menu a list of results is displayed based on the parent dropdown values...

Dropdown boxes

LIST
---------------------------

1
details
moredetails
evenmoredetails
----------
2
details
moredetails
evenmoredetails
----------
3...

Let me know what you think...

Thanks,
-LTT

[buddylee17]

Just a side note, you may be better off loading all of the dropdowns directly into javascript from php when the page loads. If the page contains multiple AJAX requests that go one after the other, I'd either recommend to do it this way or to follow Mibbits guide to optimizing http headers for AJAX.

Why?
Similar to any other http request, an AJAX request contains all the headers (Content-Type, User-Agent, Accept, Keep-Alive...) plus the content sent to and from the server. Most of the time, the AJAX request may only contain 10-50 bytes, but the headers sent with the request may add an extra 300-500 bytes. So the header overhead actually consumes more bandwidth than the data sent to and from the server.

Only use AJAX when it will be faster than a page submit to the server. If you have multiple AJAX requests in a row, it will be much slower than if you'd have just submitted them to the server with a form or loaded them all into javascript when the page is loaded.

I'm not saying don't use AJAX. Use AJAX intelligently.

[_developit]

I'm not saying don't use AJAX. Use AJAX intelligently.

Very true - it looks like there are only a few options in his tree, so it would be very little overhead to just pass them as a JavaScript array.

Also, just a note about the script the original poster updated with - you will want to replace <script language=javascript> with <script type="text/javascript"> for maximum compatibility.

[jshpro2]

I just did this for another site ( www.eliterim.com ), goto wheels and pick "acura", "cl", etc.. ( first option for each select ). It filters all the products and you can also sort by price / size, or any other attribute the admin inputs.

If you would like to chat you may contact me via info @ my website. www.ne8.net

Have a nice day