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 alt text

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

First, we need more information. What does "it's not working" mean? Any error messages, etc? — Charles Hooper, Oct 28 '10 at 06:23
What is the error message you get? And what is the jquery code you use? BTW, don't forget to escape your $_GET["qery"], cause right now it is vulnerable for SQL injections. — Rene Terstegen, Oct 28 '10 at 06:24
Second, you really shouldn't pass user-provided vars ($_GET['qery'], for example) directly into a SQL string. — Charles Hooper, Oct 28 '10 at 06:24
$_GET['qery'] is meant to be like that or should it be $_GET['query'] — Ashwini Dhekane, Oct 28 '10 at 06:27
as you have given here your result is in $do and you are running fetch on $fetch — Ashwini Dhekane, Oct 28 '10 at 06:30
@Ashwin Dhekane, I think it can be anything , as long as I use the same for making get request.. — Vamsi Krishna B, Oct 28 '10 at 06:30
Possible duplicate of [JSON encode MySQL results](http://stackoverflow.com/questions/383631/json-encode-mysql-results) — Damjan Pavlica, May 15 '17 at 21:46

score 6 · Accepted Answer · edited Feb 06 '16 at 16:56

6

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);

edited Feb 06 '16 at 16:56

marc_s

732,580
175
1,330
1,459

answered Oct 28 '10 at 06:27

Charles Hooper

2,739
1
19
16

seconded .... call fetch_array on the result which contains the query result you want.. – Ashwini Dhekane Oct 28 '10 at 06:38
this works fine..but can I add LIMIT 0, 20 to the above query ? – Vamsi Krishna B Oct 28 '10 at 06:47

Hannes de Jager · Answer 2 · 2013-08-27T20:04:05.653

3

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.

edited Aug 27 '13 at 20:04

answered Oct 28 '10 at 07:51

Hannes de Jager

2,903
6
37
57

Probably not more scalable to do this in the database, IMHO, because web frontends are (in general) easier to scale than relational databases. – Charles Hooper Dec 24 '12 at 22:05
link is dead. do you know of a mirror or alternative? Otherwise, just delete the answer please. – Gordon Jul 30 '13 at 11:53

mysql table to json

2 Answers2

Linked