Warning: Trying to access array offset on value of type bool in /var/www/vhosts/tomelliott.com/httpdocs/wp-content/themes/tomelliott/single.php on line 12

Dynamically generate a select list with jQuery, AJAX & PHP

09 March, 2014 by Tom Elliott

This post runs through the steps needed to generate the values of one select list based on the chosen value from another select. We will be using AJAX (Asynchronous JavaScript and XML), as this avoids the need to refresh the whole page, giving a smoother experience to the user and avoiding unnecessary calls to the server.

The AJAX call will send the selected drop down value to a PHP script which will return all matching values from a table in a MySQL database, which will then be used to populate our second drop down. We will also be using the jQuery framework to make the AJAX call and to detect the select list change.

The example below will be using an imaginary tea rota selection scenario, where we will select names of people based on their favourite beverage.

The PHP

Let’s first look at the PHP code to connect to the database and select the values from a table based on a GET parameter. The first line establishes the database connection, followed by a select query to return the values from a table. We will be using these values to populate our second select list. The PHP then loops through each row in the table, echoing out the HTML for the select options.

<?php
$connection = mysqli_connect("localhost", "db-username", "db-password", "db-name");

$selectvalue = mysqli_real_escape_string($connection, $_GET['svalue']);

mysqli_select_db($connection, "db-name");
$result = mysqli_query($connection, "SELECT tbl_drinks.drink_name FROM tbl_drinks WHERE tbl_drinks.drink_type = '$selectvalue'");

echo '<option value="">Please select...</option>';
while($row = mysqli_fetch_array($result))
  {
  	echo '<option value="'.$row['drink_name'].'">' . $row['drink_name'] . "</option>";
  	//echo $row['drink_type'] ."<br/>";
  }

mysqli_free_result($result);
mysqli_close($connection);

?>

You’ll obviously need to replace the database connection values with your own details along with your table and field name(s) in the SQL statement. Save your file, e.g. ajax-getvalues.php and check that everything is working by passing the GET parameter in the URL, i.e. ajax-getvalues.php?svalue=coffee. You might want to use a regular ‘echo’ to output the values to test.

Note, if you are using numerical values, you don’t need the apostrophes around '$selectvalue' in the SQL statement

The HTML

Now we’ll create the two select lists and assign the ID’s as ‘list-select’ and ‘list-target’ in this example. Our first select list will have a basic set of options and the second select has no option values, as these will be populated by the AJAX.

<select name="list-select" id="list-select">
     <option value="">Please select..</option>
     <option value="tea">tea</option>
     <option value="coffee">coffee</option>
     <option value="water">water</option>
</select>

<select name="list-target" id="list-target"></select>

The jQuery

Now to bring it all together. Here’s the jQuery that listens for the select list change, makes the AJAX call and populates the target select.

$(document).ready(function($) {
  var list_target_id = 'list-target'; //first select list ID
  var list_select_id = 'list-select'; //second select list ID
  var initial_target_html = '<option value="">Please select a colour...</option>'; //Initial prompt for target select

  $('#'+list_target_id).html(initial_target_html); //Give the target select the prompt option

  $('#'+list_select_id).change(function(e) {
    //Grab the chosen value on first select list change
    var selectvalue = $(this).val();

    //Display 'loading' status in the target select list
    $('#'+list_target_id).html('<option value="">Loading...</option>');

    if (selectvalue == "") {
        //Display initial prompt in target select if blank value selected
       $('#'+list_target_id).html(initial_target_html);
    } else {
      //Make AJAX request, using the selected value as the GET
      $.ajax({url: 'ajax-getvalues.php?svalue='+selectvalue,
             success: function(output) {
                //alert(output);
                $('#'+list_target_id).html(output);
            },
          error: function (xhr, ajaxOptions, thrownError) {
            alert(xhr.status + " "+ thrownError);
          }});
        }
    });
});

We first set up a few variables for the select list IDs, along with an initial option for our target select that prompts the user to make a selection from the initial select.

Then we setup an event listener to detect when our first select list is changed and assign the selected value to a ‘selectvalue’ variable. If the first ‘please select’ option is chosen (with a blank value) however, the second select list will revert to the initial prompt. If the selected value is not blank then we execute the AJAX call.

We also set the value of the target select to ‘Loading..’ so the user knows something is happening. This may only flash momentarily so you may wish to disable this.

The AJAX call requests our PHP script, using the selected value as the GET parameter. On successful execution of the script, it will set the HTML of the target select with the option values we’re echoing out with the PHP. There’s also some error handling thrown in for good measure which may help us identify any issues such as connectivity problems.



Parse XML with jQuery & AJAX for a select list »


