Table Structure :
We have two tables , category and products, first of all populate all the categories form category table in drop down box. then on category selection we will send Ajax request to another page called getproducts.php
Table : Categories
CREATE TABLE IF NOT EXISTS `categories` (
`cat_id` int(11) NOT NULL AUTO_INCREMENT,
`cat_name` varchar(20) NOT NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `categories`
--
INSERT INTO `categories` (`cat_id`, `cat_name`) VALUES
(1, 'Samsung'),
(2, 'Sony'),
(3, 'Motorola'),
(4, 'Xiaomi');Table : Products
CREATE TABLE IF NOT EXISTS `products` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(50) NOT NULL,
`cat_id` int(11) NOT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`product_id`, `product_name`, `cat_id`) VALUES
(1, 'Samsung Galaxy A9', 1),
(2, 'Samsung Galaxy S7', 1),
(3, 'Samsung Galaxy S6 edge', 1),
(4, 'Xperia Z5 Premium', 2),
(5, 'Xperia M5 Dual', 2),
(6, 'Xperia C5 uplta', 2),
(7, 'Moto G Turbo', 3),
(8, 'Moto X Force', 3),
(9, 'Redmi 3 Pro', 4),
(10, 'Mi 5', 4);We have following files to make dependent drop down Script. have a look
-config.php
-index.php
-getProducts.php
-jquery-1.11.2.min.js
in this tutorial we will cover following :
– How to Fillup(Populate) Drop Down Select Box from MySQL table using PHP
– Select and View, Display Data from MySQL table related parent category on Drop Down Selection.
config.php
Simpel database connection file with PDO extension.
<?Php
$dbhost = 'localhost';
$dbname = 'dbdropdown';
$dbuser = 'root';
$dbpass = '';
try{
$dbcon = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $ex){
die($ex->getMessage());
}index.php
Contains Simple HTML code to display Drop Down Select Box , Data will be Loaded from Category Table, when selection made on drop down related records will be displayed through Ajax Request.
<!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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Loading MySQL Records on Drop Down Selection using PHP jQuery</title>
<link rel="stylesheet" href="bootstrap/css/bootstrap.min.css" type="text/css" />
<style type="text/css">
select{
width:250px;
padding:5px;
border-radius:3px;
}
</style>
<script src="jquery-1.11.2.min.js"></script>
<script>/* jquery code will be here */</script>
</head>
<body>
<div class="container">
<div class="page-header">
<h3>
<select id="getProducts">
<option value="showAll" selected="selected">Show All Products</option>
<?php
require_once 'config.php';
$stmt = $dbcon->prepare('SELECT * FROM categories');
$stmt->execute();
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
extract($row);
?>
<option value="<?php echo $cat_id; ?>"><?php echo $cat_name; ?></option>
<?php
}
?>
</select>
</h3>
</div>
<blockquote>Load MySQL Records On Drop Down Selection</blockquote>
<hr />
<div class="" id="display">
<!-- Records will be displayed here -->
</div>
</div>
</body>
</html>jQuery/JavaScript code
Simple jQuery code contains two function getAll(), $(“#getProducts”).change() , function name tells what they can do when any change even occur. when option get selected related products will be loaded within <div id=”display”> tag
<script type="text/javascript">
$(document).ready(function()
{
// function to get all records from table
function getAll(){
$.ajax
({
url: 'getproducts.php',
data: 'action=showAll',
cache: false,
success: function(r)
{
$("#display").html(r);
}
});
}
getAll();
// function to get all records from table
// code to get all records from table via select box
$("#getProducts").change(function()
{
var id = $(this).find(":selected").val();
var dataString = 'action='+ id;
$.ajax
({
url: 'getproducts.php',
data: dataString,
cache: false,
success: function(r)
{
$("#display").html(r);
}
});
})
// code to get all records from table via select box
});
</script>getProducts.php
This will be our main PHP page which selects Records From products table when any category will be get selected on “index.php” …
<?php
include('config.php');
$action = $_REQUEST['action'];
if($action=="showAll"){
$stmt=$dbcon->prepare('SELECT product_id, product_name FROM products ORDER BY product_name');
$stmt->execute();
}else{
$stmt=$dbcon->prepare('SELECT product_id, product_name FROM products WHERE cat_id=:cid ORDER BY product_name');
$stmt->execute(array(':cid'=>$action));
}
?>
<div class="row">
<?php
if($stmt->rowCount() > 0){
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
extract($row);
?>
<div class="col-xs-3">
<div style="border-radius:3px; border:#cdcdcd solid 1px; padding:22px;"><?php echo $product_name; ?></div><br />
</div>
<?php
}
}else{
?>
<div class="col-xs-3">
<div style="border-radius:3px; border:#cdcdcd solid 1px; padding:22px;"><?php echo $product_name; ?></div><br />
</div>
<?php
}
?>
</div>Thanks for reading this tutorial, download the code and try it, and Don’t forget to Share with your social media friends.