How to store a query result set as a 2d array?

Question

I am writing a php script to extract some data from a MYSQL db, then I want to take that data and store it into a two dimensional array. Thinking of it normally, if I use code like this

$test = array();
$test[0]=array();
$test[0]['hello']='hello';
$test[1]=array();
$test[1]['hello']='hello';
$test[2]=array();
$test[2]['hello']='hello';
print_r($test);

the output will be:

Array ( [0] => Array ( [hello] => hello ) [1] => Array ( [hello] => hello ) [2] =>
Array ( [hello] => hello ) )

which is how I want my output to be

so this is what I do in my script

So basically in my database I have table with standings for a women's league and the columns are

team_name, played, won, drawn, lost, for, against, points

All the connections have been taken care of successfully, below is my query

$get_ladies_query = "SELECT 
`team_name`, `played`, `won`, `drawn`, `lost`, `for`, `against`, `points` 
FROM `standings_ladies` order by pos";

An important point to not before I show the next code is that, there are 2 other standings tables, men_senior and men_intermediate with the same structure but obviously only the data changes, below are the two queries just incase

$get_mens_inter_query = "SELECT 
`team_name`, `played`, `won`, `drawn`, `lost`, `for`, `against`, `points` 
FROM `standings_men_inter` order by pos";

    $get_mens_senior_query = "SELECT 
`team_name`, `played`, `won`, `drawn`, `lost`, `for`, `against`, `points` 
FROM `standings_men_senior` order by pos";

Now I create 3 arrays which I want to hold standings seperately for the ladies, mens senior, mens intermediate

$standings_ladies = array();
$standings_men_inter = array();
$standings_men_senior = array();

The data I want displayed in the array is like so

array(0=>array(team_name,wins,drawn,lost,for,against,points)
1=>array(team_name,wins,drawn,lost,for,against,points)) and so on

Now since I wanted to create the multidimensional arrays of standings for all 3 categories, I could have run the queries in 3 separate while loops altough I thought, I could accomplish the same result in 1 and i felt it would help improve performance. If its better to use 3 while loops, please let me know, what I tried is below.

//I run the 3 queries and store them in the given variables
$result_mens_senior = mysqli_query($link,$get_mens_senior_query);
$result_mens_inter = mysqli_query($link,$get_mens_inter_query);
$result_ladies= mysqli_query($link, $get_ladies_query);

//I want to create 1 while loop so based of the results returned from the 3 
//queries so based on the results returned from the 3 queries, 
//I get the max number of times I want the query to run
$ladies_boundary = mysqli_num_rows($result_ladies);
$mens_senior_boundary = mysqli_num_rows($result_mens_senior);
$mens_inter_boundary = mysqli_num_rows($result_mens_inter);
$max_size = max(array($ladies_boundary,$mens_senior_boundary,$mens_inter_boundary));

//set an index to start from 0
$index = 0;

//I will only show the example for 1 of the arrays but you get an idea
that this issue will occur for all
while ($index < $max_size)
{
//first, everytime the loop is entered, we need the next row to be fetched
    
    $standings_men_inter_table = mysqli_fetch_assoc($result_mens_inter);
    $standings_ladies_table = mysqli_fetch_assoc($result_ladies);

//there is a high chance that the other two tables return a different row size
//so its best to check that we do not go beyond
if($index < $mens_senior_boundary)
    {
            //we fetch the rows every time we enter the block
            $standings_men_senior_table = mysqli_fetch_assoc($result_mens_senior);

            //then, this is how I attempt at creating the 2 dimensional array
        array_push($standings_men_senior, array(
        $standings_men_senior_table['team_name'],
        $standings_men_senior_table['played'],
        $standings_men_senior_table['won'],
        $standings_men_senior_table['drawn'],
        $standings_men_senior_table['lost'],
        $standings_men_senior_table['for'],
        $standings_men_senior_table['against'],
        $standings_men_senior_table['points']));
    }

//incrementing index each time the loop runs
$index++;

}

