Ajax record listing from mysql table after selecting category from main list

Like dependent two list boxes we will develop one application in which we will select a category in 1st drop down list box and then all the records of this category will be displayed.

In our example we have taken continents and countries. If we are selecting Europe from the drop down list then all the countries of Europe will be displayed. Here we have restricted to 6 countries for each continent.
Ajax List box

Once the page loads we have shown all the records of all categories and then we can narrow down this record by selecting one continent from the drop down list box.

We have used GET method of posting form data to PHP script. This PHP script receives the cat_id by GET method and returns with all the records having that cat_id. If the cat_id is equal to zero ( no particular selection ) then all records are displayed.

DEMO of Ajax Listbox script

How this script works?

You should have good knowledge about basics of Ajax , Json, GET method of Ajax and managing MySQL database using PHP

There are three files used in this script. ( Download link is at bottom of this page )

config.php : Database connection file to store login details of database connection 
ajax-listbox-demo.php : This is the main file having Ajax code and display all records.
ajax-listbox-demo2.php : Backend PHP script which receives variable cat_id and returns matching records.

MySQL database Tables

This script uses two MySQL database tables to store data. In this demo we used example of continent and country, in your case you can use for any other combination.
First table plus2_cat stores name of the continent as cat_name. Another column stores unique id of each category as cat_id.

Second table plus2_subcat stores subcategory names linked to first table through cat_id. Each subcategory is connected to first table category field via cat_id. So we have three fields in our plus2_subcat. One is unique number subcat_id , second is subcat_name third is cat_id.

ajax-listbox-demo.php

This file has two parts. First part is JavaScript code to execute Ajax and second part is displaying dropdown listbox data by using table plus2_cat.
The query to collect data from table is here

$query="SELECT * FROM  plus2_cat ORDER BY cat_name";

Now let us display the listbox ( dropdown box ) by taking data from table using above query.

echo "<select name=cat_id onChange="ajaxFunction()"><option value=0>Show All</option>";
foreach ($dbo->query($query) as $nt) {
echo "<option value=$nt[cat_id]>$nt[cat_name]</option>";
}
echo "</select>";

In above code we have used onChange event of dropdown list to trigger the JavaScript function. So every time we change the option or select a new option the JavaScript function is executed to run the Ajax script.

Ajax Script

All the code is kept inside a JavaScript function. Here we used Ajax Get method to post the selected cat_id to backend script. Here is the code to collect the cat_id and post to ajax-listbox-demo2.php

var url="ajax-listbox-demo2.php";
var cat_id=document.myForm.cat_id.value;
url=url+"?cat_id="+cat_id;
url=url+"&kid="+Math.random();
//alert(url)
httpxml.onreadystatechange=stateChanged;
httpxml.open("GET",url,true);
httpxml.send(null);

We will receive the Json array with value of subcat_id and subcat from backend script. To display them in our div tag display we will loop through and collect each data.

We have used msg div to display messages while processing the user request.

ajax-listbox-demo2.php

This is the backed script with only PHP code. First it connects to database

require "config.php"; // Database Connection

Then we collect the variable cat_id , this value we get from selection of dropdown list as selected by user.

@$cat_id=$_GET['cat_id'];

As this value is coming from outside so better to sanitize it before using. So let us check the data first.

if(!is_numeric($cat_id)){
$message.="Data Error |";
exit;
}

Now let us build the query. If the user not selected any option ( first time page loading ) then the cat_id value will be 0 or blank. In other hand if user has selected one option then cat_id will have value 1 or more than 1.

if($cat_id>0){
$sql="select subcat_id, subcat_name from plus2_subcat where cat_id=$cat_id order by subcat_name";
}else{
$sql="select subcat_id, subcat_name from plus2_subcat order by subcat_name ";
$cat_id=0;
}

After generating query it is time to get the matching record from mysql table using PDO. We will get an array of data returned from plus2_subcat table.

$row=$dbo->prepare($sql);
$row->execute();
$result=$row->fetchAll(PDO::FETCH_ASSOC);

Now let us format the data to develop the Json string.

@$main = array('data'=>$result,'value'=>array("cat_id"=>"$cat_id","message"=>"$message"));
echo json_encode($main);  // Json string returned

Json data will be received by our ajax-listbox-demo.php page.

var myObject = JSON.parse(httpxml.responseText);  // Received the data

Let us collect message part first and display them inside msg div tag

var msg=myObject.value.message;
if(msg.length > 0){document.getElementById("msg").innerHTML=msg;}
else{document.getElementById("msg").style.display='none';}

Now we will collect the data array and display inside a div tag. For this we will use one PHP for loop. The loop will execute based on the number of records present in side array. So we have used myObject.data.length as upper limit of the for loop.

var str="<table width='50%'  align=center><tr><th align='left' bgcolor='#ffff00'>ID</th><th align='left' bgcolor='#ffff00'>Name</th></tr>";
var color="#f1f1f1";
for(i=0;i<myObject.data.length;i++)
{
if((i%2)==0){color="#ffffff";}
else{color="#f1f1f1";}
str = str + "<tr bgcolor="+color+"><td>" + myObject.data[i].subcat_id + " </td><td>"+ myObject.data[i].subcat_name + "</a></td></tr>"
}
str = str + "</table>" ;
document.getElementById("display").innerHTML=str;

Using the above code we can display the records.

The dump file to create MySQL tables is here.
Ajax Json XML

Subscribe to our YouTube Channel here

zeno	29-04-2012
Thanks for the code. But how will I do if I want to have 2 dropdown which are populated with database and based on the selection of the two dropdown the result is display. Please help me.

Ayaz Khan	08-10-2016
Hello friend, I want to do this in Zendframework 2, How it is possible? please help me.

Website Designing	12-08-2017
Great!! This tutorial is massively helpful. This was so easy to follow and exactly what I was looking for. It just made my work easier. Thanks.

Displaying linked records of a category

How this script works?

MySQL database Tables

ajax-listbox-demo.php

Ajax Script

Subscribe