jquery - Merge array from looped query PHP MYSQL JSON

Question

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

Answer 1

    $weeks_array=array();
    $query = "SELECT * FROM weeks"; (inside the table data is w1,w2,w3,w4)
    $sql = $db->query($query);
    while($row = $sql->fetch_assoc()){
   $array = array();
   $array["w1"] = $row['w1'];
   $array["w2"] = $row['w2'];
   $array["w3"] = $row['w3'];
   $array["w4"] = $row['w4'];
   $weeks_array[]=$array;
}
echo json_encode($weeks_array);