How do I make my php code correctly output a JSON Array containing objects and arrays

Question

At present my JSON looks like this:

[{
 "user_review_ids": {
 "category": "cat1",
 "name": "name1",
 "phone": "phone1",
 "comment": "com1",
 "reviewid": 32
 }
}, {
 "user_review_ids": {
 "category": "cat2",
 "name": "name2",
 "phone": "phone2",
 "comment": "com2",
 "reviewid": 76
 }
}], [{
 "private_review_ids": {
 "category": "cat1",
 "name": "name1",
 "phone": "phone1",
 "comment": "com1",
 "reviewid": 240
 }
}, {
 "private_review_ids": {
 "category": "cat2",
 "name": "name2",
 "phone": "phone2",
 "comment": "com2",
 "reviewid": 241
 }
}]

Which...is not correct JSON. Jsonlint.com will tell you that.

The way it should be is:

[{
 "user_review_ids": [{
 "category": "cat1",
 "name": "name1",
 "phone": "phone1",
 "comment": "com1",
 "reviewid": 32
 }, {
 "category": "cat2",
 "name": "name2",
 "phone": "phone2",
 "comment": "com2",
 "reviewid": 76
 }],
 "private_review_ids": [{
 "category": "cat1",
 "name": "name1",
 "phone": "phone1",
 "comment": "com1",
 "reviewid": 240
 }, {
 "category": "cat2",
 "name": "name2",
 "phone": "phone2",
 "comment": "com2",
 "reviewid": 241
 }]
}]

Can you tell me what I need to do with my php code so it outputs the correct JSON?

The code right now is:

<?php
 require('file.php');
 $UserReviewID = ('32,76');
 $UserReviewID = explode(",",$UserReviewID);
 $PrivateReviewID = ('240,241');
 $PrivateReviewID = explode(",",$PrivateReviewID);
 //for user_review_ids
 $results = array();
 //for private_review_ids
 $results2 = array();
 foreach($UserReviewID as $UserReviewID) {
 $sql2 = "SELECT * FROM review WHERE review_id = ?";
 $stmt2 = $con->prepare($sql2) or die(mysqli_error($con));
 $stmt2->bind_param('i', $UserReviewID) or die ("MySQLi-stmt binding failed ".$stmt2->error);
 $stmt2->execute() or die ("MySQLi-stmt execute failed ".$stmt2->error);
 $result2 = $stmt2->get_result();
 while($row = mysqli_fetch_array($result2)) {//make an array called $results
 $results[] = array('user_review_ids' => array(
 'category' => $row['cat_name'],
 'name' => $row['name'],
 'phone' => $row['phone'],
 'comment' => $row['comment'],
 'reviewid' => $row['review_id'],
 ));
 }
 }
 foreach($PrivateReviewID as $PrivateReviewID) {
 $sql2 = "SELECT * FROM review WHERE review_id = ?";
 $stmt2 = $con->prepare($sql2) or die(mysqli_error($con));
 $stmt2->bind_param('i', $PrivateReviewID) or die ("MySQLi-stmt binding failed ".$stmt2->error);
 $stmt2->execute() or die ("MySQLi-stmt execute failed ".$stmt2->error);
 $result2 = $stmt2->get_result();
 while($row = mysqli_fetch_array($result2)) {//make an array called $results2
 $results2[] = array('private_review_ids' => array(
 'category' => $row['cat_name'],
 'name' => $row['name'],
 'phone' => $row['phone'],
 'comment' => $row['comment'],
 'reviewid' => $row['review_id'],
 ));
 }
 }
 echo json_encode($results) .",";
 echo json_encode($results2);
?>

• Lawrence Cherone: You cant json_encode twice, instead join the arrays and do it once.

Answer 1

Concatenating the outputs of multiple json_encode operations does not produce valid JSON. You need to create a data structure such than you can run json_encode once and include everything in it.

