Creating a JSON string from MYSQL databases

Question

I am trying to get data from MySQL database in form of a json string.

I read this answer: JSON encode MySQL results

But this is limited to a single table. What if I want to get data from multiple tables (name from userDetails, purchase data from UserPurchases etc)? How can I create a custom string, getting data from multiple tables and creating a json string like it’s from a single table only?

 $query = "SELECT * FROM `thing` WHERE `id` = :thingId";
    $stmt = $dbh->prepare ( $query );
    $stmt->bindParam ( ":thingId" , $_GET['thingId']  );
    $stmt->execute ( );
    $rslt  =  $stmt->fetch ( );
    $thingName  = $rslt['name'];
    $thingOwnerId = $rslt['userId'];
    $thingDescription = $rslt['thingDescription'];

// Getting the thing owner details
    $query = "SELECT * from `user` WHERE ( `id` = :id ) ";    
    $stmt = $dbh->prepare( $query );
    $stmt->bindParam ( ":id" , $thingOwnerId );
    $stmt->execute(  );
    $rslt = $stmt->fetch ( );
    $thingOwnerName = $rslt['firstName']." ".$rslt['lastName'];

Now, how to make a single json strong out of this data from separate tables. The string should have the thingName,thingOwnerId, thingDescription, thingOwnerName.

score 1 · Answer 1 · answered Nov 07 '13 at 00:20

1

you can also create a class in PHP, set database values to this class and encode to JSON, eg.:

<?php

class MyCustomJson
{
    public $userId;
    public $thingId;
    //and go on...
}

//set db values to this class
$myCustomJson = new MyCustomJson();
//..read data from User table and set table values to your custom object
$myCustomJson->userId = $row["id"];
//..read data from Thing table and set table values to your custom object
$myCustomJson->thingId = $row["id"];

//and encode your custom object to json
echo json_encode($myCustomJson);
?>

answered Nov 07 '13 at 00:20

Wagner Leonardi

4,226
2
35
41

Can you please also tell me how to return data in json format. Not just echo, in jSON format ? – Ateev Chopra Nov 07 '13 at 00:31
when you do json_encode() it already returns a JSON. You could do $var = json_encode($something); instead using echo. That's ok? – Wagner Leonardi Nov 07 '13 at 00:37
I guess not. I am trying to get something what twitter does. https://api.twitter.com/1.1/search/tweets.json – Ateev Chopra Nov 07 '13 at 00:46
echo json_encode($something); should print a json like this twitter example. If isn't printing a json, you got something wrong BEFORE this. Could you update your question with actual code to solve that? – Wagner Leonardi Nov 07 '13 at 00:48
I am getting the output. It just that my add-on to view json data in firefox isn't recognising it as json. maybe there is some problem with the add-on. – Ateev Chopra Nov 07 '13 at 00:52

score 1 · Accepted Answer · answered Nov 07 '13 at 09:48

Collect the required data from your queries in an array, then output that array in a JSON encoded format to the browser. Remember to set the Content-Type: application/json header before any output.

PHP

//collect your data in an array
$data=array(
    'name'=>$thingOwnerName,
    'description'=>$thingDescription,
    'otherField'=>$someValue
    ...
);

//send JSON header to browser
header('Content-Type: application/json');

//echo JSON encoded data
echo json_encode($data);

Thanks! `Content-Type` helped ! – Ateev Chopra Nov 08 '13 at 10:31 — Ateev Chopra, Nov 08 '13 at 10:31

score 0 · Answer 3 · answered Nov 07 '13 at 00:17

If they are different queries, you can merge the results and encode that array like this:

$merged = array();

 $query = "SELECT * FROM `thing` WHERE `id` = :thingId";
    $stmt = $dbh->prepare ( $query );
    $stmt->bindParam ( ":thingId" , $_GET['thingId']  );
    $stmt->execute ( );
    $rslt  =  $stmt->fetch ( );
$merged['thing'] = $rslt;    

// Getting the thing owner details
    $query = "SELECT * from `user` WHERE ( `id` = :id ) ";    
    $stmt = $dbh->prepare( $query );
    $stmt->bindParam ( ":id" , $thingOwnerId );
    $stmt->execute(  );
    $rslt = $stmt->fetch ( );
$merged['user'] = $rslt;

echo json_encode($merged);

Creating a JSON string from MYSQL databases

3 Answers3