Reputation: 8141

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

Upvotes: 9

Answers (3)

rebe100x

Reputation: 1493

Maybe try live instead of on. It is deprecated but I found out is more likely to work when element is not loaded.

$$('#customer').on('change', function (){

changed to

$('#customer').live('change', function (){

Upvotes: 2

MrCode

Reputation: 64536

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'].

Upvotes: 20

AlexP

Reputation: 9857

Looks as if you are creating an array of JSON objects on each itteration in select.php.

Below the array is generated in PHP and then JSON encoded, which in my option is the best way to generate JSON strings.

// select.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'],
    'name' => $row['vehicleId'] // Don't you want the name?
  );
}
echo json_encode($json);

// Test.php

$('#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]['name'] + '</option>';
    }
    $('#vehicle').html(options);
  });
});

Upvotes: 2

Using Ajax To Populate A Select Box

Answers (3)

Related Questions