mirvatJ mirvatJ - 1 month ago 8
MySQL Question

how to make array of array by looping result from a query

I have a survey about games, and I have one table for the games data, and another for people's answers.

I want to output an array in Json format for answers with each of the 3 favorite games name and their year in an array of array.

expected output

[
{
"id": "1",
"username": "userX",
"g1": {"name": "game1", "year": "1991"},
"g2": {"name": "game2", "year": "1992"},
"g3": {"name": "game3", "year": "1993"},
}
]


what i've tried

$sql = "SELECT * FROM tbAnswers AS answer INNER JOIN tbgames AS game ON answer.g1 = game.id";

try {
$db = new db();
$db = $db->connect();
$stmt = $db->prepare($sql);

$stmt->execute();

$answer = $stmt->fetchAll(PDO::FETCH_OBJ);

$db = null;

if(empty($answer)) {
$response->getBody()->write
('
{
"error":
{
"status":"400",
"message":"Invalid Request"
}
}');
} else {
$response->getBody()->write(json_encode($answer));
}
} catch(PDOException $e) {
$response->getBody()->write
('
{
"error":
{
"message":'. $e->getMessage() .'
}
}');
}


the current output

[
{
"id": "1",
"username": "userX",
"name": "game1",
"year": "1991"
}
]


I think i should do a foreach somewhere in else to go through each game and echo the result of it based on the id from answers, but i am not sure how to apply it


  1. where to place to foreach

  2. how to select and get the results based on each game id

  3. how to do it in json format



i'm sure it's not how i am doing it, this is how i am trying to echo the data in else

echo"[";
echo"\n{";
echo"\n";
echo '"id:"'.' "'.$answer[0]->id.'",';
echo"\n";
echo"}\n";
echo"]";


here are my tables structure

tbGames

id , name , year
1 , 'game1' , '1991'
2 , 'game2' , '1992'
3 , 'game3' , '1993'
4 , 'game4' , '1994'


tbAnswers

id , name , g1 , g2 , g3
1 , userX , 1 , 2 , 3
2 , userY , 3 , 1 , 4
3 , userZ , 1 , 1 , 2
4 , userW , 2 , 3 , 4

Answer Source

Using this query:

$sql = "SELECT answer.id a_id, answer.name a_name, game1.id g1_id, game1.name g1_name, game1.year g1_year, game2.id g2_id, game2.name g2_name, game2.year g2_year, game3.id g3_id, game3.name g3_name, game3.year g3_year FROM tbAnswers AS answer INNER JOIN tbgames AS game1 ON answer.g1 = game1.id INNER JOIN tbgames AS game2 ON answer.g2 = game2.id INNER JOIN tbgames AS game3 ON answer.g3 = game3.id";

you should change your else statement content to:

} else {
    foreach($answer as $value) {
        $array_resp[]=[
            'id' => $value->a_id,
            'username' => $value->a_name,
            'g1' => ['name'=>$value->g1_name, 'year'=>$value->g1_year],
            'g2' => ['name'=>$value->g2_name, 'year'=>$value->g2_year],
            'g3' => ['name'=>$value->g3_name, 'year'=>$value->g3_year],
            ];
    }
    $response->getBody()->write(json_encode($array_resp));
}