29 Comments

  • Dave says:

    I’m going to have to question whats wrong with the “copy table to” section in operations?

    https://www.dropbox.com/s/svjrv9edeyi2ntr/Screenshot%202014-04-17%2009.19.37.png

  • neilb says:

    Thanks for the well worked example. However, think line 12 of the php section just needs a minor edit:

    The $row[‘drink_type’] should instead be $row[‘full_name’]

    Whole statement should therefore be:

    echo ” . $row[‘full_name’] . “”;

  • Matthew James says:

    Thanks for this, really easy to follow, implemented in minutes.

    As a JS/AJAX newbie, the only question I have is how to initiate the call upon page load as well as on change of the first select?

    • Tom Elliott says:

      Hi Matthew, what I would look to do is take all the code from the select .change function into it’s own separate function. Then this new function can be called from both .change and $(window).load . Hope that helps 🙂

      • Matthew James says:

        Thank you ever so much! Still took me a couple of hours of fiddling but I got there in the end. 🙂

  • Joshua says:

    Hi there, thank you for the awesome code! I was wondering how can i get the selected GET value in a query in the same PHP script.

    My code looks like this:

    $selectvalue = mysql_real_escape_string($connection, $_GET[‘svalue’]);

    $result = mysql_query(‘SELECT loadscriptnaam FROM Loadscripts WHERE vbox_md5 IN (SELECT md5naam FROM Virtualbox WHERE virtualboxnaam = “$selectvalue”‘);

  • Joshua says:

    It seems that the PHP script is not returning the selected GET value.. How can I fix this?

    • Tom Elliott says:

      Hi Joshua, I’d first test the PHP script works by running the URL direct e.g. ajax-getvalues.php?svalue=value. If this works, then I’d check you’re passing the selectvalue OK in the JS – e.g. console.log(‘ajax-getvalues.php?svalue=’+selectvalue); right before the AJAX request

  • Shuaib says:

    I have a question, How can get value in php from the select list created by ajax????

  • Mary says:

    Thanks for the tutorial. I have a question, how do I access and use the value of the select target after it is returned and then seleceted? I want to pass this value to another request to get results to be displayed.

  • Soft Sunrise says:

    very nice but why now post as a demo and source

  • gstratt says:

    I’ve been struggling to make PHP+MYSQL+AJAX+HTMLFORMS work for quite a while, you’re tutorial really helped me put those concepts together for a project I’m working. THANK YOU!

  • Wilber says:

    Very helpful, Very clean and light script. Great Work!

  • Jani says:

    Hello, i found this tutorial very useful! I would like to make 2nd dropdown depend on first one, let’s say that i got database which includes two tables Brand and Model and for the first list i need the Brand and when i select the brand it gets models for that brand?

  • Peter says:

    Thanks for your tutorial.
    One addition I’d like help with is setting the ‘selected’ flag within the select list.
    I want to implement this within an “edit” form, not just to add to the database, so I’d need to be able to pull the values for both dropdowns, and set them as “selected” within the tag.
    Any suggestions would be most welcome.

  • Marie Michelle says:

    Hi, very nice tutorial. I actually implement this in my system. But I have one concern, how to add comma in…
    echo ” . $row[‘t_lname’] . $row[‘t_fname’] . “”;

    that is the code. I really dont know how and I searched in google but no answer. Hope you can help me:) Thanks!

    • Marie Michelle says:

      Got it!
      . $row[‘t_lname’] .’,’. $row[‘t_fname’] . “”;

      Again thank u so much for this tutorial. Really helped me alot:)

  • Zahangir Ahammad says:

    how to catch the list_target_id change event? like list_selected_id

  • Tommy says:

    can this cascade to a third dropdown list? This is my scenario: All data is pulled from the db, including the initial list, so, Catalog Year > Departments > Courses

    Select the catalog year to display the correct departments. Select the department to find the correct course. Course is dependent on both the catalog year and dept.

  • Kragh says:

    Your solution is wrong. The server side (PHP) should produce data only (JSON), the client side (javascript) should receive this data and create option elements dynamically and append them to select.

    • BerniZ says:

      I agree!
      The target <select will look right, but it won't function because the DOM (the 'software' behind the page) is not aware of the new version of the 'select ' list. For example if you have a list_target_id.change function, it will not work after the ajax call.

  • Sarah O'Sullivan says:

    Hi,

    Should this method work if I am populating a select list based on the date selection of an input=date?

    Thanks,
    Sarah

  • PHP is highly susceptible to SQL Injection! Use prepared statements!

    Just using MySQLi does not make code SQL Injection proof.

  • Shanawaj says:

    Sir, thank for your tutorial and by passing the svalue in php its working but what to do with the javascript. how to link the file do we need to create three files 1. HTML. 2. PHP. 3 JS.

    Thanks in advance

  • Siggi says:

    Thanks very much for this tutorial. It was very helpful

  • I love this and it seems pretty straight forward but I am not sure where to place the JQUERY code and further how it is called? Is there a downloadable demo?

  • Great one. This is good example for auto loaded drop down using jQuery. This was so easy to follow and exactly what I was looking for.
    Good job and well explained. It is massively helpful.

    Thanks.

  • Gaetan Martin says:

    Great! Finally a tutorial that explains exactly what I’ve been looking for … Thank you Tom