Create dynamic JSON array in php

Question

I'd like to create a JSON dynamically like that:

{
"storie":[
{
"story_id":"111",
"username":"Username1",
"profile_photo":"boh.png",
"copertina":"ok.png",
"num_elements":"1",
"rating":"4.5"
},
{
"story_id":"222",
"username":"Username2",
"profile_photo":"hello.png",
"copertina":"hi.png",
"num_elements":"2",
"rating":"3.5"
}
]
}

I'm trying to do that getting values from MySQL database and I'm able to do that:

$response = array();
$sql = mysqli_query($conn, "SELECT * FROM storie WHERE userid IN (SELECT following FROM follow WHERE follower='$userid')");
while($row = mysqli_fetch_assoc($sql)){
  $usern = getuserinfo($row['userid'], "username", $conn);
  $prof_photo = getuserinfo($row['userid'], "profile_photo", $conn);
  $idsto=$row['storia_id'];
  $elem = mysqli_query($conn, "SELECT COUNT(*) AS da_vedere FROM `storie_images` WHERE storia_id='$idsto' AND imm_id NOT IN (SELECT imm_id FROM image_views WHERE viewer='$userid')");
  while($ok = mysqli_fetch_assoc($elem)){
    $num_elem = $ok['da_vedere'];
  }
  //here I put the line that add the array to the json array:
}

But the problem is this row, which should create a new array and put it into the json:

$response['storie'] = [array("story_id" => $idsto, "username" => $usern, "profile_photo" => $prof_photo, "copertina" => $row['copertina'], "num_elements" => $num_elem, "rating" => "4.5")];

It works when there's only a record, but it doesn't work if there are more records. Can someone help me?

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 16 '20 at 13:45

score 2 · Accepted Answer · answered Aug 16 '20 at 13:28

simply replace:

$response['storie'] = [array("story_id" => $idsto, "username" => $usern, "profile_photo" => $prof_photo, "copertina" => $row['copertina'], "num_elements" => $num_elem, "rating" => "4.5")];

with

$response['storie'][] = array("story_id" => $idsto, "username" => $usern, "profile_photo" => $prof_photo, "copertina" => $row['copertina'], "num_elements" => $num_elem, "rating" => "4.5");

score 1 · Answer 2 · answered Aug 16 '20 at 13:27

That's because you are using $response['storie'] inside while , to fix this problem :

1- create another array called result for example , then use it in while

$response = array();
$result = array ()
while (....)
{
//here I put the line that add the array to the json array:
$result[] = array("story_id" => $idsto, "username" => $usern, "profile_photo" => $prof_photo, "copertina" => $row['copertina'], "num_elements" => $num_elem, "rating" => "4.5");
}

2- then use the response array outside the loop:

$response['storie'] = $result;

And your code will be like this :

   $response = array();
   $result = array ()

    $sql = mysqli_query($conn, "SELECT * FROM storie WHERE userid IN (SELECT following FROM follow WHERE follower='$userid')");
    while($row = mysqli_fetch_assoc($sql)){
    $usern = getuserinfo($row['userid'], "username", $conn);
    $prof_photo = getuserinfo($row['userid'], "profile_photo", $conn);
    $idsto=$row['storia_id'];
    $elem = mysqli_query($conn, "SELECT COUNT(*) AS da_vedere FROM `storie_images` WHERE storia_id='$idsto' AND imm_id NOT IN (SELECT imm_id FROM image_views WHERE viewer='$userid')");
    while($ok = mysqli_fetch_assoc($elem)){
    $num_elem = $ok['da_vedere'];
    }
    //here I put the line that add the array to the json array:
    $result[] = array("story_id" => $idsto, "username" => $usern, "profile_photo" => $prof_photo, "copertina" => $row['copertina'], "num_elements" => $num_elem, "rating" => "4.5");
    }

   $response['storie'] = $result;

Create dynamic JSON array in php

2 Answers2