PHP, SQL, jQuery and AJAX - Populate Select Boxes - PHP Tutorials

In this tutorial I will show you how to write a simple jQuery AJAX script to populate Multiple select boxes from data retrieved from an SQL Database. As a demonstration I will use the concept of a search query implemented on most motor sales websites.

For a demonstration (NOT one of my websites) of the script please visit :
Sierra Blanca Motors of Ruidoso | New Vehicle Inventory

First, Lets build our relationships in the Database :
We will have one table named 'types' to save the different vehicle types on may want to query. We will have another table named 'makes' to save the different vehichle manufacturers names. The final Table will be named 'model' to save a list of models available. The 'model' table will have two refference keys namely 'mo_make' and 'mo_type'. These two fields will hold the unique id numbers for the model based on the type and make we have in our database.

Now, lets get down to some work. Create a database in your phpmyadmin and call it 'vehicles'. After that is done, run the following SQL command on your database :


--

-- Table structure for table `makes`

--


CREATE TABLE IF NOT EXISTS `makes` (

  `m_id` int(11) NOT NULL AUTO_INCREMENT,

  `m_caption` varchar(255) NOT NULL,

  PRIMARY KEY (`m_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


--

-- Dumping data for table `makes`

--


INSERT INTO `makes` (`m_id`, `m_caption`) VALUES

(1, 'Buick'),

(2, 'Dodge'),

(3, 'Chrysler'),

(4, 'Jeep'),

(5, 'Chevrolet');


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


--

-- Table structure for table `models`

--


CREATE TABLE IF NOT EXISTS `models` (

  `mo_id` int(11) NOT NULL AUTO_INCREMENT,

  `mo_caption` varchar(255) NOT NULL,

  `mo_make` int(11) NOT NULL,

  `mo_type` int(11) NOT NULL,

  PRIMARY KEY (`mo_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


--

-- Dumping data for table `models`

--


INSERT INTO `models` (`mo_id`, `mo_caption`, `mo_make`, `mo_type`) VALUES

(1, '2010 LaCrosse', 1, 1),

(2, '2010 Malibu', 5, 1),

(3, '2011 Ram Pickup', 2, 3),

(4, '2010 Liberty', 4, 2),

(5, '2010 Town & Country', 3, 2);


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


--

-- Table structure for table `types`

--


CREATE TABLE IF NOT EXISTS `types` (

  `t_id` int(11) NOT NULL AUTO_INCREMENT,

  `t_caption` varchar(255) NOT NULL,

  PRIMARY KEY (`t_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


--

-- Dumping data for table `types`

--


INSERT INTO `types` (`t_id`, `t_caption`) VALUES

(1, 'Passenger Cars'),

(2, 'Sports Utility Vehicles'),

(3, 'Trucks');

I always believe its good to have a good folder structure in place. So Please create the following folders, as the tutorial will be working with the same structure.


/projectname

/projectname/lib

/projectname/js

/projectname/ajax

Create a file called "class_dbcon.php" and save it in the /projectname/lib directory. Add the following content to the file:


<?php

/*


	In this class we instantiate an SQL Connection object. Connection details are assinged to 

	object variabes so that they can be used when connecting to the database. The two main 

	functions are conn() and disc(). They are for connecting and disconnecting to the SQL database.


*/

class doConnect

{

	private $databasehost;

	private $databasename;

	private $databaseusername;

	private $databasepassword;

	

	function __construct()

	{

		$this->setRes();

		$this->conn();

	}

	

	function setRes()

	{

		$this->databasehost = "localhost";

		$this->databasename = "vehicles";

		$this->databaseusername ="cars";

		$this->databasepassword = "car123";

	}

	

	function conn()

	{

		$con = @mysql_connect($this->databasehost,$this->databaseusername,$this->databasepassword) or die(mysql_error());

		@mysql_select_db($this->databasename) or die(mysql_error());


	}

	

	function disc()

	{

		mysql_close();

	}

}

?>

Remember to replace the vallues in the following function to match your database details:


	function setRes()

	{

		$this->databasehost = "localhost";

		$this->databasename = "vehicles";

		$this->databaseusername ="cars";

		$this->databasepassword = "car123";

	}

How you would use this class in a script:


include('lib/class_dbcon.php');

$connect = new doConnect();

//

// All your queries to be executed on the page

//

$connect->disc();

Create the files "ajax_make.php" and "ajax_model.php" in the /projectname/ajax directory.

Add the following content to the "ajax_make.php" file:


<?php

	if(isset($_POST['type']))

	{

		include('../lib/class_dbcon.php');

		$connect = new doConnect();

		$option .= '<option value="">-- Select Make --</option>';		

		$query = mysql_query("SELECT m_id, m_caption FROM makes ORDER BY m_id ASC") or die(mysql_error());

		while($row = mysql_fetch_assoc($query))

		{

			$option .= '<option value="' . $row['m_id'] . '">' . $row['m_caption'] . '</option>';

		}

		

		echo $option;

	}

?>

Bassically all we do in the above mentioned script is build html <option> tags containing all the information for our select box. As you can see it receives a $_POST variable called 'type'. The jQuery AJAX function will POST to this file. Notice that we echo out the result. I will discuss why we do this later on in the tutorial.

Add the following content to the "ajax_model.php" file:


<?php

	if(isset($_POST['type']) && isset($_POST['make']))

	{

		$make = $_POST['make'];

		$type = $_POST['type'];

		

		include('../lib/class_dbcon.php');

		$connect = new doConnect();

		$option .= '<option value="">-- Select Model --</option>';

		$query = mysql_query("SELECT mo_id, mo_caption FROM models WHERE mo_type = '$type' AND mo_make = '$make' ORDER BY mo_id ASC") or die(mysql_error());

		while($row = mysql_fetch_assoc($query))

		{

			$option .= '<option value="' . $row['mo_id'] . '">' . $row['mo_caption'] . '</option>';

		}

		

		echo $option;

	}

?>

This script will receive two POST variables. Type and Make. We use these posted values to build our SQL query which, in effect, builds our <option> tags. As in the previous script, the jQuery AJAX function will POST the variables to this file. Notice that we echo out the result. I will discuss why we do this later on in the tutorial.

Now that we've basically finished the back-end of our jQuery AJAX script, lets build up our index page that will hold our Select Forms and contain the main jQuery AJAX functions. I will first post the entire page content and thereafter explain everything in sections.

Create "index.php" in your /projectname directory. Add the following content to the file:


<!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" dir="ltr" lang="en">

<head>

	<title>Multiple Select Boxes</title>

	<script type="text/javascript" src="js/jquery-1.4.2.js"></script>

	<script type="text/javascript">


		$(document).ready(function() {

		$('#loader').hide();

	

		$('#type').change(function(){


			$('#make').fadeOut();

			$('#loader').show();


			$.post("ajax/ajax_make.php", {

				type: $('#type').val()

			}, function(response){

				setTimeout("finishAjax('make', '"+escape(response)+"')", 400);

			});

			return false;

		});


		$('#make').change(function(){


			$('#model').fadeOut();

			$('#loader').show();


			$.post("ajax/ajax_model.php", {

				type: $('#type').val(),

				make: $('#make').val()

			}, function(response){

				setTimeout("finishAjax('model', '"+escape(response)+"')", 400);

			});

			return false;

		});


		});


		function finishAjax(id, response){

	 	 $('#loader').hide();

	 	 $('#'+id).html(unescape(response));

	 	 $('#'+id).fadeIn();

		}

	</script>

</head>

<body>

<div id="loader"><strong>Loading...</strong></div>

<form name="theform" id="form" method="POST" action="search.php">

	<label for="type">Type:</label>

		<select id="type" name="type">

			<?php

				include('lib/class_dbcon.php');

				$connect = new doConnect();

	

				$q = mysql_query("SELECT * FROM types ORDER BY t_id ASC");

				while($row = mysql_fetch_assoc($q))

				{

					echo '<option value="'.$row['t_id'].'">'.$row['t_caption'].'</option>';

				}

				$connect->disc();

			?>

		</select>

	<label for="make">Make:</label>

		<select id="make" name="make">

			<option value="">-- Select Make --</option>

		</select>

	<label for="model">Model:</label>

		<select id="model" name="model">

			<option value="">-- Select Model --</option>

		</select>

		 

	<input type="submit" name="submit" value="Search">

</form>

</body>

</html>

Now firstly, we include our jQuery file. I've attached a version of jQuery to this post that you can download, But you should check their website for the latest version. Thereafter we start declaring onclick events for our two select boxes. Note that it uses the box id when executing the onclick events for our jQuery. The Script then posts the select box values by using the .val() attribute. The response is then passed to our second unique function, where the response is updated into the page. Remember we echo'd the result in our ajax_* pages? The echo was the responce that gets updated into the select boxes.

So a bassic flow chart to how this script works would be :


select box => onchange => post to ajax file => get data from sql table

select box <= update <= set response <= process and echo data

And thats it! It's very simple and easy to use, and you can customize it to death to work for you.

For all the lazy basterds out there just looking for the script, I've attached it to the post for you :p