Home jQuery

Dynamic and Dependent Drop Down List Using AJAX

This tutorial will teach you how to make Dynamic and Dependent Drop Down List using AJAX. We are using PHP and MySQL as well.

Example #1

1.0 Create index.php

Create index.php with the following Basic HTML Code

<!DOCTYPE html>
<html>
<head>
    <title>Dynamic Dropdown Example</title>
</head>
<body>

</body>
</html>

2.0 Retrieve ‘Teams’ from database

Put the following code between the ‘body’ tags of the previous section.

<?php 
// retrieve teams from the database 
// include database and object files
include_once 'config/database.php';
include_once 'objects/team.php';

// instantiate database and product object
$database = new Database();
$db = $database->getConnection();

$team = new Team($db);

$stmt = $team->read();
$num = $stmt->rowCount();

if($num>0){
	echo "<select id='teams-dropdown'>";
	echo "<option value='0'>Select team...</option>";
	while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
		extract($row);
		echo "<option value='{$id}'>{$name}</option>";
	}
	echo "</select>";
}
?>

3.0 Create database.php

Create database.php file. The previous section will not work without the database connection. Put the following code.

<?php
class Database{

	// specify your own database credentials
	private $host = "localhost";
	private $db_name = "nba";
	private $username = "root";
	private $password = "";
	public $conn;

	// get the database connection
	public function getConnection(){

		$this->conn = null;

		try{
			$this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
		}catch(PDOException $exception){
			echo "Connection error: " . $exception->getMessage();
		}

		return $this->conn;
	}
}
?>

4.0 Create team.php

Create ‘objects’ folder and inside it, create team.php file. The section before the previous section will not work without this. Put the following code inside it.

<?php
class Team{

	// database connection and table name
	private $conn;
	private $table_name = "teams";

	// object properties
	public $id;
	public $name;

	public function __construct($db){
		$this->conn = $db;
	}
	
	// read products
	public function read(){

		$query = "SELECT id, name
				FROM " . $this->table_name . "
				ORDER BY name";

		// prepare query statement
		$stmt = $this->conn->prepare($query);

		// execute query
		$stmt->execute();

		return $stmt;
	}
	
}
?>

5.0 Create empty select box

Open index.php and put the following code under the code in section 2.0 above.

<!-- create empty select box -->
<select id='players-dropdown'>
	<option value='0'>Select player...</option>
</select>

6.0 Prepare jQuery script

Include the jQuery library and its initial script. Put the following code under the code of the previous section.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.0.0/jquery.min.js"></script>
<script>
$(document).ready(function(){

});
</script>

7.0 Detect change of ‘teams’ dropdown

