Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

PHP, SQL, jQuery and AJAX - Populate Select Boxes

ajax jquery

  • Please log in to reply
8 replies to this topic

#1 DEViANT

DEViANT

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 265 posts

Posted 08 October 2010 - 07:42 AM

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 ** out there just looking for the script, I've attached it to the post for you :P

Attached Files


  • 0

:D You should rep+ me so that I can win :D

My Blog | Ask me!
Error : Satan did it

#2 John

John

    CC Mentor

  • Moderator
  • 4450 posts
  • Location:New York, NY

Posted 16 October 2010 - 09:57 PM

Great tutorial, but I'd just like to point out one thing.

$con = @mysql_connect($this->databasehost,$this->databaseusername,$this->databasepassword) or die(mysql_error()); 
        @mysql_select_db($this->databasename) or die(mysql_error());
You use the suppression operator (@) to suppress the warnings, but if there is an error you kill the script and display the error. They contradict each other. I tend to avoide or die(mysql_error()) in production code.
  • 0

#3 DEViANT

DEViANT

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 265 posts

Posted 17 October 2010 - 06:01 AM

Holly ** your right, don't know where my head was at when I was setting that up :P

Thanks for the heads up ^^
  • 0

:D You should rep+ me so that I can win :D

My Blog | Ask me!
Error : Satan did it

#4 baris22

baris22

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 27 October 2010 - 06:36 PM

it doesn`t work on Explorer 8, i do not know much abaut jquery i don`t know what is wrong.
  • 0

#5 DEViANT

DEViANT

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 265 posts

Posted 29 October 2010 - 06:27 AM

Your going to have to give me more to work with than that. A co-worker tested my script on his windblows machine running IE8 and it worked fine. Ensure your browser has Javascript enabled.
  • 0

:D You should rep+ me so that I can win :D

My Blog | Ask me!
Error : Satan did it

#6 AllezinE

AllezinE

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 01 September 2011 - 11:42 AM

Hi,

Really useful script, I've been looking for that for a loooong time, since I am in the dark ages as far as AJAX goes :)
Only one question - how do you actually get the result from the choices given. I need to print the echo result in a table, so
based on the drop-down menus it will pull the record and echo it in a table.

Any help really appreciated.

Thank you.
Best Regards.
  • 0

#7 A3MIRAL

A3MIRAL

    CC Newcomer

  • Member
  • PipPip
  • 16 posts
  • Location:USA
  • Programming Language:Java, C#, PHP, JavaScript, Bash
  • Learning:C, Objective-C, Python

Posted 21 December 2011 - 02:17 PM

Very nice!
These are my favorite languages, and i always like to look for other people using them!
  • 0

#8 hinkeltje

hinkeltje

    CC Newcomer

  • Member
  • PipPip
  • 13 posts
  • Programming Language:PHP, JavaScript

Posted 18 June 2012 - 11:32 PM

Hi Deviant, I am trying to get this example to work on http://www.itasc-dev.com/makes/
But somehow I'm having troubles getting the second selectbox populated.
Could you please help me?


Addition: Ok, i figured out the script actually does work in Google Chrome and Firefox. But not in Internet Explorer. Is there a fix for this?
  • 0

#9 FilipPipo

FilipPipo

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 26 November 2012 - 08:03 AM

Thx 4 ur trouble
  • 0





Also tagged with one or more of these keywords: ajax, jquery

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download