How To 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>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);

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.
All 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).

Source: Load JSON data with jQuery, PHP and MySQL