PHP Selecting MySQL Row by Category using jQuery

Submitted by nurhodelta_17 on Sunday, September 10, 2017 - 10:14.
In this tutorial, I'm going to show you how to create a simple select of MySQL row by category using jQuery. This tutorial features a select tag that whenever we change the value of a category the page or the data from table automatically change based on the chosen category or value of select tag. So, let's get started.

Creating our Database

First step is to create our database. 1. Open phpMyAdmin. 2. Click databases, create a database and name it as "category". 3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.
  1. CREATE TABLE IF NOT EXISTS `category` (
  2. `categoryid` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `cat_description` VARCHAR(30) NOT NULL,
  4. PRIMARY KEY(`categoryid`)
  5. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
  1. CREATE TABLE IF NOT EXISTS `material` (
  2. `materialid` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `categoryid` INT(11) NOT NULL,
  4.   `material_name` VARCHAR(30) NOT NULL,
  5. PRIMARY KEY(`materialid`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
category

Inserting Sample Data into our Database

Next is to insert sample data into the database that we have created. 1. Click "category" database that we have created. 2. Click SQL and paste the code below.
  1. INSERT INTO `category` (`categoryid`, `cat_description`) VALUES
  2. (1, 'Masonry'),
  3. (2, 'Carpentry'),
  4. (3, 'Wood Works'),
  5. (4, 'Steel Works');
  1. INSERT INTO `material` (`materialid`, `categoryid`, `material_name`) VALUES
  2. (1, 1, 'Deformed Bar 10mm x 6m'),
  3. (2, 1, 'Deformed Bar 12mm x 6m'),
  4. (3, 2, 'Concrete Hollow Blocks, 4'),
  5. (4, 2, 'Portland Cement'),
  6. (5, 3, 'Mixing Sand'),
  7. (6, 3, 'Washed Sand'),
  8. (7, 4, 'G.I. Tie Wire'),
  9. (8, 4, 'C.W. Nail');

Creating our Connection

Next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.
  1. <?php
  2.         $conn=mysqli_connect("localhost","root","","category");
  3.        
  4.         if (!$conn) {
  5.         die("Connection failed: " . mysqli_connect_error());
  6. }
  7. ?>

Creating our Sample Table

Lastly, we create our sample table with our category selector. We name this as "index.php".
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4.         <title>PHP Selecting MySQL Row by Category using jQuery</title>
  5.         <link href="css/bootstrap.css" rel="stylesheet">
  6.         <script src="js/bootstrap.js"></script>
  7.         <script src="js/jquery.js"></script>
  8. </head>
  9. <body>
  10. <nav class="navbar navbar-default">
  11.     <div class="container-fluid">
  12.                 <div class="navbar-header">
  13.                         <a class="navbar-brand" href="https://www.sourcecodester.com/user/224918/track">sourcecodester.com || nurhodelta_17</a>
  14.                 </div>
  15.     </div>
  16. </nav>
  17. <div class="container">
  18.         <div class="row">
  19.                 <div class="col-lg-2">
  20.                 </div>
  21.                 <div class="col-lg-8">
  22.                 <h2>Sample Table</h2>
  23.                 </div>
  24.                 <div class="col-lg-2">
  25.                 </div>
  26.         </div>
  27.        
  28.         <div style="height:10px;"></div>
  29.         <div class="row">
  30.                 <div class="col-lg-2">
  31.                 </div>
  32.                 <div class="col-lg-8">
  33.                 <select id="catList" class="btn btn-default">
  34.                         <option value="0">All Category</option>
  35.                         <?php
  36.                                         include('conn.php');
  37.                                         $c=mysqli_query($conn,"select * from category");
  38.                                         while($row = mysqli_fetch_array($c)){
  39.                                                 $catid = isset($_GET['category']) ? $_GET['category'] : 0;
  40.                                                 $selected = ($catid == $row['categoryid']) ? " selected" : "";
  41.                                                 echo "<option$selected value=".$row['categoryid'].">".$row['cat_description']."</option>";
  42.                                         }
  43.                         ?>
  44.                         </select>
  45.                 </div>
  46.                 <div class="col-lg-2">
  47.                 </div>
  48.         </div>
  49.         <div style="height:10px;"></div>
  50.         <div class="row">
  51.                 <div class="col-lg-2">
  52.                 </div>
  53.                 <div class="col-lg-8">
  54.                 <table  class="table table-bordered table-striped">
  55.             <thead  class="alert alert-success">
  56.                                 <th>Category Name</th>
  57.                                 <th>Material Name</th>
  58.             </thead>
  59.             <tbody>
  60.                 <?php
  61.                                 include('conn.php');
  62.                                 $where = "";
  63.                                 if(isset($_GET['category']))
  64.                                 {
  65.                                         $catid=$_GET['category'];
  66.                                         $where = " WHERE material.categoryid = $catid";
  67.                                 }
  68.                                
  69.                                 $result=mysqli_query($conn,"select * from material left join category on category.categoryid=material.categoryid $where");
  70.                                 while($row1 = mysqli_fetch_array($result))
  71.                                         {
  72.                                         ?>
  73.                                         <tr>
  74.                                         <td><?php echo $row1['cat_description']; ?></td>
  75.                                         <td><?php echo $row1['material_name'] ?></td>
  76.                                         </tr>
  77.                                         <?php
  78.                                         }
  79.                                 ?>
  80.                         </tbody>               
  81.                 </table>
  82.                 </div>
  83.                 <div class="col-lg-2">
  84.                 </div>
  85.         </div>
  86. </div>
  87. <script type="text/javascript">
  88.         $(document).ready(function(){
  89.                 $("#catList").on('change', function(){
  90.                         if($(this).val() == 0)
  91.                         {
  92.                                 window.location = 'index.php';
  93.                         }
  94.                         else
  95.                         {
  96.                                 window.location = 'index.php?category='+$(this).val();
  97.                         }
  98.                 });
  99.         });
  100. </script>
  101. </body>
  102. </html>
And that ends our tutorial. If you have any question or suggestions regarding the tutorial, feel free to comment below or message me here at sourcecodester.com. Hope this tutorial helps.
Tags
PHP select by category
query by category
select category
select category jquery
category jquery
select row category
select row jquery

Comments

Submitted by Josué (not verified) on Wed, 10/11/2017 - 07:41

All your Tutorials are so usefull! Täck sa Myckett!

Add new comment

About text formats