How to form a request correctly

[Anonymous]

How to correctly form a query to the database in order to get the result at the output:

Game: 1 (game_id)
Партия: 1 (party_id)
User 1: 43
User 2: 11
User 3: 87

Party 2 (party_id)
User 1: 34
User 2: 43
User 3: 65

Game 2 (game_id)
Party 1 (party_id)
User 1: 90
User 2: 21
User 3: 66

 

[Anonymous]

SQL works in two dimensional data sets; you can't get the "game > party > user list" tree you want directly. You can do this by getting all the results from your table, then building the tree in PHP:

$db = new PDO(
    'mysql:dbname=test',
    'root',
    'root',
    [
        PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
    ]
);

$res = $db->query('SELECT game_id, party_id, user_id, point FROM results ORDER BY game_id, party_id');

$results = [];

foreach ($res as $row) {
    if (!isset($results[$row->game_id])) {
        $results[$row->game_id] = [
            'game' => $row->game_id,
            'parties' => []
        ];
    }

    if (!isset($results[$row->game_id]['parties'][$row->party_id])) {
        $results[$row->game_id]['parties'][$row->party_id] = [
            'party' => $row->party_id,
            'users' => []
        ];
    }

    $results[$row->game_id]['parties'][$row->party_id]['users'][$row->user_id] = [
        'user' => $row->user_id,
        'point' => $row->point
    ];
}

Once you have your data structure, you can loop through it to print out your results:

foreach ($results as $game) {
    echo 'Game: ' . $game['game'], PHP_EOL;

    foreach ($game['parties'] as $party) {
        echo 'Party: ' . $party['party'], PHP_EOL;

        foreach ($party['users'] as $user) {
            echo 'User ' . $user['user'] . ': ' . $user['point'], PHP_EOL;
        }

        echo PHP_EOL;
    }
}

This looks complicated, but most of the gnarliness is in the setting up of the tree structure.

 

[Anonymous]

Everyone is working. Thank you very much.