Cascading Dropdown Using Ajax, PHP, and MySQL

by

Dependent dropdown with Ajax and PHP is a common script, so I created one for developers. This post is about how to create dependent dropdowns with jQuery, MySQL, and PHP.

In this tutorial we fetch data from database, convert it to XML format, and populate it into a dropdown with jQuery/JavaScript. The reason to convert data to XML is that it can easily be populated into the next dropdown with jQuery/JavaScript in a fraction of a second.

We use three dependent dropdown lists: Category, Subcategory, and Sub-subcategory. The example uses Printer Manufacturer, Printer Type, and Printer Model.

Database Setup

Table structure with parent-child relationship:

  • manufacturers — holds manufacturer names
  • categories — holds printer types (linked to manufacturer)
  • models — holds printer models (linked to type)

dbconfig.inc.php

$dbhost = "db-host";
$dbuser = "database-user";
$dbpass = "password";
$dbname = "database-name";

// -- do not edit below this line --

// connect using PDO
try {
    $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
} catch(PDOException $e) {
    echo $e->getMessage();
    die();
}

man_list.php (Manufacturer list)

include("dbconfig.inc.php");

header("Content-type: text/xml");
echo "<?xml version=\"1.0\"?>\n";
echo "<list>\n";
$select = "SELECT * FROM manufacturers";
try {
    foreach($dbh->query($select) as $row) {
        echo "\t<item>\n";
        echo "\t\t<id>".$row['man_id']."</id>\n";
        echo "\t\t<name>".$row['man_name']."</name>\n";
        echo "\t</item>\n";
    }
} catch(PDOException $e) {
    echo $e->getMessage();
    die();
}
echo "</list>\n";

How It Works

  1. First AJAX call — On page load, populate the first dropdown and disable the other two.
  2. Second AJAX call — On change of the first dropdown (manufacturer), enable the second dropdown and populate it with types. Disable the third dropdown.
  3. Third AJAX call — On change of the second dropdown (type), populate the third dropdown with models.

JavaScript (myminiAJAX.js)

The JavaScript file contains functions for making AJAX requests:

  • createREQ() — Creates a request to the server
  • requestGET(url, query, req) — GET method request
  • requestPOST(url, query, req) — POST method request
  • doCallback(callback, item) — Handles the response callback
  • doAjax(url, query, callback, reqtype, getxml) — Main AJAX function

function.js (Callback functions)

Contains callback functions that convert XML data to dropdown items:

  • populateComp(xmlindata) — Populate manufacturer dropdown
  • populateType(xmlindata) — Populate printer type dropdown
  • populateModel(xmlindata) — Populate printer model dropdown

How it works.

  1. First ajax call -When page load

it populate the first dropdown.and disable other two selctboxes.(i.e first dropdown autocomplete).

  1. Second ajax call – Onchange dropdown (First Dropdown i.e “manufacturer” ).

enable second dropdown -Populate type type in second dropdown,disable third dropdown.

3.Third ajax Call -onchange dropdown (second dropdown i.e “type”).

Live Demo

Download