Displaying linked records of a categoryLike dependant 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.
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.
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 )
MySQL database TablesThis 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.
The query to collect data from table is here
Now let us display the listbox ( dropdown box ) by taking data from table using above query.
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.
This is the backed script with only PHP code. First it connects to database
Then we collect the variable cat_id , this value we get from selection of dropdown list as selected by user.
As this value is coming from outside so better to sanitize it before using. So let us check the data first.
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.
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.
Now let us format the data to develop the Json string.
Json data will be received by our ajax-listbox-demo.php page.
Let us collect message part first and display them inside msg div tag
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.
Using the above code we can display the records.
The dump file to create MySQL tables is here.
Ajax & PHP scripts Creating XMLHttp object in different browsers Sample Code: Get Method of Ajax form submission Server Clock:: Display real-time date and time of server Dependant drop down list box using Ajax & PHP Email validation using Ajax in a form Getting customer details by entering customer id using Ajax Progress Bar using Ajax Progress Bar using MySQL PHP & Ajax Displaying Message at client side using Ajax & PHP Web page HTML form validation using Ajax and PHP Form validation with onBlur event using Ajax and PHP
Json support in PHP json_encode to generate Json string from PHP Array data Json Data formatting to return database records to main script Searching MySql database as we type using Ajax Displaying all records based on selection of a drop down list box