I'm probably overlooking a fairly simple way of doing this; perhaps someone has an idea of how to make this easy with limited looping and without an excessively long query. Let's say I have a MySQL table with data like this: (There's 12 months, and could be maybe 10 different possible grades). I'll query out just the results for a given user_id and year.
+----+---------+------+-------+-------+-------+
| id | user_id | year | month | grade | value |
+----+---------+------+-------+-------+-------+
| 1 | 1 | 2021 | Jan | A | 95 |
+----+---------+------+-------+-------+-------+
| 2 | 2 | 2021 | Jan | D | 75 |
+----+---------+------+-------+-------+-------+
| 3 | 2 | 2021 | Feb | F | 45 |
+----+---------+------+-------+-------+-------+
I want to be able to query the data and put it into a multi-dimensional associative PHP array. Essentially, so I can access the data like this:
echo $month_value['Jan']['D']; // Should give me 75
echo $month_value['Feb']['F']; // Should give me 45
Figured out a simple method that works for me:
$sql_retrieve = $con->prepare("SELECT month, grade, value
FROM table
WHERE user_id = ? AND year = ?;");
$bind_process = $sql_retrieve->bind_param('ii',$user_id,$year);
$sql_retrieve->execute();
$result = $sql_retrieve->get_result();
$month_values = []; // initialize array
if($result->num_rows > 0 ){ // If there are results
while($row=$result->fetch_assoc()){
$month_values[$row["month"]][$row["grade"]] = $row["value"]; // add to array
} // end while
} // end of if num_rows > 0
print_r($month_values); // Example
echo 'Value: '.$month_values['Jan']['D'];
This then provides the MySQL results into a multi-dimensional associative PHP array, so they can be referenced as such.