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

Question

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

where to place to foreach
how to select and get the results based on each game id
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

first, your sql query should be something like this: SELECT * 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 — Pedro Rodriguez Larez
– Pedro Rodriguez Larez, Commented Aug 18, 2017 at 20:13
You need to change your query instead of answer.g1 use answer.id this must be get the data you need — Hail Hydra
– Hail Hydra, Commented Aug 18, 2017 at 20:13
@perodriguezl can you tell me where to place to foreach how to select and get the results based on each game id how to do it in json format — mirvatJ
– mirvatJ, Commented Aug 18, 2017 at 20:32
replace your SQL query with this $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"; and show the new (FULL) output — Pedro Rodriguez Larez
– Pedro Rodriguez Larez, Commented Aug 18, 2017 at 20:39
@perodriguezl i did try that before, it shows the same output as the current output that i posted in my question, even though i added all the inner joins — mirvatJ
– mirvatJ, Commented Aug 18, 2017 at 20:43

Pedro Rodriguez Larez · Accepted Answer · 2017-08-18 22:04:16Z

0

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));
}

edited Aug 18, 2017 at 22:04

answered Aug 18, 2017 at 21:29

Pedro Rodriguez Larez

5453 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mirvatJ Over a year ago

your answer makes a lot of sense, but i am getting this error Cannot use object of type stdClass as array

Pedro Rodriguez Larez Over a year ago

yes, you are right, you should try with the modification I Aplied, @mirvatJ

mirvatJ Over a year ago

now i get Cannot use object of type Slim\Http\Response as array

Pedro Rodriguez Larez Over a year ago

@mirvatJ change the $response var name to $array_resp

mirvatJ Over a year ago

i figured that one out by now :p your amazing @perodriguezl thank you so much for your help!!

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related