mysql table to json

Question

I am planning to use jQuery UI Autosuggest for a search form. So I need a json output which can be used by jQuery UI Auto suggest.

Here's the database

Table name recent_tags

I have tried this

First connect to db

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

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['query']);
$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.