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

Question

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 json_encode(): [ { 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;
}

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

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

source

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 21, 2016 at 14:58

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stuart Over a year ago

Thank you so much, trincot. This is exactly what I was looking for. I knew what the structure needed to be but wasn't exactly sure how to do it (I tried many different variations to create the array). Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

source

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

source

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related