Also, to get the structure you showed, you need to change how you create the arrays resulting from the database rows - you're creating multiple items with the same index.

This should work:

<?php
 require('file.php');
 $UserReviewID = ('32,76');
 $UserReviewID = explode(",",$UserReviewID);
 $PrivateReviewID = ('240,241');
 $PrivateReviewID = explode(",",$PrivateReviewID);
 //for user_review_ids
 $results = array();
 //for private_review_ids
 $results2 = array();
 foreach($UserReviewID as $UserReviewID) {
 $sql2 = "SELECT * FROM review WHERE review_id = ?";
 $stmt2 = $con->prepare($sql2) or die(mysqli_error($con));
 $stmt2->bind_param('i', $UserReviewID) or die ("MySQLi-stmt binding failed ".$stmt2->error);
 $stmt2->execute() or die ("MySQLi-stmt execute failed ".$stmt2->error);
 $result2 = $stmt2->get_result();
 while($row = mysqli_fetch_array($result2)) {//make an array called $results
 $results[] = array(
 'category' => $row['cat_name'],
 'name' => $row['name'],
 'phone' => $row['phone'],
 'comment' => $row['comment'],
 'reviewid' => $row['review_id'],
 ));
 }
 }
 foreach($PrivateReviewID as $PrivateReviewID) {
 $sql2 = "SELECT * FROM review WHERE review_id = ?";
 $stmt2 = $con->prepare($sql2) or die(mysqli_error($con));
 $stmt2->bind_param('i', $PrivateReviewID) or die ("MySQLi-stmt binding failed ".$stmt2->error);
 $stmt2->execute() or die ("MySQLi-stmt execute failed ".$stmt2->error);
 $result2 = $stmt2->get_result();
 while($row = mysqli_fetch_array($result2)) {//make an array called $results2
 $results2[] = array(
 'category' => $row['cat_name'],
 'name' => $row['name'],
 'phone' => $row['phone'],
 'comment' => $row['comment'],
 'reviewid' => $row['review_id'],
 ));
 }
 }
 $combinedResults = array(array('user_review_ids' => $results, 'private_review_ids' => $results2));
 echo json_encode($combinedResults);
?>

Having said that, I'm not convinced you actually need the very outer array in your sample result, it doesn't seem to serve much purpose since it only has one - fixed - item in it. If you wanted to ditch that, then the last- but-one line would be

 $combinedResults = array('user_review_ids' => $results, 'private_review_ids' => $results2);

instead, which will just return a single object with those field names.

Answer 2

I think you just need to json_encode a single array. Perhaps something like:

echo json_encode( array_merge($results, $results2) );

• CHarris: Thanks, however I am getting the keys user_review_ids and private_review_ids repeated in each object whereas I just want them once, let me investigate...
• ADyson: @CHarris this is because you create those key sagain every time you output a row from the respective DB tables. See my answer for a solution which avoids that
• Andrew Chart: I was wondering whether actually you should just be pushing the array to a variable rather than an array. I.e. $results = array(...); instead of $results[] = array(...)
• ADyson: @AndrewChart since that code is in a loop, that would simply overwrite one variable lots of times, and result in only the last row of data ever being returned from each table.
• Andrew Chart: Ah yes I see. Looking too quickly by eye, sorry.

Answer 3

instead of

 echo json_encode($results) .",";
 echo json_encode($results2);

use

echo json_encode(array_merge($results,$results2));

Answer 4

You can use the inbuilt php function json_encode() example:

<?php
$pdo = new PDO("mysql:dbname=database;host=127.0.0.1", "user", "password");
$statement = $pdo->prepare("SELECT * FROM table");
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);

good luck !

• Lawrence Cherone: OP is using mysqli
• ADyson: this is a generic example of no relevance or direct benefit to the question. It uses the wrong DB library and entirely misses the point that OP already knows about json_encode, but is having an issue with the specific format of the data being output