Multiple Dropdown Lists Using jQuery Ajax and PHP

jquery logo.png

In this course you will learn how to populate a dropdown list based on the value of another dropdown list. This is commonly used for countries and states.

For example:

dropdownExample.jpg

You will be creating 4 files:

  • dbConfig.php

  • index.php

  • style.css

  • getStates.php

Join the course and get access to high quality video tutorials explaining every step within this lesson. Plus, get access to hints and tips now covered in this lesson. You will also have access to the instructor just in case you have any issues.

STEP 1

We will start by considering the relationships required for our database.

We will need a table for the countries and a table for the states.

The two tables will have a one-to-many relationship i.e. one country will have many states.

databaseERD.png

Note how the two tables are linked. We have the countryId as a foreign key in the states table.

We can now create the tables. You can download the SQL dump here or create your own tables based on the above relationship diagram.

STEP 2

We can now create a connection script to connect to the database. Your connection details will differ depending on your platform and login credentials. I will be using a MySQL database and PDO connection.

Save this file as required. I'll name mine dbConfig.php.

STEP 3

Create a file called index.php.

Now we will create the HTML for the dropdowns.  We will start with a basic HTML5 template and connections to Bootstrap css, our own css styles and jQuery:

STEP 4

Now the structure for the dropdowns. Since we are using Bootstrap I have added some form classes to make things easier to style.

STEP 5

We want the states dropdown to be hidden by default and only show when the country has been selected.

We therefore create our style.css page and add the following code:

STEP 6

The country dropdown will need to be populated with the list of countries when the page loads. We can add the required php code and database call at the top of the page:

STEP 7

We will now loop through the results and create a separate option list element for each country (lines 24 to 28):

STEP 8

When we select a country from the dropdown country list we want the state dropdown list to be updated. Therefore we need to perform an action when the country list has been changed.

We add an onChange attribute which is calling a function called getStates() on line 22.

We pass in this.value which is the id of the selected country.

STEP 9

We can now write the jQuery function called getStates.

To check that the country dropdown is passing the value of the selected country to the function we can just use a simple alert command. Lines 15 to 19.

If everything is working you should have an alert appear when you change the country. The id of the country should appear in the popup.

You should see something like this:

popupForCountryDropdown.jpg

STEP 10

We can now start the rest of the jQuery coding.

Line 16: we create a function and accept the argument countryId. This is the this.value we passed into the function on line 37.

Line 17: by default the states select list is hidden so we now show it

Line 18: we present a 'Loading...' message while the ajax call is being processed below

Line 19: this is an ajax request

Line 20: we are using a POST method

Line 21: the URL of the page we are sending the data to (we have yet to create this page)

Line 22: the dataType that we are expecting back from our request

Line 23: the data that we are posting to the page on line 19. We are sending the country id.

Line 25: upon completion of the processing we should receive some data back. 

Line 26: we update the state dropdown

STEP 11

All we need to do now is create the getStates.php page:

Line 2: we check if we actually received a value for the country

Line 3: we require our database connection

Line 4: we assign the posted country id value to the variable $countryId. Note that we have NOT considered any sanitization of values which you MUST do on a live server!

Lines 5 to 8: We search for all states belonging to the country. I am using prepared statements here.

Line 10: echo out a default ' Select State...' message. This will be the first option in the new select list

Lines 11 to 13: we loop through the results and build up a list for the state dropdown.