php select and json encode issue

Question

I'm building a website to learn PHP and am making an autosuggest from Jquery Ui.

Here's my code ( I took this code from a post on SO a long time ago, and I'm not 100% what it does, so if anyone could explain, it would be helpful!) This code is from suggest.php, which I call to from my jquery code (which I think is working, so I didn't post it, but can If you need it!)

<?
include("config.php");

$queryString = strtolower($_GET["q"]);

$return = array();
$query = mysql_query("SELECT name FROM company WHERE name LIKE '$queryString%' UNION SELECT cat FROM cat WHERE cat LIKE '$queryString%' UNION SELECT subcat FROM subcat WHERE subcat LIKE '$queryString%' LIMIT 10");
while ($row = mysql_fetch_array($query)) {
     array_push($return,array('label'=>$row['name'],'value'=>$row['name']));
}
echo(json_encode($return));

?>

Right now this is making the autosuggest work, but only with the same results (example, if I type "Johns" it comes up with "Johns Hot Dogs" as a suggestion, but If I type "fjfjdjf669959" then it comes up with "Johns Hot Dogs" as well.

I'm doing a Mysql Union because I'm trying to populate my autosuggest with the name row from company table, the cat row from cat table, and the subcat row from subcat table.

Why is this not working?

Thanks for any and all help!!

My JQUERy code looks like this:

<script>
    $(function() {
        $( "#search" ).autocomplete({
            source: "suggest.php"
        });
    });
</script>

Answer 1

First of all your php code is vulnerable to SQL injection attacks. Furthermore, the mysql_* functions are deprecated. Instead, use PDO.

Your code fails because you're reading the wrong query variable. $_GET['q'] is empty since the jQuery UI autocomplete plugin uses the parameter term for the search query. With an empty $queryString, you execute the SQL query

SELECT name FROM company WHERE name LIKE '%'   -- UNION ...

which of course just returns everything. You want:

<?php
include("config.php");
$db = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if (!isset($_GET['term'])) {
    header('HTTP/1.0 400 Bad Request');
    echo 'Missing term parameter in request';
    exit();
}
$queryString = strtolower($_GET["term"]);
$query = $db->prepare("SELECT name FROM company WHERE name LIKE :qs" .
  " UNION SELECT cat AS name FROM cat WHERE cat LIKE :qs" .
  " UNION SELECT subcat AS name FROM subcat WHERE subcat LIKE :qs " .
  " LIMIT 10");
$query->execute(array(':qs' => $queryString . '%'));
$query->setFetchMode(PDO::FETCH_NAMED);
$result = array_map(function($row) {
    return array('label'=>$row['name'],'value'=>$row['name']);
}, $query->fetchAll());

header('Content-Type: application/json');
echo(json_encode($result));

Here is a live, downloadable (incl. database) demo.