Thursday, 28 April 2016

Load Records on Select box using Ajax Jquery Mysql and PHP



In this post you learn how to load records on selection of dropdown box using Ajax Jquery Mysql and PHP. For this I have use example of brand with product. When user select particular brand name then at that time product relates to that brand name will be load with out page refresh. I have use two table, In one table have store brand name and in second table I have store product name. Both table has one common field which is brand id is a foreign key in product table. I have use simple ajax function call when user select item from select box then it send request to php page and on the basis of selected brand it select product data from product table as per selected brand name.


Source Code

Database


 --  
 -- Table structure for table `brand`  
 --  
 CREATE TABLE IF NOT EXISTS `brand` (  
  `brand_id` int(11) NOT NULL AUTO_INCREMENT,  
  `brand_name` varchar(250) NOT NULL,  
  PRIMARY KEY (`brand_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;  
 --  
 -- Dumping data for table `brand`  
 --  
 INSERT INTO `brand` (`brand_id`, `brand_name`) VALUES  
 (1, 'Samsung'),  
 (2, 'Sony'),  
 (3, 'Motorola'),  
 (4, 'Xiaomi');  
 -- --------------------------------------------------------  
 --  
 -- Table structure for table `product`  
 --  
 CREATE TABLE IF NOT EXISTS `product` (  
  `product_id` int(11) NOT NULL AUTO_INCREMENT,  
  `product_name` varchar(250) NOT NULL,  
  `brand_id` int(11) NOT NULL,  
  PRIMARY KEY (`product_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;  
 --  
 -- Dumping data for table `product`  
 --  
 INSERT INTO `product` (`product_id`, `product_name`, `brand_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);  

load_data_select.php


 <?php   
 //load_data_select.php  
 $connect = mysqli_connect("localhost", "root", "", "zzz");  
 function fill_brand($connect)  
 {  
      $output = '';  
      $sql = "SELECT * FROM brand";  
      $result = mysqli_query($connect, $sql);  
      while($row = mysqli_fetch_array($result))  
      {  
           $output .= '<option value="'.$row["brand_id"].'">'.$row["brand_name"].'</option>';  
      }  
      return $output;  
 }  
 function fill_product($connect)  
 {  
      $output = '';  
      $sql = "SELECT * FROM product";  
      $result = mysqli_query($connect, $sql);  
      while($row = mysqli_fetch_array($result))  
      {  
           $output .= '<div class="col-md-3">';  
           $output .= '<div style="border:1px solid #ccc; padding:20px; margin-bottom:20px;">'.$row["product_name"].'';  
           $output .=     '</div>';  
           $output .=     '</div>';  
      }  
      return $output;  
 }  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | Multiple Image Upload</title>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
      </head>  
      <body>  
           <br /><br />  
           <div class="container">  
                <h3>  
                     <select name="brand" id="brand">  
                          <option value="">Show All Product</option>  
                          <?php echo fill_brand($connect); ?>  
                     </select>  
                     <br /><br />  
                     <div class="row" id="show_product">  
                          <?php echo fill_product($connect);?>  
                     </div>  
                </h3>  
           </div>  
      </body>  
 </html>  
 <script>  
 $(document).ready(function(){  
      $('#brand').change(function(){  
           var brand_id = $(this).val();  
           $.ajax({  
                url:"load_data.php",  
                method:"POST",  
                data:{brand_id:brand_id},  
                success:function(data){  
                     $('#show_product').html(data);  
                }  
           });  
      });  
 });  
 </script>  

load_data.php


 <?php  
 //load_data.php  
 $connect = mysqli_connect("localhost", "root", "", "zzz");  
 $output = '';  
 if(isset($_POST["brand_id"]))  
 {  
      if($_POST["brand_id"] != '')  
      {  
           $sql = "SELECT * FROM product WHERE brand_id = '".$_POST["brand_id"]."'";  
      }  
      else  
      {  
           $sql = "SELECT * FROM product";  
      }  
      $result = mysqli_query($connect, $sql);  
      while($row = mysqli_fetch_array($result))  
      {  
           $output .= '<div class="col-md-3"><div style="border:1px solid #ccc; padding:20px; margin-bottom:20px;">'.$row["product_name"].'</div></div>';  
      }  
      echo $output;  
 }  
 ?>  

0 comments:

Post a Comment