如何将数据从mysql转换为jquery数组以进行自动完成？

Below is snippet of jquery for autocomplete:

var columns = [{
    name: 'Color',
    minWidth: '100px'},
{
    name: 'Hex',
    minWidth: '70px'},
    {
     name: 'Testy',
    minWidth: '70px'}],
    colors = [['White', '#fff','test1'], ['Black', '#000','test2'], ['Red', '#f00','test3'], ['Green', '#0f0','test4'], ['Blue', '#00f']];


$("#search").mcautocomplete({
    showHeader: true,
    columns: columns,
    source: colors,
    select: function(event, ui) {
        // Set the input box's value
        this.value = (ui.item ? ui.item[1] : '');
        // Set the output div's value
        $('#outputDiv') && $('#outputDiv').text(ui.item ? ('You have selected ' + ui.item[1]) : 'Select a color');
        return false;
    }
});

This will output something like this for the autocomplete:

ANd when highlighted item chosen , the second item in the array will be selected in the textbox:

Its because I've set here to choose the second item in the array via this code:ui.item[1]

But I would like to replace the jquery array with value from database:

Say I fetch data from database to be filled in the colors array:

<?php

    $sql="SELECT item1,item2,item3 FROM loc_coordinate";
    $result = mysqli_query($mysqli,$sql) or die(mysqli_error());
    $test=array();
    if($result)
    {
        while($row=mysqli_fetch_array($result))
        {
          //how do I convert this 3 items into this jquery array(`colors = [['White', '#fff','test1'], ['Black', '#000','test2'], ['Red', '#f00','test3'], ['Green', '#0f0','test4'], ['Blue', '#00f']];`)

            echo $row['item1'].">".$row['item2'].>".$row['item3']." 
";

        }
    }
?>

Assuming all retreived db rows have item1,item2 and item3 values, then this should do it :

...

if($result)
    {
        echo 'var colors = [';
        while($row=mysqli_fetch_array($result))
        {    
            echo '[';

            echo '\''.$row['item1'].'\',';
            echo '\''.$row['item2'].'\',';
            echo '\''.$row['item3'].'\',';

            echo '],';

        }
        echo  '];';
    }

...

It will output something like :

var colors = [['row_01','row_02','row_03',],['row_01','row_02','row_03',],['row_01','row_02','row_03',],];

where row_0x is the retreived value, make this be outputted inside the <script></script> tags and when generated from server to the client side JavaScript will use it.

A cleaner solution is to use the PHP built in json_encode() function like :

if($result)
    {
        $output = 'var colors = ';
        $all_rows = [] ;
        while($row=mysqli_fetch_array($result)) 
        {    
            $all_rows[]=[$row['item1'],$row['item2'],$row['item3']];
        }
        $output.= json_encode($all_rows).';';
        echo  $output;
    }

This will output something like :

var colors = [["1","2","3"],["1","2","3"],["1","2","3"],["1","2","3"]];

(thanks @MikeBrant for the comment)

You may also consider a different approach by separating your html and PHP files. The PHP file will output encoded Json results wile the HTML file may use Ajax within jQuery to retrieve them. There is many good tutorials out there. Here is one of them.

This worked for me..I retrieved data from ajax in json format.

<script>

/*
 * jQuery UI Multicolumn Autocomplete Widget Plugin 2.2
 * Copyright (c) 2012-2015 Mark Harmon
 *
 * Depends:
 *   - jQuery UI Autocomplete widget
 *
 * Dual licensed under the MIT and GPL licenses:
 *   http://www.opensource.org/licenses/mit-license.php
 *   http://www.gnu.org/licenses/gpl.html
*/
$.widget('custom.mcautocomplete', $.ui.autocomplete, {
    _create: function() {
      this._super();
      this.widget().menu( "option", "items", "> :not(.ui-widget-header)" );
    },
    _renderMenu: function(ul, items) {
        var self = this, thead;

        if (this.options.showHeader) {
            table=$('<div class="ui-widget-header" style="width:100%"></div>');
            // Column headers
            $.each(this.options.columns, function(index, item) {

                //hide the header
                //table.append('<span style="float:left;min-width:' + item.minWidth + ';"> ' + item.name + '</span>');
            });
            table.append('<div style="clear: both;"></div>');
            ul.append(table);
        }
        // List items
        $.each(items, function(index, item) {
            self._renderItem(ul, item);
        });
    },
    _renderItem: function(ul, item) {
        var t = '',
            result = '';

        $.each(this.options.columns, function(index, column) {

            t += '<span style="float:left;min-width:' + column.minWidth + ';">' + item[column.valueField ? column.valueField : index] + '</span>'
        });

        result = $('<li></li>')
            .data('ui-autocomplete-item', item)
            .append('<a class="mcacAnchor">' + t + '<div style="clear: both;"></div></a>')
            .appendTo(ul);
        return result;
    }
});
//ajax starts
 $("document").ready(function(){          
            var data = {
              "action": "test"
            };
            data = $(this).serialize() + "&" + $.param(data);
            $.ajax({
              type: "GET",
              dataType: "json",
              url: "store2.php", //Relative or absolute path to response.php file
              data: data,
              success: function(data) {
                //$("#showPlace").html("<br />JSON: " + data );
               // alert("Form submitted successfully.
Returned json: " + data["json"]);
              // alert(data);
                // console.log(data);
                  var columns = [{
                  name: 'level',
                  minWidth: '200px'},
              {
                  name: 'subject',
                  minWidth: '70px'},
                  {
                  name: 'catid',
                  minWidth: '70px'}
                  ],

                  colors = data;
                 var selectThis=$("#search").attr("class");//textbox class determines which item in array to select
                 var valueV=$("#search").attr("value");
              $("#search").mcautocomplete({
                  showHeader: true,
                  columns: columns,
                  source: colors,
                  select: function(event, ui) {
                      // Set the input box's value
                      this.value = (ui.item ? ui.item[selectThis] : '');

                      //search for nearest value


                      // Set the output div's value
                      $('#outputDiv') && $('#outputDiv').text(ui.item ? ('You have selected ' + ui.item[selectThis]) : 'Select a color');
                      return false;
                  }
              });
                            }
                          });

                      });





</script>