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.

HTML Code

The initial HTML code looks like this:

<form>
    Fruit:
    <select name="name" id="fruitName">
        <option>Apple</option>

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

    </select>

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.attr('options');
           options[options.length] = 
        $('option', select).remove();

       $.each(data, function(index, array) {

 
 new Option(array['variety']);
        });

populat
    });

}

$(document).ready(function() {
 

 eFruitVariety();

 $('#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]";

$pdo = new PDO($dsn, $usernam
$password = "[PASSWORD HERE]";
e, $password);

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


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. 


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  "Related Posts" list below.