Pokrowsky Pokrowsky - 2 months ago 11
JSON Question

PHP json_encode in autocomplete

I have

<form action='' method='post'>
City name <br /><br />
<input type='text' name='search' value='' class='auto'>
</form>


and

<script type="text/javascript">
$(function() {

//autocomplete
$(".auto").autocomplete({
source: "search.php",
minLength: 1
});

});
</script>


This is autocomplete. And now I receive fields from MySQL base in search.php file:

if(isset($_GET['term']))
{
$city = array();
$airport = array();
$citysearchsql = mysqli_query(db(),"SELECT * FROM citycode WHERE cityname LIKE '%".$_GET['term']."%' ");
while($citysearchresult = mysqli_fetch_array($citysearchsql))
{



$airnamesql = mysqli_query(db(),"SELECT * FROM airports WHERE citycode='".$citysearchresult['citycode']."' ");
while($airnameresult = mysqli_fetch_array($airnamesql))
{
$airport[] = $airnameresult['airportname'];
}
$city[] = $citysearchresult['cityname'];
}




echo json_encode($city);

}


Base has two tables
citycode
and
airports
. When user writes city name, need to show city name and under city name show airport name, like here

http://joxi.ru/krD8oY1u0Nb0kr

I cant do it with
json_encode
.

Answer

Optimized to be just one query:

if (isset($_GET['term'])) {
        $city = array();
        $citysearchsql = mysqli_query(db(),"SELECT a.airportname, c.cityname FROM citycode c INNER JOIN airports a ON c.citycode = a.citycode WHERE c.cityname LIKE '%".$_GET['term']."%' ORDER BY c.citycode ASC");
        while($citysearchresult = mysqli_fetch_array($citysearchsql))
        {
            if (!in_array($citysearchresult['cityname'], $city)) {
                $city[] = $citysearchresult['cityname'];
            }
            $city[] = $citysearchresult['airportname'];   
        }
        echo json_encode($city);
}