Php query into json

Question

I am querying my database Select * from Customer the customer tables holds Name,Surname Address,age.

I want to be able to transform the query into a json object in the following object:

Customer:

[
    {Name:"john", Surname:"Beta" ,Age:"23"},
    {Name:"Fred", Surname:"alpha" ,Age:"31"}
];

Do you have any ideas?I tried to loop through the query and use merge_array.. but it MERGED the array as expected... Thank you for your time.

Create your array, then use $returnData = json_encode($array); to format the array as JSON for returning to the browser/Ajax call — Mark Baker, Mar 04 '11 at 15:27

score 6 · Accepted Answer · answered Mar 04 '11 at 15:31

6

You just need to group into the expected nested structure:

while ($row = mysql_fetch_assoc($r)) {
    $customer[] = $row;
}

$struct = array("Customer" => $customer);
print json_encode($struct);

answered Mar 04 '11 at 15:31

mario

THANKS it worked.. :( I am such an ignorant for now trying it earlier... :(( I guess you never finish to learn – 7dr3am7 Mar 04 '11 at 15:50

score 2 · Answer 2 · answered Mar 04 '11 at 15:26

2

If you have code like this:

$sql = "SELECT id as userid, fullname, userstatus 
        FROM   sometable
        WHERE  userstatus = 1";
$result = mysql_query($sql);

seems like json_encode(mysql_fetch_assoc($result)); would do the job? Put in a foreach/while for all results...

answered Mar 04 '11 at 15:26

NDM

if I do as you say it will only fetch the first row- if I put it into a foreach loop I suppose it will always create a new json object, wouldn't?, I am trying to put that message into one json object :(( – 7dr3am7 Mar 04 '11 at 15:41
then you could loop over it and do `$arr[] = mysql_fetch_assoc($result)` and after the loop encode the `$arr` – NDM Jul 20 '11 at 10:20

score 1 · Answer 3 · answered Mar 04 '11 at 15:27

1

Either use it yourself, or have a look at what MySQL to JSON is doing and implement something like it :)

answered Mar 04 '11 at 15:27

Konerak

score 0 · Answer 4 · answered Jun 18 '13 at 16:32

Because I am using PDO to handle database calls I used a foreach statement.

$grids = $db->run("DATABASE QUERY");

foreach ($grids as $row) {
    $grid[] = $row;
}

$struct = array("Grid" => $grid);
print json_encode($struct);

Otherwise same code as above. Thanks Mario.

4 Answers4