mysql table to json

Question

Hi, I am planning yo use jquery UI Autosuggest for a search form.so I need a json output which can be used by Jquery UI Auto suggest.

heres the database

table name recent_tags

I have tried this first connected to db

$do = mysql_query("SELECT * FROM recent_tags where query like '%" . $_GET['qery'] . "%'"); 
while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
  $row_array['query'] = $row['query'];
        array_push($return_arr,$row_array);
    }
echo json_encode($return_arr);

but It's not working.. please guide me..

EDIT :

getting error

Warning: array_push() [function.array-push]: First argument should be an array in /pathto/my/file.php

Thanks

Answer 1

Try this:

$return_arr = Array();

$query = mysql_real_escape_string($_GET['qery']);
$result = mysql_query("SELECT * FROM recent_tags where query like '%" . $query . "%'"); 

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    array_push($return_arr,$row);
}
echo json_encode($return_arr);

Answer 2

Probably not the route you will go but I'll give this answer for completeness or just because I find it interesting:

There is also the possibility to let the database generate the JSON for you. mysqludf.org have a set of MYSQL user defined functions for JSON available here. Below is an example of converting a few fields to JSON:

select json_array(
   customer_id
   ,first_name
   ,last_name
   ,last_update
   ) as customer
from   customer 
where  customer_id =1;

If you have a lot of data to convert this may perhaps prove to be more scalable.