Load Data on Drop Down Selection using PHP MySQL and Ajax | Coding Cage

Load Data on Drop Down Selection using PHP MySQL and Ajax

On By
Hi, friends' after a long time here i am back with a simple yet useful PHP jQuery snippet that titled List Out MySQL Records on Drop Down Selection using PHP jQuery, with the help of Ajax method, so flow is simple when a selection made on drop down select box records will be displayed from MySQL table through Ajax Request related to the selected option, for that we need only few php files, first is for drop down box and second for ajax call request, before proceed you can take a quick look at demo, so let's start coding ...
How to Load Data on Drop Down Selection using PHP MySQL and Ajax



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.



Like This Post? Please share!
Subscribe through email and get Free updates

You May Also Like...

8 comments:

  1. As we know you are expert in php so you must publish tutorial with PDO and Mysqli many of the viewers like me don't use PDO. So it's difficult to understand your PDO code.

    ReplyDelete
    Replies

    1. Hello Shaggy,
      Well PDO is not so difficult to understand, it's just a improved extension of MySQL extension, and MySQL extension is now deprecated, so you should learn PDO or MySQLi

      Delete

    2. how do you display the data if the function u wanna access is on another file from a class? sorry for asking im really new to PDO thing mate

      Delete

  2. Hi pradeep thanks another good example. i have also learn your CRUD example 'PHP PDO CRUD Tutorial using OOP with Bootstrap'. could you please help how to use drop down box in the crud example to filter and update records

    ReplyDelete

  3. thabk you, pradeep good example ....

    ReplyDelete

  4. hi ., thankyou for this post. i just want to know when i wrote this $sql = SELECT * FROM otherTable WHERE columnOfThattable = '$action'.. *this is an example query*
    wanna ask why my query above does not work.. i use columnOfThattable is not the primary key of that table ..,

    ReplyDelete

  5. Hi there,

    I used this as the start point for something else and it works great.

    One thing that I can't work through though.

    I need to pull a variable from the query return but it only seems that the data shows on the rendered web page and never in source so I can't attach to it.

    In short order I need the resulting array to appear in source instead of this

    Any tip would be appreciated.

    Regards

    ReplyDelete

  6. Hi there,

    thanks for yout tutorial. I used it for my own database, edited a few things and it works great, however I habe a "charset Problem". In my Database I have entries with german "Umlaute" like "ü". These are corrupted in the output of script.

    In another script with mysqli I just used

    $db = new mysqli('localhost', 'root', 'pw123', 'dbname');
    $db->set_charset('utf8');

    in the config file and this worked, however with your DPO version I get error messages...can you provide me with the correct syntax and where to put it in?

    Thanks

    ReplyDelete