Two related drop down list by using Ajax & PHP

Ajax controlled Drop down listbox Please read the basic drop down list box control tutorial. We will try to develop similar functionality by using ,Ajax (asynchronous JavaScript and XML) and PHP. The biggest advantage of using Ajax here is we need not reload the page again. With more form components ( if already filled by visitor ) we have to re-fill all the data once the page reloads. Now without sending all the information back to server we can send only cat_id of selected first category and then we will manage the second list based on cat_id value.

This demo script you can download the sample code along with table structures data at the end of this file in zip format.

How dropdown list with Ajax script works?

There are three files used in this tutorial.
  1. main.php : Display the category listbox and posts Ajax request.
  2. dd.php : Receives cat_id and collects matching subcategory from table.
  3. mainck.php : Collects the form data and display the same.

main.php

This file first displays a form with three elements, one textbox and two dropdown selection boxes. One Test box is there as part of the form to collect data and it has nothing to do with the main script managing dropdowns. It only explains how other elements can be added to same form.
<form name="testform" method='POST' action='mainck.php'>
Name:<input type=text name=fname>

The first dropdown list box display data of first table category. The table category have two columns, one is cat_id and other is category. In our dropdown box we kept cat_id as option value and category as text part. You can read more on how to populate listbox by taking data from MySQL table here. First we will connects to database using config.php

require "config.php";// connection to database 

Here is the code to add option to our first list box.
<?Php
require "config.php";// connection to database 

echo "<br>Select Category first  <select name=cat id='s1' onchange=AjaxFunction();>
<option value=''>Select One</option>";

$sql="select * from category "; // Query to collect data from table 

foreach ($dbo->query($sql) as $row) {
echo "<option value=$row[cat_id]>$row[category]</option>";
}
?>
</select>
In the above code we have used onchange event of listbox to trigger a JavaScript function AjaxFunction().

echo "<br>Select Category first  <select name=cat id='s1' onchange=AjaxFunction();>

Using this function we will collect the value of cat_id of selected category.
var cat_id=document.getElementById('s1').value;
After getting the value of cat_id we will post it to our PHP script dd.php file by GET method. You can read more on how to post data by GET method using Ajax. Here is the code.
	var url="dd.php";
var cat_id=document.getElementById('s1').value;
url=url+"?cat_id="+cat_id;
url=url+"&sid="+Math.random();
httpxml.onreadystatechange=stateck;
//alert(url);
httpxml.open("GET",url,true);
httpxml.send(null);

dd.php

Now data is posted to dd.php file. We will collect the data and sanitize the same before using in our query. We expect cat_id to be numeric value. In your case if your are using text then you have to change the sanitization code. You can read more on security and injection attack here.

if(!is_numeric($cat_id)){
echo "Data Error";
exit;
}
If you are not using integer as cat_id then you can go for alphanumeric characters check by using ctype_alnum function. Replace the function inside if condition checking.

Now to get matching record ( matching with cat_id ) we will use second table subcategory. Here is the query

$sql="select subcategory,subcat_id from subcategory where cat_id='$cat_id'";

We will get subcategory data ( subcat_id and subcategory ) from the table as an array. Same array we will use in Jason format and return to data to main.php.
$row=$dbo->prepare($sql);
$row->execute();
$result=$row->fetchAll(PDO::FETCH_ASSOC);

$main = array('data'=>$result);
echo json_encode($main);
In our main.php file we will receive the Jason formatted data. We will store data in an array variable myarray.

var myarray = JSON.parse(httpxml.responseText);

