Dependent Drop Down List Using JavaScript
[adinserter block=”34″]
This tutorial will teach you how to make a dynamic and dependent drop down list using JavaScript. We are using PHP and MySQL as well.
Create index.php with the following Basic HTML Code
<!DOCTYPE html>
<html>
<head>
<title>Dynamic Dropdown Example</title>
</head>
<body>
</body>
</html>
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>";
}
?>
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;
}
}
?>
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;
}
}
?>
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>
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>
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();
});
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){
});
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);
?>
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;
}
}
?>
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>")
});
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:
We are going to use 4 files only, see below:
Prepare our favorite JavaScript library.
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');
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>
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();
}
?>
You can download all the code used in this tutorial for only $9.99 $5.55!
[purchase_link id=”12395″ text=”Download Now” style=”button” color=”green”]
Thank you for learning from our post about: Dynamic and Dependent Drop Down Menu with PHP, MySQL and jQuery!