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.

user2959229 · Accepted Answer · 2013-11-07 09:48:18Z

1

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

answered Nov 7, 2013 at 9:48

user2959229

1,3552 gold badges11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ateev Chopra Over a year ago

Thanks! Content-Type helped !

Wagner Leonardi · Accepted Answer · 2013-11-07 00:20:43Z

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 7, 2013 at 0:20

Wagner Leonardi

4,4662 gold badges39 silver badges41 bronze badges

5 Comments

Ateev Chopra Over a year ago

Can you please also tell me how to return data in json format. Not just echo, in jSON format ?

Wagner Leonardi Over a year ago

when you do json_encode() it already returns a JSON. You could do $var = json_encode($something); instead using echo. That's ok?

Ateev Chopra Over a year ago

I guess not. I am trying to get something what twitter does. api.twitter.com/1.1/search/tweets.json

Wagner Leonardi Over a year ago

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?

Ateev Chopra Over a year ago

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.

scrowler · Accepted Answer · 2013-11-07 00:17:35Z

0

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

answered Nov 7, 2013 at 0:17

scrowler

24.4k10 gold badges67 silver badges99 bronze badges

Collectives™ on Stack Overflow

Creating a JSON string from MYSQL databases

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related