从MYSQL数据库创建JSON字符串

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.

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