jQueryUI Autocomplete: Returning data from multiple columns in a MySQL database and joining them

Question

I am fairly new with jQuery, jQueryUI and its widgets. I've solved my problems with the dialog widget. One last thing before I can move on is the Autocomplete widget. To show you where I'm currently at, here's what I have so far.

Javascript

$(document).ready(function() {
    var cache = {},
        lastXhr;
    $( "#place" ).autocomplete({
        minLength: 2,
        select: function(event, ui) {
                $('#placed_id').val(ui.item.id);
            },
        source: function( request, response ) {
            var term = request.term;
            if ( term in cache ) {
                response( cache[ term ] );
                return;
            }

            lastXhr = $.getJSON( "/database/places.php", request, function( data, status, xhr ) {
                cache[ term ] = data;
                if ( xhr === lastXhr ) {
                    response( data );
                }
            });
        }
    });
});

The file places.php

<?php
$dbhost = 'localhost';
$dbuser = 'abc123';
$dbpass = 'abc123';
$dbname = 'test';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);

$return_arr = array();

if ($conn)
{
    $fetch = mysql_query("SELECT * FROM places where place_name like '%" . mysql_real_escape_string($_GET['term']) . "%' ORDER BY LENGTH(place_name) ASC"); 

    while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
        $row_array['id'] = $row['place_id'];
        $row_array['value'] = $row['place_name'];
        array_push($return_arr,$row_array);
    }
}
mysql_close($conn);
echo json_encode($return_arr);
?>

Currently, the database looks like this

------------------------------------
|place_id | place_name             |
------------------------------------
|    1    | Chicago, Illinois      |
|    2    | Hillsboro, Illinois    |
|    3    | Jacksonville, Illinois |
------------------------------------

I would want it to be more specific so I could make page where users can search either by the city, state, or both. Something like this:

---------------------------------------
|place_id | place_state | place_city  |
---------------------------------------
|    1    | Illinois    | Chicago     |
|    2    | Illinois    | Hillsboro   |
|    3    | Illinois    | Jacksonville|
---------------------------------------

Question is: What needs to be changed on both the javascript and php files so I could achieve this?

[EDIT] I want it to still return [City, State] with the place_id value hidden.

Also, any suggestions on how to better implement both are more than welcome. Thanks.

Answer 1

For MYSQL part: You can use SUBSTRING_INDEX to split your text as you want lik this:

select place_id,
       substring_index(place_name,',',1) 'Place_State',
       substring_index(place_city,',',-1) 'Place_City' 
from Places     
Where ...

Then in your php file add the new columns place_state and place_city and format it in a json format so that you can later read it using javascript :

while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
    array_push($return_arr,array('id' => $row['place_id'], 'Place_State' => $row['place_state'], 'Place_City' => $row['Place_City'));
}
mysql_close($conn);
echo json_encode(array("return_arr" => $return_arr));   

Then use javasctipt to format the json array as you want to output it to the user for example like this:

$("#return_arr").append("<table'><tr>" + /* #return_arr is html element*/
                           "<tr>" + 
                             "<th id='thead'>Place Id</th>" +
                             "<th id='thead'>State</th>" +
                             "<th id='thead'>City</th>" +
                          "</tr>");
$.each(data.return_arr,function(){ 
    var row = "<tr>" +
                 "<td>" + this["id"] + "</td>" +
                 "<td>" + this["Place_State"]   + "</td>" +
                 "<td>" + this["Place_City"]    + "</td>"+
              "</tr>";
    $("#return_arr").append(row);
});
$("#return_arr").append("</table>");

This is just an example of how you can read the json array and format it, I am not good in jQuery UI or jQuery Autocomplete but this the way you can use to output your data to the html div that your Automcomplete will display data on it.

Hope this will help you.