I would like to generate a 2-dimensional array that expands as I add the results from multiple SQL queries. I'm struggling with 2 different problems depending on how I approach the issue.
The initial query
$query = "SELECT YEAR(`Date`) as Year, count(*) as Total FROM `t_Table` GROUP BY YEAR(`Date`)";
if ($result = $mysqli -> query($query)) {
while($row = $result -> fetch_array()) {
<some action to get these results into a 2-D array>
}
$result->close();
}
Approach 1: use Year as the index
The following works to create the array but uses a numeric index:
$arr_result[] = array("Year" => $row["Year"], "Total" => $row["Total"]);
This produces:
Array (
[0] => Array ( [Year] => 2015 [Total] => 158322 )
[1] => Array ( [Year] => 2016 [Total] => 47908 )
)
What do I need to do to make Year the index? I've tried this but it doesn't work:
$arr_result[$row["Year"]] = "TotApts" => $row["TotApts"]);
and this:
$arr_result[$row[0]]["TotApts"] => $row["TotApts"];
What am I missing?
Approach 2: keep the index numeric and add additional columns to each row
So bring in query number 2 which extracts Year and count of Gender from the DB and I've verified that all is good as follows:
while($row = $result -> fetch_array()) {
printf("Year %s - Gender %s<br>", $row["Year"], $row["Gender"]);
}
This produces
Year 2015 - Gender 91819
Year 2016 - Gender 27930
But I'm banging my head as to how to insert these into $arr_result so that I produce the following:
Array (
[0] => Array ( [Year] => 2015 [Total] => 158322 [Gender] => 91819 )
[1] => Array ( [Year] => 2016 [Total] => 47908 [Gender] => 27930 )
)
For example, I've tried among many many other (equally wrong) permutations:
$arr_result[][$row["Year"] = array("Gender" => $row["Gender"]);
Given use of the numeric index, do I need to loop through them (from i = 0 to x) from within the fetch_array() while loop and check that Year = the query year and only then update the value? Seems like that's a bit of unnecessary complexity. Am sure there is simpler approach...
