jQuery JSON drop down not populating

Question

Hi guys, I am trying to create a drop down list using jQuery, PHP and mySQL, here is my code thus far,

HTML:

<select id="box"></select><br />
<input id="button" type="button" value="populate" />

Javascript/jQuery:

$(document).ready(function() {
    $("#button").click(function (){     
        $.getJSON("test_post.php", function(data){

            $.each(data, function(user) {
                $('#box').append(
                    $('<option></option>').html(user)
                );
            });
        });
    });
});

PHP:

mysql_connect('localhost', 'user', '1234');
mysql_select_db('json');
$test = mysql_query('SELECT user,pass FROM login WHERE user="john"');

while($row = mysql_fetch_array($test, true)) {
    $data .= json_encode($row);
};

echo $data;

I have 3 entries in the database, and when I run the 'test_post.php' file, it echoes out the JSON, but when I try and get it to populate the drop down, nothing happens!

Any idea why?

Answer 1

Try taking json_encode out of while loop and encoding it at the end. Like this:

while($row = mysql_fetch_array($test, true))
{
    $data[] = $row;
}

$data = json_encode($data);
echo $data;

[EDIT]: After OP comment:

If you just want the name of the user in the drop down, then do it like this:

while($row = mysql_fetch_assoc($test))
{
    $data[$row['user']] = $row;
}

$data = json_encode($data);
echo $data;

Answer 2

Have you tried validating the JSON? jQuery is quite picky about it's JSON: it needs to validate correctly, no comments and the right content-type! Try adding the following in your PHP to see if it helps (I always do this)

header('Content-type: application/json');

Answer 3

There are several problems in your code.

First: attempt to use undefined variable $data, you should have initialized it to an empty string before while loop: $data = '';, otherwise this can send PHP notice with the JSON response, depending on values of display_errors and error_reporting settings.

Second: as @shamittomar said, $data must be an array and json_encode() must be called only once for the whole array. For now, you're sending several JSON objects concatenated to a single string, which is wrong.

Third: value of the user function parameter in the JavaScript is actually an index of the data array, not a value, but even if user would be a value, it would be a JavaScript object with user and pass properties, not a string.

Resulting code should be (changed parts only):

PHP

$data = array();
while ($row = mysql_fetch_array($test, true)) {
    $data[] = $row;
};

echo json_encode($data);

JavaScript

$.each(data, function(i, user) {
    $('#box').append(
        $('<option></option>').html(user.user)
    );
});