We have to detect when the user selects a team. Put the following code under “$(document).ready(function(){” of the previous section.

// detect change of dropdown
$("#teams-dropdown").change(function(){
	
	// get id of selected team 
	var team_id=$(this).find(':selected').val();
	
});

8.0 Get JSON data

Now, we will have to get the list of players based on selected team. Put the following code under “var team_id” line of code of the previous section.

// set json url
var json_url="players_json.php?team_id=" + team_id;

// get json data
jQuery.getJSON(json_url, function(data){
	
});

9.0 Create players_json.php

Create players_json.php file. This file will render the list of players based on selected team. We are going to use the ‘JSON’ data format because JavaScript can read this format easily. Put the following code inside this file.

<?php 
// set json headers
header("Access-Control-Allow-Methods: GET");
header('Content-Type: application/json');

// retrieve players from the database 
// get team id parameter
$team_id=isset($_GET['team_id']) ? $_GET['team_id'] : die('Team ID not found.');

// include database and object files
include_once 'config/database.php';
include_once 'objects/player.php';

// instantiate database and product object
$database = new Database();
$db = $database->getConnection();

$player = new Player($db);

$player->team_id=$team_id;
$stmt = $player->read();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($results);
?>

10.0 Create player.php file

Go inside the ‘objects’ folder and create the player.php file. This file will retrieve players data from the database. Put the following code inside it.

<?php
class Player{

	// database connection and table name
	private $conn;
	private $table_name = "players";

	// object properties
	public $id;
	public $team_id;
	public $name;

	public function __construct($db){
		$this->conn = $db;
	}
	
	// read products
	public function read(){

		$query = "SELECT id, team_id, name 
				FROM " . $this->table_name . " 
				WHERE team_id=:team_id
				ORDER BY name";

		// prepare query statement
		$stmt = $this->conn->prepare($query);

		// santize
		$this->team_id=htmlspecialchars(strip_tags($this->team_id));
		
		// bind value 
		$stmt->bindParam(":team_id", $this->team_id);
		
		// execute query
		$stmt->execute();

		return $stmt;
	}
	
}
?>

11.0 Change players dropdown

Now we will try to change the content of ‘players’ drop down. Open your index.php file and put the following code after “jQuery.getJSON(json_url, function(data){” line of index.php file.

// empty contents of players dropdown
$("#players-dropdown").html("");
$("#players-dropdown").append("<option value='0'>Select player...</option>");

// put new dropdown values to players dropdown
jQuery.each(data, function(key, val){
	$("#players-dropdown").append("<option value='" + val.id + "'>" + val.name + "</option>")
});

Example #2

I used this code when I have a small list of records (e.g. authors or categories) that can be picked using a drop-down list and then, I want to load the related information instantly without refreshing the page.

So in this post, we are going to code that does:

  • A drop down list with small number of names or authors
  • When a user selected an item from the drop down, it will load the related data below it.

We are going to use 4 files only, see below:

1.0 jQuery CDN

Prepare our favorite JavaScript library.

2.0 db_connect.php

We will use this file for database connection.

<?php
$host = "YOUR_DATABASE_HOST";
$db_name = "YOUR_DATABASE_NAME";
$username = "YOUR_DATABASE_USERNAME";
$password = "YOUR_DATABASE_PASSWORD";

try{
	$con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}

// to handle connection error
catch(PDOException $exception){
	echo "Connection error: " . $exception->getMessage();
}
?>

Here’s the database table we used. Run the following SQL query on your PhpMyAdmin.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `firstname`, `lastname`, `username`, `password`) VALUES
(1, 'John', 'Doe', 'johnny', 'john'),
(2, 'Albert', 'Minston', 'albert', 'albert');

3.0 index.php

This will show the users drop-down list, records are from the database users table.

<!DOCTYPE html>
<html lang="en">
	<head>
		<title>Example #2 - Dynamic and Dependent Drop Down List Using AJAX</title>
		<style>
		body{
			font-family:arial,sans-serif;
		}

		select{
			margin:0 0 10px 0;
			padding:10px;
		}

		td {
			background-color:#e8edff;
			padding: 10px;
		}
		</style>
	</head>
<body>

<?php
// connect to database
include_once "db_connect.php";

// retrieve list of users and put it in the select box
$query = "SELECT id, firstname, lastname, username FROM users";
$stmt = $con->prepare($query);
$stmt->execute();

//this is how to get number of rows returned
$num = $stmt->rowCount();

// make sure there are records on the database
if($num > 0){

// this will create selec box / dropdown list with user records
echo "<select id='users'>";

	// make a default selection
	echo "<option value='0'>Select a user...</option>";

	while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
		extract($row);
		echo "<option value='{$id}'>{$firstname} {$lastname}</option>";
	}
echo "</select>";

}

// if no user records
else{
	echo "<div>No records found</div>";
}

// this is where the related info will be loaded
echo "<div id='userInfo'></div>";
?>

<script src="js/jquery-1.9.1.min.js" ></script>
<script>
$(document).ready(function(){
	$("#users").change(function(){

		// get the selected user's id
		var id = $(this).find(":selected").val();

		// load it in the userInfo div above
		$('#userInfo').load('data.php?id=' + id);

	});
});
</script>

</body>
</html>

4.0 data.php

This contains the query and will show the table with information related to the selected drop-down item.

<?php
include 'db_connect.php';

try {

	// prepare query
	$query = "SELECT firstname, lastname, username
			FROM users
			WHERE id = ?";

	$stmt = $con->prepare( $query );

	// this is the first question mark above
	$stmt->bindParam(1, $_REQUEST['id']);

	// execute our query
	$stmt->execute();

	// store retrieved row to a variable
	$row = $stmt->fetch(PDO::FETCH_ASSOC);

	// values to fill up our table
	$firstname = $row['firstname'];
	$lastname = $row['lastname'];
	$username = $row['username'];

	// our table
	echo "<table>";
		echo "<tr>";
			echo "<td>Firstname: </td>";
			echo "<td>{$firstname}</td>";
		echo "</tr>";
		echo "<tr>";
			echo "<td>Lastname: </td>";
			echo "<td>{$lastname}</td>";
		echo "</tr>";
		echo "<tr>";
			echo "<td>Username: </td>";
			echo "<td>{$username}</td>";
		echo "</tr>";
	echo "</table>";

}catch(PDOException $exception){

	// to handle error
	echo "Error: " . $exception->getMessage();
}
?>

Download Source Code

You can download all the code used in this tutorial for only $9.99 $5.55!

THANK YOU!

has been added to your cart!

Powered by Easy Digital Downloads

Thank you!

have been added to your cart!

Powered by Easy Digital Downloads

Thank you for learning from our post about: Dynamic and Dependent Drop Down Menu with PHP, MySQL and jQuery!