Then finally, I just want to print what I think is the array but get this, attached image, hope you can see it clearly

Just to investigate even further, every time, the 'if' block is entered, I just commented everything out and just put this to see what was being returned

if($index < $mens_senior_boundary)
{
    print_r(mysqli_fetch_assoc($result_mens_senior));
}

The output I got was almost 90% what I need

Array 
([team_name] => Morley Gaels [played] => 8 [won] => 6 [drawn] => 2 
[lost] => 0 [for] => 110 [against] => 83 [points] => 14 ) 
Array ( [team_name] => Southern Districts [played] => 8 [won] => 3 [drawn] => 2 
[lost] => 3 [for] => 104 [against] => 98 [points] => 8 ) 
Array ( [team_name] => St Finbarrs [played] => 8 [won] => 3 [drawn] => 2 
[lost] => 3 [for] => 107 [against] => 99 [points] => 8 ) 
Array ( [team_name] => Western Shamrocks [played] => 8 [won] => 3 [drawn] => 0 
[lost] => 5 [for] => 96 [against] => 88 [points] => 6 ) 
Array ( [team_name] => Greenwood [played] => 8 [won] => 1 [drawn] => 1 
[lost] => 9 [for] => 82 [against] => 109 [points] => 3 )

What I need is for example:

Array(0=>Array 
([team_name] => Morley Gaels [played] => 8 [won] => 6 [drawn] => 2 
[lost] => 0 [for] => 110 [against] => 83 [points] => 14 )
1=>Array
([team_name] => Southern Districts [played] => 8 [won] => 3 [drawn] => 2 
[lost] => 3 [for] => 104 [against] => 98 [points] => 8 )..... so on);

My questions are

• What is wrong with my code and what is the correct way to dynamically create multidimensional arrays in php ?
• Is there something I have not understood about how mysql_fetch_assoc works and how it returns ?
• Anything to improve, anything I am doing wrong ?

I appreciate your time, than you for reading it, I tried to be as detailed as I can about what I have tried.

Answer 1

Try this

After you do this:

$result_mens_senior = mysqli_query($link,$get_mens_senior_query);
$result_mens_inter = mysqli_query($link,$get_mens_inter_query);
$result_ladies= mysqli_query($link, $get_ladies_query);

just do this

while ($standings_men_senior[] = mysqli_fetch_assoc($result_mens_senior)){}
while ($standings_men_inter[] = mysqli_fetch_assoc($result_mens_inter)){}
while ($standings_ladies[] = mysqli_fetch_assoc($result_ladies)){}

Basically all of the posted code should be able to be replaced with:

<?php
$ladies_query = "SELECT `team_name`, `played`, `won`, `drawn`, `lost`, `for`, `against`, `points` 
FROM `standings_ladies` order by pos";

$inter_query = "SELECT `team_name`, `played`, `won`, `drawn`, `lost`, `for`, `against`, `points` 
FROM `standings_men_inter` order by pos";

$senior_query = "SELECT `team_name`, `played`, `won`, `drawn`, `lost`, `for`, `against`, `points` 
FROM `standings_men_senior` order by pos";

$ladies_stmt = mysqli_query($link, $ladies_query) || die ("Couldn't get Ladies"); // reminds me of high school
$inter_stmt  = mysqli_query($link, $inter_query)  || die ("Couldn't get Inter");
$senior_stmt = mysqli_query($link, $serior_query) || die ("Couldn't get Seniors");

$standings_men_senior = array();
$standings_men_inter = array();
$standings_ladies = array();

while ($row = mysqli_fetch_assoc($senior_stmt)){
    $standings_men_senior[] = $row;
}
while ($row = mysqli_fetch_assoc($inter_stmt)){
    $standings_men_inter[] = $row;
}
while ($row = mysqli_fetch_assoc($ladies_stmt)){
    $standings_ladies[] = $row;
}