I have 4 table results generated from PHP Looped Query
The result i was expecting is like this
|_Number_|_w1_|_w2_|_w3_|_w4_|
| 1 | 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 1 | 1 |
| 3 | 1 | 1 | 1 | 1 |
| 4 | 1 | 1 | 1 | 1 |
But the query is generating results is like this
|_Number_|_w1_| |_Number_|_w2_| |_Number_|_w2_| |_Number_|_w2_|
| 1 | 1 | | 1 | 1 | | 1 | 1 | | 1 | 1 |
| 2 | 1 | | 2 | 1 | | 2 | 1 | | 2 | 1 |
| 3 | 1 | | 3 | 1 | | 3 | 1 | | 3 | 1 |
| 4 | 1 | | 4 | 1 | | 4 | 1 | | 4 | 1 |
and the query i'm using is
$array = array();
$query = "SELECT * FROM weeks"; (inside the table data is w1,w2,w3,w4)
$sql = $db->query($query);
while($row = $sql->fetch_assoc()){
$weeks_array[] = $row;
}
foreach($weeks_array as $key => $value) :
$no = 1;
$weeks = $value['weeks'];
$query2 = "SELECT * FROM weeks_data WHERE weeks = $weeks;
$sql2 = $db->query($query2);
while($row2 = $sql2->fetch_assoc()){
$weeks_data['number'] = $no++;
$weeks_data['weeks'] = $row2['weeks'];
$weeks_data['data'] = $row2['data'];
array_push($array,$weeks_data)
}
endforeach;
$data['rows'] = $weeks_data;
echo json_encode($data);
i've been trying to use normal looped table, and it seems that i found the easier way is to use JSON.
But i'm hitting the dead end when generated JSON is like this
{
number : 1,
weeks : "w1",
data : 1
},
{
number : 1,
weeks : "w1",
data : 1
},
{
number : 1,
weeks : "w2",
data : 1
},
{
number : 1,
weeks : "w2",
data : 1
}
and i need it like this
{
number : 1,
w1: "1",
w2 : "1",
w3 : "1",
w4 : "1",
},
{
number : 2,
w1: "1",
w2 : "1",
w3 : "1",
w4 : "1",
}
i really appriciate all the helps and suggestion provided to me. thanks
question from:
https://stackoverflow.com/questions/65881977/merge-array-from-looped-query-php-mysql-json