PuzzledInVA PuzzledInVA - 4 months ago 17
SQL Question

PHP: Have an existing 2D array and want to add another column from a mySQL query

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...

Answer

Try the following in the first approach:

$arr_result[$row["Year"]]["TotApts"] = $row["TotApts"];

In the second approach you can do this:

$arr_result[] = array( Year" => $row["Year"], "Total" => $row["TotApts"], "Gender" => $row["Gender"] )

Comments