Stuart Stuart - 1 year ago 168
PHP Question

How do I prepare multidimensional array from php mysql query for jQuery autocomplete?

I am trying to set up a jQuery autocomplete that uses results from a PHP MySql query.

I have the basic autocomplete working for a 1-d array of values, but can't get it to work with a 2-d array. I want to use a 2-d array so that one column populates the auto-complete and one column populates the value submitted in the form (either through the value of the input or another hidden value).

I know the format needs to look like this after using

[ { label: "Choice1", value: "value1" }, ... ]
but can't quite get it to look like that...

The code below is not my working code but it represents what I would like to do: display the names in the autocomplete field, but submit the page_id.

Here is my code:

$query = "SELECT name, page_id FROM table WHERE city = '{$location}' ORDER BY name";
$result = $mysqli->query($query);

while($row = mysqli_fetch_assoc($result)){
$array[] = $row;

$( function() {
var array = <?php echo json_encode($array); ?>;
$( "#input" ).autocomplete({
source: array
} );

Answer Source

When you want to distinguish value and label, you must provide an array of objects, as described in the jQuery autocomplete documentation:


Array: An array can be used for local data. There are two supported formats:

  • An array of strings: [ "Choice1", "Choice2" ]
  • An array of objects with label and value properties: [ { label: "Choice1", value: "value1" }, ... ]

As you are looking for that second option you should in PHP prepare that exact structure. This you can do as follows:

while($row = mysqli_fetch_assoc($result)){
    $array[] = array(
        "label" => $row['name'],
        "value" => $row['page_id']

This adds so-called associative arrays into the normal (indexed) array. json_encode translates associative arrays into object notation.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download