PHP & MySQL, Javascript, Demos, Tutorials

I've recently had the need to show different select menus depending on what was selected in the first select menu, this guide will explain how to achieve this quickly.

See the demo for a working example.

In my example I have two tables books and book_categories, the categories table holds all the categories the demo will use namely:

  • CSS
  • jQuery
  • HTML
  • PHP  

The books table holds a list of books each book belongs to a specific category the structure of the books table is as follows:

books_categories:

How does it work?

The first select menu lists all the categories then when a selection is made an ajax request is made via jQuery to change.php. A query is ran to select all books from the selected query and in turn populates the second select menu.

Getting Started

First inside a form tag, create a select menu inside the menu populate the options with the book categories.

<p><label>Book Type:</label>
      <select name='catID' id='catID'>
      <option value=''>Select</option>
      <?php
      $stmt = $conn->query('SELECT catID,catTitle FROM book_categories ORDER BY catTitle');
      while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
          echo "<option value='$row->catID'>$row->catTitle</option>";
      }
      ?>
     </select>
    </p>

Give the select menu an id for jQuery to use later in this case I used catID for both the name of the select and its id.

Next created the second select menu again give the select an id for jQuery to use.

<p><label>Book:</label>
      <select name='bookID' id='bookID'></select>
    </p>

At the bottom of the page include a call to load jQuery.

To bind the two select menus add a bind function that will run every time the select menu with an id of catID changes, each time it calls a ajax function.

The ajax function is performed with a GET request to another php file passing in the selected catID.

Upon retrieving a successful request populate the results from the php file to the second select menu by making use of .html() and injecting the new items into the select menu with an id of bookID.

<script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script type="text/javascript">
$(function() {
 
 $("#catID").bind("change", function() {
     $.ajax({
         type: "GET", 
         url: "change.php",
         data: "catID="+$("#catID").val(),
         success: function(html) {
             $("#bookID").html(html);
         }
     });
 });
            
 
});
</script>

Change.php

In change.php a connection is made to the database then the catID is collected from the GET request and a query is performed to select all records matching the selected catID.

Finally the results are looped through and echo out an option for each loop inside the option contains the bookId and bookTitle.

These will be added into the select second select menu via jQuery on the previous page.

<?php
include('config.php');

//collect the passed id
$id = $_GET['catID'];

//run a query 
$stmt = $conn->query('SELECT bookID,bookTitle FROM books WHERE catID = '.$conn->quote($id).' ORDER BY bookTitle');

//loop through all returned rows
while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo "<option value='$row->bookID'>$row->bookTitle</option>";
}

Putting it all together:

index.php

<?php require('config.php');?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Demo</title>
</head>
<body> 
 
    <form action='' method='post'>

        <p><label>Book Type:</label>
      <select name='catID' id='catID'>
      <option value=''>Select</option>
      <?php
      $stmt = $conn->query('SELECT catID,catTitle FROM book_categories ORDER BY catTitle');
      while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
          echo "<option value='$row->catID'>$row->catTitle</option>";
      }
      ?>
     </select>
    </p>

    <p><label>Book:</label>
      <select name='bookID' id='bookID'></select>
    </p>

    </form>
 
<script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script type="text/javascript">
$(function() {
 
 $("#catID").bind("change", function() {
     $.ajax({
         type: "GET", 
         url: "change.php",
         data: "catID="+$("#catID").val(),
         success: function(html) {
             $("#bookID").html(html);
         }
     });
 });
            
 
});
</script>
</body>
</html>

change.php

<?php
include('config.php');

//collect the passed id
$id = $_GET['catID'];

//run a query 
$stmt = $conn->query('SELECT bookID,bookTitle FROM books WHERE catID = '.$conn->quote($id).' ORDER BY bookTitle');

//loop through all returned rows
while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo "<option value='$row->bookID'>$row->bookTitle</option>";
}