CODEWITHSUNDEEP
phpsql
Searlee
Searlee

Reputation: 61

Formatting PHP array from SQL query

I have three tables: player, score and tournament

I'm trying to format an SQL query into a PHP array in which I can parse the data as a html table, row with the headings: Position, Player Name and Player Score for each individual tournament. e.g.

Tournament 1 (ID=1):

Position | Player Name | Score
------------------------------
1        | SIMON       | 1000
2        | JAMES       | 500

Tournament 2 (ID=2):

Position | Player Name | Score
------------------------------
1        | JAMES       | 2000
2        | SIMON       | 1000

The SQL query that I'm using is currently:

FROM `player` p 
JOIN `score` s ON p.player_id = s.player_id 
JOIN `tournament` t ON s.tournament_id = t.tournament_id 
ORDER BY t.tournament_id, s.score_value DESC";

$result = mysqli_query($link, $score) or die("Error in Selecting " . mysqli_error($conn));

$arr = array();
while($row = mysqli_fetch_assoc($result))
{
   $arr[] = $row;
}

This gives me an array:

Array
(
    [0] => Array
        (
            [user_name] => SIMON
            [player_id] => 34
            [score_value] => 1000
            [tournament_id] => 1
        )

    [1] => Array
        (
            [user_name] => JAMES
            [player_id] => 35
            [score_value] => 500
            [tournament_id] => 1
        )
    [2] => Array
        (
            [user_name] => JAMES
            [player_id] => 35
            [score_value] => 2000
            [tournament_id] => 2
        )

    [3] => Array
        (
            [user_name] => SIMON
            [player_id] => 34
            [score_value] => 1000
            [tournament_id] => 2
        )
)

but I'm not sure how to format it to get the result I require. I'm currently trying to change the while loop in order to change the array.

Upvotes: 0

Views: 259

Answers (1)

user3265030
user3265030

Reputation: 204

If I understand right then:

    $arr = [];
    while($row = mysqli_fetch_assoc($result))
    {
        $tournament_id = $row['tournament_id'];
        if (!isset($arr[$tournament_id])) {
            $arr[$tournament_id] = [];
        }
        $arr[$tournament_id][] = $row;
    }

Upvotes: 1

Related Questions