Problems with php select and json encode

Question

I am building a website to learn PHP and am doing autosuggest from Jquery Ui.

Here is my code (I made it from the SO post a long time ago, and I'm not 100% what it does, so if someone could explain it, it would be useful!) This code is from a suggestion. php that I call from my jQuery code (which I think works, so I did not publish it, but I 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 does the work with startup, but only with the same results (for example, if I type “Jones”, “Johns Hot Dogs” appears as a sentence, but if I type “fjfjdjf669959” then this will happen with “Johns Hot Dogs "

I am doing Mysql Union because I am trying to populate my autosuggest with the name row from the company table, cat rows from the cat table and sub- lines < from the sub- tables < .

Why is this not working?

Thanks for any help!

My JQUERy code is as follows:

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

Answer 1

First of all, your php code is vulnerable to SQL injection attacks . In addition, mysql_ * functions are deprecated . Use PDO instead.

Your code crashes because you are reading the wrong request variable. $_GET['q'] empty because the jQuery UI autocomplete plugin uses the term parameter for a search query. With an empty $queryString you are executing an SQL query

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

which of course just returns everything. Do you want to:

 <?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 demo (including a database) .