Load JSON data with jQuery, PHP and MySQL

This post shows how to populate a select box based on the value of the another, by getting JSON data with jQuery from a PHP script that gets the data from a MySQL database.

Previous posts

As a build up to this post I have recently covered loading JSON data with jQuery, converting an array to JSON with PHP, and fetching data from a MySQL database with PHP PDO using data from my example fruit table. A couple of other relevent posts are how to iterate through an associative array, get and set form element values and adding a new option to a select with jQuery.

Update 18 November 2013

At some point, the jQuery team deprecated the use of .attr() and then removed it altogether, replacing it with .prop(). I'm not sure which versions this occurred in, but at the time of this update jQuery 1.10.2 and 2.0.3 are current and .attr() is not supported at all.

I have updated this post to use .prop() instead of .attr() and tested it with both 1.10.2 and 2.0.3 in Chrome 31 on OSX Mavericks. I've also added a link to the HTML and PHP files which can be used as-is once the test database table has been created (the SQL is in the PHP file) and the credentials added to the file.

Downloadable example

A downloadable example can be found here. This contains a PHP file which connects to the database and returns data when the fruitName parameter is specified.

ll you need to do is add in your database name, login name and password to the PHP file and create the fruit table. The SQL for doing this is included in a comment at the end of the PHP file.

The HTML file should then work as-is, as long as you run the whole thing through a web server (e.g. don't just open the HTML file in your web browser and expect it to work).

Working example

The example below doesn't actually make a JSON call with jQuery but does illustrate how the example detailed in this post works.

Select a different fruit from the first select and it will change the available varieties in the second select.

HTML Code

The initial HTML code looks like this:

<form>
    Fruit:
    <select name="name" id="fruitName">
        <option>Apple</option>
        <option>Banana</option>
        <option>Orange</option>
        <option>Pear</option>
    </select>
    Variety:
    <select name="variety" id="fruitVariety">
    </select>
</form>

The set of fruit names was already populated on the server-side and the default set of varieties could be too; I have chosen to populate them with Javascript in this example.

jQuery Code

The jQuery code needs to initially populate the variety drop down based on the value of the fruit drop down. It then needs to update the variety if the fruit drop down changes.

Assuming the PHP script to fetch the fruit is at /fruit-varieties.php do this:

function populateFruitVariety() {
   
    $.getJSON('/fruit-varities.php', {fruitName:$('#fruitName').val()}, function(data) {

        var select = $('#fruitVariety');
        var options = select.prop('options');
        $('option', select).remove();

        $.each(data, function(index, array) {
            options[options.length] = new Option(array['variety']);
        });

    });

}

$(document).ready(function() {
 
 populateFruitVariety();
 $('#fruitName').change(function() {
  populateFruitVariety();
 });
 
});

The "$.getJSON('/fruit-varities.php', {fruitName:$('#fruitName').val()}" line is what retrieves the data and it passes the currently selected fruit name value as part of the get string to the PHP script.

PHP Code

The PHP script connects to the database, retrieves the data for the selected fruit name and then returns it as a JSON encoded string.

$dsn = "mysql:host=localhost;dbname=[DATABASE NAME HERE]";
$username = "[USERNAME HERE]";
$password = "[PASSWORD HERE]";

$pdo = new PDO($dsn, $username, $password);

$rows = array();
if(isset($_GET['fruitName'])) {
    $stmt = $pdo->prepare("SELECT variety FROM fruit WHERE name = ? ORDER BY variety");
    $stmt->execute(array($_GET['fruitName']));
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
echo json_encode($rows);

Caching issues

Internet Explorer and Firefox will cache the subsequent requests made to the same fruit name but the other browsers won't, requesting them again each time. There are a couple of ways to solve this which is covered in another post.

Radio buttons instead of select boxes

I'vw written a follow up post "Load JSON data with jQuery and PHP - Radio Buttons" to show how to do the same thing but with radio buttons instead of select boxes.

Further reading

I haven't written much in the way of comments for each of the above code snippets; they've all been covered to some degree in previous posts which led up to this one. Please refer to these in the "Previous Posts" section at the top of this post or the "Related Posts" list below.

Related posts:

• jQuery JSON Ajax requests and caching
• Converting an array to JSON data with PHP
• Fetching data using PHP and PDO with bound placeholders
• Load JSON data with jQuery
• Iterate through an associative array the jQuery way
• Add a new option to a select with jQuery