Using jQuery to auto-populate select form list from db?

[sleepyw]

First, I don't know anything about jQuery other than it (and AJAX) will do what I'm trying to do. I've been to the jQuery site and found a few items that do what I want in general, but none seem to specifically relate to pulling data from a db to populate a select list.

 

Here's the problem I'm trying to solve:

 

I have a MySQL db table that stores products and version numbers. For example, we'll say the db table has data something like this:

 

Product                  Version

-------                  -------

Microsoft Word          2000

Microsoft Word          2003

Microsoft Word          2007

iTunes                      6.0

iTunes                      7.0

iTunes                      7.4

PhotoShop                  6.0

PhotoShop                  CS

PhotoShop                  CS2

PhotoShop                  CS4

 

So what I'm trying to do is on the PHP page is show the DISTINCT product list (Microsoft Word, iTunes, PhotoShop), and when a user selects one, it looks at the db and searches through the distinct Product names and then displays the versions in a second <SELECT> drop-down list.

 

So if someone selects iTunes from the Product drop-down, the second Version drop-down list shows the options 6.0, 7.0, 7.4 (dynamically generated).

 

Here's a sample of what I have as I'm trying to implement it (without the jQuery code, of course):

	
<strong>Product: </strong>
<select name="Product">
	<? $Product_sql="SELECT DISTINCT Product FROM Products_Release";
		$Product_result=mysql_query($Product_sql);
		while($Product_rows=mysql_fetch_array($Product_result)){
	echo "<option value='"; 
	echo $Product_rows['Product']; 
	echo "'>";
	echo $Product_rows['Product'];
	echo "</option>";
	} ?>
</select>

The below drop-down would then auto-populate based on the Product selected above:

	<strong>Version:</strong> <? $Version_sql="SELECT Version FROM Products_Release WHERE Product='$Product_rows[Product]' ORDER by Version ASC";
		$Version_result=mysql_query($Version_sql);
		while($Version_rows=mysql_fetch_array($Version_result)){
	echo "<option value='"; 
	echo $Version_rows['Version']; 
	echo "'>";
	echo $Version_rows['Version'];
	echo "</option>";
	} ?>

 

Obviously that doesn't work, but that's what I'm trying to do. Does anyone know of specific jQuery code to handle this as it relates to PHP and MySQL? Most of the code I find is for set values added right to the code, not querying a db with specific parameters.

 

Very much appreciated - thank you in advance!

[nadeemshafi9]

create a JSON http://en.wikipedia.org/wiki/Json object on the server, then get it with ajax in jquery, now you can easily say,

 

textbo1.value = thejsonresult.record1.name

 

you can find a class that transforms an array into json and just output it and retrive it with ajax

[sleepyw]

Thanks for your reply.

 

I am aware of the types of code I need to use - I am actually looking to see if someone has a specific piece of code they know of that I can just plug my data into. Otherwise, I don't know anything about JSON, AJAX, or JQUERY, so most of what you typed is foreign to me, sorry.

[nadeemshafi9]

Thanks for your reply.

 

I am aware of the types of code I need to use - I am actually looking to see if someone has a specific piece of code they know of that I can just plug my data into. Otherwise, I don't know anything about JSON, AJAX, or JQUERY, so most of what you typed is foreign to me, sorry.

 

its actualy quite simple

 

this is how you get the resulkts from the file

$.ajax({
   type: "POST",
   url: "some.php",
   data: "name=John&location=Boston",
   success: function(msg){
     alert( "Data Saved: " + msg );
    // msg will have all your data in it
// you can access it via msg.items[1].name etc depending on what you name it in teh JSON
   }
});

 

 

 

now on your server you create a php file which outputs results the array of your query into json

 

 

use

 

(PHP 5 >= 5.2.0, PECL json >= 1.2.0)

string json_encode  ( mixed $value  [, int $options = 0  ] )

 

place your mysql query result -> to array in the function and get the result

 

then just echo the result

 

 

 

 

 

[sleepyw]

Thanks again for taking the time to help out. I'll see how far I can get with your info.

[nadeemshafi9]

Thanks again for taking the time to help out. I'll see how far I can get with your info.

 

there are many other ways to do it, but they are non standard and will lead to complications