0

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.

Searlee
  • 61
  • 2
  • 14
  • Can you clarify what the difficulty is? It seems to me that you have all the information you need to create your HTML table in the PHP array that is returned. – Graham Asher Oct 03 '19 at 15:48
  • Then you need to provide us your while-loop and say where the problem is. – freeek Oct 03 '19 at 16:04
  • I suggest to split the array into 2 arrays based on `tournament_id` and loop over them to [create a dynamic table](https://stackoverflow.com/q/7885871/9128538). – YTZ Oct 03 '19 at 16:32

1 Answers1

1

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;
    }
user3265030
  • 194
  • 1
  • 3