Formatting PHP array from SQL query

Question

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.

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
– Graham Asher, Commented Oct 3, 2019 at 15:48
Then you need to provide us your while-loop and say where the problem is. — freeek
– freeek, Commented Oct 3, 2019 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. — YTZ
– YTZ, Commented Oct 3, 2019 at 16:32

user3265030 · Accepted Answer · 2019-10-03 16:07:23Z

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

answered Oct 3, 2019 at 16:07

user3265030

2042 silver badges3 bronze badges

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

1 Comment

Searlee Over a year ago

This was perfect. Thank you for your help.

Collectives™ on Stack Overflow

Formatting PHP array from SQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related