cascading dropdown using ajax Php and mysql.

dependent dropdown with ajax and php is very common to use script so I create one script for developers cascading dropdown ajax post is about how to create dependent dropdown with jQuery Mysql Php.I have seen there are many tutorial ,but this tutorial is populate dropdown using ajax with xml. In the example we will take 3 dependent dropdown list

Live Demo
Others tutorial just load dropdown / selectbox with php and in this case your dropdown may disapear for a moment,But here we are using jQuery to populate dependent drop down items.

In this tutorial we fetch data from database and covert it to xml format and populate it into dropdown with the help of jQuery/Javacript.Thge reason to convert data to xml is that it can be easily populated to next dropdown with help of jQuery/Javascript in fraction of seconds.

In some internet turorial they create the dropdown and send it as a response but it is not good so,In this totorial we send only data to the browser as response.

At the end of this article you can view its live example and can download toolkit to play.

Now lets start how to fill dropdown list using javascript,jquery,Ajax, Php and mysql.

at the end i create two ajax dropdown examples,One is ajax dropdown with javascript and second ajax dropdown with jquery.

Populate dropdown with ajax

The cascading dropdown will be done with “Parent child” relation between them. In this example we are using three drop down so there is catagory , subcategory and sub-subcatagory.

In example we populate dropdown with ajax items will be.

  1. Printer Manufacturer.
  2. Printer Type.
  3. Printer Model.

Lets digg into the logic.

Database : (Mysql)


cascading dropdown ajax

Table structure for these will be look like this.The Image shows the relation between these three tables.

first table is manufacturer, second holds catagory (Printer Type),third table holds subcatagory (Printer Model).

Code : “PHP

File: dbconfig.inc.php

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

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

// connect using PDO
try { // attempt to create a connection to database
	$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}
catch(PDOException $e) { // if it fails, we echo the error and die.
	echo $e->getMessage();
	die();
}

Configure this file after you download ajax control toolkit dropdown.

  1. db-host : Your database host (Most commonly “localhost” ).
  2. database-user : username of your mysql database.
  3. password : your database password.
  4. database-name : your database name.

Sample Code of man_list and its response.:

File : man_list.php

// manufacturer_list
include("dbconfig.inc.php");

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

Response :

< ?xml version="1.0" ?>


	30
	Apple


	31
	Brother


	32
	Canon


	33
	Compaq

Code: “Javascript”

File : myminiAJAX.js

Contains code for making ajax request and ajax loader.

function createREQ();
Create Request to server .
function requestGET(url, query, req);
If request uses GET method .
function requestPOST(url, query, req);
If request uses POST method .
function doCallback(callback, item) ;
Which function should be use to display response.
function doAjax(url, query, callback, reqtype, getxml);
Main AJAX function.

Code: “Javascript/jQuery”

File : function.js

Contains Callback Function.Convert xml data to dropdown items.

function populateComp(xmlindata)
Populate Company(Manufacturer)
function populateType(xmlindata)
Populate Printer Type.
function populateModel(xmlindata)
Populate Printer Model .
 

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).

2. 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 Free. No Cost

[social_lock]http://www.mediafire.com/?p8al84azf2hh7mz [/social_lock]

Unzip Password :buffernow.com1717

 

