Iain Simpson Iain Simpson - 3 months ago 16
Ajax Question

Using Ajax To Populate A Select Box

Ok, this is my very first attempt at Ajax and its driving me insane as I really cant get my head round it. What im trying to do is populate the first box with the customers from the database, and then use the customerID to select all the vehicleID's from the database using the select.php script.
What is happening is the customers box is getting selected but when the select a customer nothing happens.

This is my Test.php file :

<?php include 'connectmysqli.php'; ?>
<html>

<head>
<meta http-equiv="Content-type" content="text/html; charset=utf-8">
<title>Select test</title>
<script src="./js/jquery/jquery.js"></script>
<script type="text/javascript" charset="utf-8">
$$('#customer')
.on('change', function () {
$.getJSON('select.php', { customerId: $(this)
.val() }, function (data) {

var options = '';
for (var x = 0; x < data.length; x++) {
options += '<option value="' + data[x][
'id'] + '">' + data[x]['reg'] +
'</option>';
}
$('#vehicle')
.html(options);
});
});

</script>
</head>

<body>
<select id="customer">
<?php
$sql = <<<SQL
SELECT *
FROM `customers`
SQL;
if(!$result = $db->query($sql)){ die('There was an error running the query [' . $db->error . ']');}
while($row = $result->fetch_assoc()){
if ($row['bussinessname'] == ''){$name = $row['title'].' '.$name = $row['firstname'].' '.$name = $row['surname'];}else
{$name = $row['bussinessname'];}
echo '<option value="'.$row['customerID'].'">'.$name.'</option>';
}
echo '</select></p>'; ?>
</select>
<select id="vehicle">
</select>
</body>

</html>


This is my select.php file :

<?php include 'connectmysqli.php'; ?>
<?php
$id = $_GET['customerId'];
$sql = 'SELECT * FROM vehicles WHERE customerID = ' . $id;
$result = $db->query($sql);

$json = array();
while ($row = $result->fetch_assoc()) {
$json[] = array(
'id' => $row['vehicleID'],
'reg' => $row['reg'] // Don't you want the name?
);
}
echo json_encode($json);

?>


I am attempting to mod this tutorial to work with the databases but so far i'm unsuccessful.
http://remysharp.com/2007/01/20/auto-populating-select-boxes-using-jquery-ajax/

In the Chrome console I get the error :

Port error: Could not establish connection. Receiving end does not exist. miscellaneous_bindings:235
chromeHidden.Port.dispatchOnDisconnect

Answer

Your customer select's change event is being assigned before the select is rendered on the page. Move the event handler into document.ready:

<script type="text/javascript" charset="utf-8">
$(document).ready(function(){
    $('#customer').on('change', function (){
        $.getJSON('select.php', {customerId: $(this).val()}, function(data){
            var options = '';
            for (var x = 0; x < data.length; x++) {
                options += '<option value="' + data[x]['id'] + '">' + data[x]['reg'] + '</option>';
            }
            $('#vehicle').html(options);
        });
    });
});
</script>

I also changed $$('#customer') to $('#customer'). Finally, fix your SQL injection vulnerability:

$sql = 'SELECT * FROM vehicles WHERE customerID = ' . (int)$id;

Casting the ID as a int will prevent SQLi here, but you should consider using a Prepared Statement.

The error you mentioned in the question looks unrelated to your code. It looks related to a Chrome extension.


Not part of the problem but here is an improved version of your code that builds the vehicle options:

$.getJSON('select.php', {customerId: $(this).val()}, function(data){
    var vehicle = $('#vehicle');

    for (var x = 0; x < data.length; x++) {
        vehicle.append(new Option(data[x].reg, data[x].id));
    }
});

The improvements are:

  • Storing the select in a variable, so that we don't have to keep querying the DOM on each iteration of the loop
  • Using new Option(...) and .append() to create the options and add them to the vehicle select. Building elements like this is better than creating raw HTML because this way, you don't have to worry about characters such as < and > in the data - which would break your HTML with the current code.
  • Your data is decoded into an array of Javascript objects, so the object notation is preferred (data[x].id) instead of data[x]['id'].
Comments