CODEWITHSUNDEEP
phpjson
bachtiyar93
bachtiyar93

Reputation: 13

SQL loops in PHP to JSON with 2 tables based on the id of the related table

I hope you are always healthy. I am having trouble generating an array (json) of 2 tables. the table that I have is as follows. app_produk_ready this tabel 1

app_produk_ready_img this tabel 2

php code that I made

<?php
require_once('connection.php');
$response = array();
      $query = mysqli_query($CON,"SELECT * FROM app_produk_ready");
      while($row = mysqli_fetch_array($query)){
            $id=(int)$row['id'];
            $kain=$row['kain'];
            $seri=$row['seri'];
            $harga=(int)$row['harga'];
            $stok=$row['stok'];   
            $data['id']=$id;
            $data['kain']=$kain;
            $data['seri']=$seri;
            $data['harga']=$harga;
            $data['stok']=$stok;
            $tableImage=mysqli_query($CON,"SELECT image FROM app_produk_ready_img WHERE id=$id");
            while($img = mysqli_fetch_array($tableImage)){
            $gambar=$img['image'];
            $data['images'][]=$gambar;
      }
      $response[] = $data; 
}
echo json_encode($response);
mysqli_close($CON);
?>

But the results I get on the image actually accumulate on the loop. examples of wrong results

[
{
id: 1,
kain: "Katun Jepang",
seri: "SR01",
harga: 55000,
stok: "100",
images: [
"https://sweetroommedan.com/app2/img_ready/1-1.jpg",
"https://sweetroommedan.com/app2/img_ready/1-2.jpg",
"https://sweetroommedan.com/app2/img_ready/1-3.jpg"
]
},
{
id: 2,
kain: "Katun Jepang",
seri: "SR02",
harga: 55000,
stok: "100",
images: [
"https://sweetroommedan.com/app2/img_ready/1-1.jpg",
"https://sweetroommedan.com/app2/img_ready/1-2.jpg",
"https://sweetroommedan.com/app2/img_ready/1-3.jpg",
"https://sweetroommedan.com/app2/img_ready/2-1.jpg",
"https://sweetroommedan.com/app2/img_ready/2-2.jpg"
]
},
]

What i want should be

[
{
id: 1,
kain: "Katun Jepang",
seri: "SR01",
harga: 55000,
stok: "100",
images: [
"https://sweetroommedan.com/app2/img_ready/1-1.jpg",
"https://sweetroommedan.com/app2/img_ready/1-2.jpg",
"https://sweetroommedan.com/app2/img_ready/1-3.jpg"
]
},
{
id: 2,
kain: "Katun Jepang",
seri: "SR02",
harga: 55000,
stok: "100",
images: [
"https://sweetroommedan.com/app2/img_ready/2-1.jpg",
"https://sweetroommedan.com/app2/img_ready/2-2.jpg"
]
},
{
id: 3,
kain: "Katun Jepang",
seri: "SR03",
harga: 55000,
stok: "100",
images: [
"https://sweetroommedan.com/app2/img_ready/3-1.jpg",
"https://sweetroommedan.com/app2/img_ready/3-2.jpg",
"https://sweetroommedan.com/app2/img_ready/3-3.jpg",
"https://sweetroommedan.com/app2/img_ready/3-4.jpg"
]
}
]

What do I need to do to get the results I expect? thanks for the help.

Upvotes: 1

Views: 60

Answers (2)

Alex
Alex

Reputation: 49

Why do you use two query's individualy? You can use inner join

 SELECT * 
FROM table1 
INNER JOIN table2
      ON table1.primaryKey=table2.table1Id

See: https://www.w3schools.com/sql/sql_join_inner.asp for more details

Upvotes: 0

MortenSickel
MortenSickel

Reputation: 2200

I would try to reset the data array for each new row from the outer query.

i.e.

$response = array();
  $query = mysqli_query($CON,"SELECT * FROM app_produk_ready");
  while($row = mysqli_fetch_array($query)){
     data=array();
     (...)

Your problem is that you are adding data onto the data['images'] array without ever resetting it.

Upvotes: 1

Related Questions