PHP data from multiple MySQL queries JSON output wrap in array

Question

I am doing 2 queries on the same MySQL table.

Full code here

$json = array();
$json['soldArticles'] = array();


for ($brojac = 0; $brojac < count($storeLocationID); $brojac++) {
$storeName = storeFullName($storeLocationID[$brojac]);

$sqlTOTAL = "SELECT SUM(art_ukupno) AS ukupno, art_nazivProdavnice FROM tbl_ProdatiArtikli WHERE art_datum BETWEEN '2022-08-10' AND '2022-08-12' 
             AND art_nazivProdavnice = '$storeName'";
  
$result = $conn->query($sqlTOTAL);

$sql = "SELECT art_kategorija, SUM(art_kolicina) AS Qty, SUM(art_ukupno) AS Total
        FROM tbl_ProdatiArtikli 
        WHERE art_datum BETWEEN '2022-08-10' AND '2022-08-12' AND art_nazivProdavnice = '$storeName'
        GROUP BY art_kategorija, art_nazivProdavnice 
        ORDER BY art_nazivProdavnice ASC, art_kategorija ASC";

$result1 = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    $json["Prodavnica"] = $row["art_nazivProdavnice"];
    $json["Total"] = $row["ukupno"];

    while ($row1 = mysqli_fetch_array($result1)) {
        $index['Category'] = $row1['art_kategorija'];
        $index['Qty'] = $row1['Qty'];
        $index['Total'] = $row1['Total'];
        
        array_push($json['soldArticles'], $index);
    }
}
    
echo json_encode($json, JSON_UNESCAPED_UNICODE);

}

This is the output that I get

{
"soldArticles":[
  {
     "Category":"16",
     "Qty":"10.90",
     "Total":"4895.00"
  },
  {
     "Category":"19",
     "Qty":"27.00",
     "Total":"275.00"
  },
 ...
],
"Prodavnica":"Store 01",
"Total":"162640.00"
}

Now I cannot seem to add another (PARENT) array that will have the name ex. DATA, and within that array will be the output of all store objects like below.

Does anyone have any ideas on how to fix this and add the main array to wrap the existing objects?

Example of what I want to achieve

{
"DATA":[
{
"soldArticles":[
  {
     "Category":"16",
     "Qty":"10.90",
     "Total":"4895.00"
  },
  {
     "Category":"19",
     "Qty":"27.00",
     "Total":"275.00"
  },
 ...
],
"Prodavnica":"Store 01",
"Total":"162640.00"
},

//next store object,
//next store object

]

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) 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/32391315) — Dharman, Aug 16 '22 at 11:32
@Dharman thanks for the info, I'll definitely change the code structure. — BigBoiVladica, Aug 16 '22 at 11:39

score 0 · Accepted Answer · answered Aug 16 '22 at 11:21

0

Whenever you are trying to push an array into another array you can use following methods

$data = array(); #this will initialize an empty array

// method 1
$data[] = $json;

// method 2
array_push($data, $json);

answered Aug 16 '22 at 11:21

Arslaan Shaikh

86
5

PHP data from multiple MySQL queries JSON output wrap in array

1 Answers1