Happy Coding

  • Jamie

    love the code / project here but WHATS THE PASSWORD to extract the files?

    • buffernow

      Hi Jamie its
      buffernow.com1717

  • Jamie

    love the code / project here but WHATS THE PASSWORD to extract the files?

    • buffernow

      Hi Jamie its
      buffernow.com1717

  • Chris Smith

    This is probably a dumb question but I can’t figure out how to take the values of the 3 drop down and send them to a POST so I can retrieve them in php using a submit button. I have tried to make the option value=”” populate dynamically based on the selection but I can not figure out how to do it. I am sorry I am sure this is a basic fix.

    • buffernow

      i cant understand your question exactly ,
      you can take these dropdown in a form
      and after submit you can get the values.

      • Chris Smith

        Sorry for the confusion I figured out why I was not getting the data passed over when I submitted it was a glitch on my part. This is a great utility thank you for sharing it.

  • Chris Smith

    This is probably a dumb question but I can’t figure out how to take the values of the 3 drop down and send them to a POST so I can retrieve them in php using a submit button. I have tried to make the option value=”” populate dynamically based on the selection but I can not figure out how to do it. I am sorry I am sure this is a basic fix.

    • buffernow

      i cant understand your question exactly ,
      you can take these dropdown in a form
      and after submit you can get the values.

      • Chris Smith

        Sorry for the confusion I figured out why I was not getting the data passed over when I submitted it was a glitch on my part. This is a great utility thank you for sharing it.

  • Karen

    Great script! Really works well. I just have a query. When I select a sector and a subsector, it saves it perfectly to my database. However, when I go into this record to edit it, how can I get the drop down to have the saved value selected?

    • buffernow

      sorry karen
      this script is only for dependent selectbox
      for your requirement it need a lil bit complex modification.

  • Karen

    Great script! Really works well. I just have a query. When I select a sector and a subsector, it saves it perfectly to my database. However, when I go into this record to edit it, how can I get the drop down to have the saved value selected?

    • buffernow

      sorry karen
      this script is only for dependent selectbox
      for your requirement it need a lil bit complex modification.

  • Bessy

    The scripts works well. As a beginner, I tried to put jquery code in indexjq.php to a separate jquery.js file, but the code can’t work. How can I load first dropdown “manufacturer” and make ajax call in jquery.js? Thanks a lot.

    • buffernow

      hi Bessy you can use
      “document.ready”

  • Bessy

    The scripts works well. As a beginner, I tried to put jquery code in indexjq.php to a separate jquery.js file, but the code can’t work. How can I load first dropdown “manufacturer” and make ajax call in jquery.js? Thanks a lot.

    • buffernow

      hi Bessy you can use
      “document.ready”

  • ntahlah

    hi! i tried import your “DB_dropdown.sql” on phpmyadmin but i it says got error on it.. could u please help me..

  • ntahlah

    hi! i tried import your “DB_dropdown.sql” on phpmyadmin but i it says got error on it.. could u please help me..

  • webturtles

    Hi, works great, however keen to pre-populate drop-downs when editing db records. Was thinking of some way to add an extra xml tag (say ) and put a value in there if match the db value or just populating a js variable on page load from db. Hard bit seems to be adding the code into the populate functions to add the selected element to the right option. Need to try and trap the right option value and add .attr(“selected” etc) then trigger the next drop-down. Any clues to taking this forward would be much appreciated! Thanks

  • webturtles

    Hi, works great, however keen to pre-populate drop-downs when editing db records. Was thinking of some way to add an extra xml tag (say ) and put a value in there if match the db value or just populating a js variable on page load from db. Hard bit seems to be adding the code into the populate functions to add the selected element to the right option. Need to try and trap the right option value and add .attr(“selected” etc) then trigger the next drop-down. Any clues to taking this forward would be much appreciated! Thanks

  • Chelle

    it is not working..sorry…I need some help..really..pls…

    :((

  • isaac

    Please help. the php work, but looks cannot link to DB_dropdown.sql, when click the first level, nothing appear, e.g. apple….. how can i fix it?

    • buffernow

      May its javascript error.
      please give a look at firebug console.

      • isaac
        • buffernow

          Seems sql not installed properly
          Ajax Response
          Warning: Invalid argument supplied for foreach() in /home/a1943970/public_html/bksearch/man_list.php on line 10

          • isaac

            Thx. yes, u are right, this is my first time to use sql, i don’t need to install sql, i am stuipd. now work it, thank you so much.

          • isaac

            sorry, i have another problem, when i click you the third level, how can change to open a link? i try to change the sql like: (5664, 30, 98, ‘Apple-Color-Printer’, ”), but not work. can you teach me how to modify?

  • isaac

    Please help. the php work, but looks cannot link to DB_dropdown.sql, when click the first level, nothing appear, e.g. apple….. how can i fix it?

    • buffernow

      May its javascript error.
      please give a look at firebug console.

      • isaac
        • buffernow

          Seems sql not installed properly
          Ajax Response
          Warning: Invalid argument supplied for foreach() in /home/a1943970/public_html/bksearch/man_list.php on line 10

          • isaac

            Thx. yes, u are right, this is my first time to use sql, i don’t need to install sql, i am stuipd. now work it, thank you so much.

          • isaac

            sorry, i have another problem, when i click you the third level, how can change to open a link? i try to change the sql like: (5664, 30, 98, ‘Apple-Color-Printer’, ”), but not work. can you teach me how to modify?

  • kris

    Hi, are you still here? 🙁
    It’s very very nice scripts that I was looking. But I’m new in php and jquery, I need your help.
    I have 3 tables, (1). category table: id_category, category_name. (2). classification table: id_class, classification_name. (3). books table: id_book, id_category, id_class, title_book, author_book. In your scripts we have to choose 3 dropdown list, but I only need 2 dropdown list. I’ve tried to disable one of the dropdown list, but the second dropdown list wont enable. Then how to display the results in table form? not the message box?
    Please I need your help. I’ve tried to edit, googling but no results.
    Thank you very much if you will help me.
    Best regards,
    Kris

    • buffernow

      Hi Kris
      can I look at your url where you are working
      It may be jQuery issue
      thanks

  • kris

    Hi, are you still here? 🙁
    It’s very very nice scripts that I was looking. But I’m new in php and jquery, I need your help.
    I have 3 tables, (1). category table: id_category, category_name. (2). classification table: id_class, classification_name. (3). books table: id_book, id_category, id_class, title_book, author_book. In your scripts we have to choose 3 dropdown list, but I only need 2 dropdown list. I’ve tried to disable one of the dropdown list, but the second dropdown list wont enable. Then how to display the results in table form? not the message box?
    Please I need your help. I’ve tried to edit, googling but no results.
    Thank you very much if you will help me.
    Best regards,
    Kris

    • buffernow

      Hi Kris
      can I look at your url where you are working
      It may be jQuery issue
      thanks

  • dsstrainer

    Can you explain why you need the “man_id” in the model table? Won’t the type_id be enough?

    • buffernow

      Thanks for figure out
      many other things need to update in this article
      I will do that soon 🙂

  • dsstrainer

    Can you explain why you need the “man_id” in the model table? Won’t the type_id be enough?

    • buffernow

      Thanks for figure out
      many other things need to update in this article
      I will do that soon 🙂

  • Klaus Tenbrink

    Hi, thank you for this brilliant idea. This script is very important for me, but I can’t download any stuff. I liked you at g+, twitter and FB, but no “clickable” link… Please help!

  • Nitesh Shrivastava

    Hello i have implemented your code and its working fine. But when i am using hindi fonts in database it shows question marks in dropdown. Can you please tell me the issue.

  • Mark Dacoron

    Where is the link to download

    • buffernow

      sorry the script was broken
      now it is working

  • Muhon

    Hello buffernow, is it possible to make the 3rd drop-down as checklist?

  • Jojojojo

    Thank you so much for this code, you saved a lot of time and spared my keyboards life since I was about to smash it

  • KearalaPsc Tuts

    there a problem with the 3rd select box.
    please watch my video

    https://drive.google.com/file/d/0BxawGn4TxUFBeFJXVnljUjFjQTQ/view?usp=sharing

  • Tom Bu M’bre

    Hi, im having a problem trying to storeVietnamese character in the database. I set the database, table, column collation in utf8_general_ci, and I even tried using utf8_Vietnamese_cicollation. The database store it correctly but when I display it on the webpage, the characters doesn’t turn out right. I even set the webpage charset to UTF8.