First we will remove if any old option is there in our 2nd drop down list showing subcategory.
for(j=document.testform.subcat.options.length-1;j>=0;j--)
{
document.testform.subcat.remove(j);
}
Now we will add options to our 2nd dropdown list. For this we will loop through the array. Note that the total number of elements present inside the array is stored can be found out by myarray.data.length property.
for (i=0;i<myarray.data.length;i++)
{
var optn = document.createElement("OPTION");
optn.text = myarray.data[i].subcategory;
optn.value = myarray.data[i].subcat_id;  // You can change this to subcategory 
document.testform.subcat.options.add(optn);
}
In the above code we have assigned text part and option value part of the 2nd dropdown list to subcategory and subcat_id respectively. If you want you can change this to match your requirement. Once the form is submitted the data assigned to option value only will be carried to next page mainck.php.
echo "\$_POST['fname'] = $_POST[fname]<br>
\$_POST['cat'] = $_POST[cat]<br>
\$_POST['subcat'] = $_POST[subcat]
";
With this our both dropdown boxes are ready for selection. Once you select first listbox then the cat_id will be transported to dd.php file and matching subcategory name and subcat_id is returned and used in second dropdown list box.

You can fill all the data and submit the form to get all values in next page for further processing.

New script with multiple selection drop down list box is used. This part is added in your zip file.


Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    rahul

    15-10-2013

    after selecting item from combobox i.e category, population of data in second combobox is not working. im getting empty small box.

    any help would be appreciated.
    Yusrizal Siregar

    22-11-2013

    I'm not using 2 option, but 1 option and 1 textarea, need editing on dd.php and main.php. Work well
    Nice share!
    Erica

    09-01-2014

    I've struggled to integrate this into my website for days - - can someone help me? I believe this works, but just can't get it through my thick skull... Thanks for everything!
    Max

    24-10-2014

    Thank you for your script. I got it to work but is it possible for the second select option to return both the name and an id from the mysql table ?
    For example to return information like this <option value='Value ID'>Value Name</option> ? Thank you in advance for your help.
    smo

    24-10-2014

    Yes , we can use like that and many time we use option and string different data. For that in your sql you can collect both columns and format accordingly. For example we can display student name but keep student ID in option value.

    max

    24-10-2014

    Thank you for your reply,

    my mysql dd script is like this

    $str=$str . "$r_p[lastname] $r_p[firstname] $r_p[id]".",";

    it outputs this :

    "John Smith 1, Joe Black 2,"

    This works perfectly,

    however, I would like to have in the <select> field a result like this :

    <select name="subcat" id="s2">
    <option value='1'>John Smith</option>
    <option value='2'>Joe Black</option>
    </select>

    Since I don't know how to do it, in my passing script I then use

    $subcat = filter_var($subcat, FILTER_SANITIZE_NUMBER_INT);

    this removes everything and keeps the ID, as you can see it's not a good solution.
    smo

    24-10-2014

    Thanks for pointing out that. I can understand your requirement. The script will be modified accordingly. Give me few hours.
    smo

    24-10-2014

    Download fresh copy of script now.See the changes. Now for 2nd dropdown you should get subcat_id as value and subcategory as text part of the dropdown list.Submit the form and see...
    max

    24-10-2014

    Thanks for your reply, i don't know if i downloaded the right one, but there are two things i noticed :

    1. In dd.php there is a mistake :
    $sql="select subcategory,subcat_id from subcategory where cat_id='$cat_id'";
    Should be
    $sql="select subcategory,cat_id from subcategory where cat_id='$cat_id'";

    because of your sql_dump file :

    CREATE TABLE `subcategory` (
    `cat_id` int(2) NOT NULL default '0',
    `subcategory` varchar(25) NOT NULL default ''
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    I tried running your script, but I get :

    $fname =
    $cat = 3
    $subcat = undefined

    smo

    24-10-2014

    thanks, it is fixed now. SQL is correct. The dump file is updated with subcat_id column. Download a fresh copy. Let us know if you are facing any problem.
    Peter

    26-10-2014

    ok how do I populate these dropdowns on an edit window? I am using the three drop downs to add a record, but how do I populate all three when I pull up the records to edit?
    smo

    27-10-2014

    Actually you are not editing the dropdown. You have to update record or data in table. So you can select dropdowns and arrive at the record to edit. Then the data can be placed in a text box to edit. You can see this record edit script here
    max

    28-10-2014

    It's working great ! I will send you a link of my project once I'm done ! Thank you again !
    sania

    31-10-2014

    heloo i am worid i want too show dataa through drop down list i need 4 droap down list
    CHINAGORO

    14-11-2014

    I WILL BE HAPPY TO BE A MEMBER OF THIS FORUM.
    Augustine Sechele

    26-11-2014

    hello please help me here, yes it is working fine but it is refreshing the whole form so m not able to enter any data
    patrick

    08-12-2014

    help me sir, instead of integer in cat_id field, i will use a alphanumeric .. can u give me a edited sample code. thanks a lot!
    smo

    08-12-2014

    You need to change the data checking part, in place of using is_numeric function use ctype_alnum function. The link is added to the tutorial now.
    patrick

    08-12-2014

    sir, is it possible instead if calling the cat_id in the two table .. i will call directly the value of a category and subcategory by varchar type or in a alpha numeric.. because it is redundant if i add a data in a table like cat_id, bcoz i will add the same value of category in cat_id but its in alphanumeric not int.. reply asap .. thanks!
    patrick

    08-12-2014

    sir, i already change in ctype_num .. but still not work for me .. only in that statement sir ? sorry im new sir in programming and i dont have any experience in debugging .. please help me sir .. i need to finished this part to go to the next part of my project.
    patrick

    08-12-2014

    sir smo, help me please ..
    smo

    13-12-2014

    The second table is to be changed and we can't keep cat_id to link it to first table. We have to keep category in a column in 2nd table. Then change the query to link both tables through this category. In such case we don't need two tables and we can keep one table only having data of category and subcategory.
    Hamidu

    07-01-2015

    Please after populating the dropdown, how can i save the LGA name into database since its value takes Numbers.
    pat

    13-01-2015

    it didnt save to my table.. !!
    is there any difference on how to insert in pdo and mysql ?

    anyone help me, thanks in advance..!!
    smo

    21-01-2015

    There is no difference in query part, it will change in PHP PDO functions.
    Cathy

    30-01-2015

    After making selection in category box I only get small subcat box. I haven't changed anything yet, was just testing it out. Am I doing something wrong?
    smo

    31-01-2015

    It seems you are not getting any output from dd.php. Give some cat_id value at top of the dd.php page and run it to see how data is posted.
    Cathy

    01-02-2015

    Don't know what "give some cat_id value at top of page" means for me to do
    smo

    11-02-2015

    If you are independently checking the pages then you need this, otherwise just comment this line.
    Frank

    29-03-2015

    Hi there!
    I successfully connected to my database and category (=city) and subcategory (=street) are working well.
    Now I want to use the dynamic dropdowns in Wordpress.
    Category works and the second dropdown stays empty.
    I guess the Javascript is not working properly.
    Any advice what I have to do to let it work inside Wordpress?
    Thanks,
    Frank
    meeshal

    04-05-2015

    hey you have some error in your website.. the css loads very late.. after 2 3 sec...
    smo1234

    06-05-2015

    fixed now
    Michael

    24-05-2015

    Hello, works great except when you select multiple values in the second multiple select drop down list and submit, I don't get ALL of the selected values??

    lotfi

    03-08-2015

    It works...Thanks
    okwaithaio

    26-04-2016

    Why did I open dd.php Error message "Error!: could not find driver"
    help me! please
    rohit

    16-11-2016

    is its possible to only one table through cascade three dropdown menu
    for example, i have one table that is class in class table i have four column di,standard,division and roll no
    what i want is if i select standard in first dropdown button pop up all division in standard column which i selected and after i select division on that division shows all roll no of taht division student is it possioble to do in one table
    if yes plz help me to do this stuff.
    smo1234

    19-02-2017

    What is the version of your PHP, do you have PDO support ? Check this PDO installation guide.

    Post your comments , suggestion , error , requirements etc here




    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer