fetch multidimensional array from database

Question

Is there a way to run one mysql query and fetch a multidimensional array?

For example, assume you have a specific user and you want to fetch data on that user as well as the most recent events/activity of that user.

The table structure would be something like:

users Table:

ID | username | emailaddress | firstname | lastname | dateofbirth

events Table:

ID | user | event_label | timestamp

There can of course be only one unique user but there can be multiple events for that user. So if I do a LEFT JOIN on these two tables, I would have to use a while loop to fetch all the event rows. But then on each iteration I would again have the same data from the users table.

For example, the code for the query could be something like this:

$mysqli = new mysqli(HOST, USER, PASSWORD, DATABASE);
$id = 123456;
$select = $mysqli->query("
  SELECT users.*, events.event_label, events.timestamp
  FROM users
    LEFT JOIN events ON users.id=events.user    
  WHERE users.id=$id
");
while ($row = $select->fetch_assoc()) {
    $result[] = $row;
}
echo "<pre>";
print_r($result);
exit("</pre>");

/*
Sample output:
    Array (
    [0] => Array
        (
            [id] => 1
            [username] => name
            [emailaddress] => joe@example.com
            [firstname] => Joe
            [lastname] => Test
            [dateofbirth] => 2015-07-10
            [event_label] => Subscribe
            [timestamp] => 2015-07-10 00:00:00
        )
    [1] => Array
        (
            [id] => 1
            [username] => name
            [emailaddress] => joe@example.com
            [firstname] => Joe
            [lastname] => Test
            [dateofbirth] => 2015-07-10
            [event_label] => Visit
            [timestamp] => 2015-07-10 01:00:00
        )
    [2] => Array
        (
            [id] => 1
            [username] => name
            [emailaddress] => joe@example.com
            [firstname] => Joe
            [lastname] => Test
            [dateofbirth] => 2015-07-10
            [event_label] => phonecall
            [timestamp] => 2015-07-10 03:00:00
        )
)
*/

The desired result would be something like the below. I know that I can just rearrange the data in PHP but I was wondering if it is possible to achieve the same with MySQL.

/*
Desired output:
    Array (
    [0] => Array (
        [id] => 1
        [username] => name
        [emailaddress] => joe@example.com
        [firstname] => Joe
        [lastname] => Test
        [dateofbirth] => 2015-07-10
        [events] => Array (
                [0] => Array (
                     [event_label] => Subscribe
                     [timestamp] => 2015-07-10 00:00:00
                )
                [1] => Array (
                     [event_label] => Visit
                     [timestamp] => 2015-07-10 01:00:00
                )
                [2] => Array (
                     [event_label] => phonecall
                     [timestamp] => 2015-07-10 03:00:00
                )
        )
)
*/

Answer 1

You should be able to change your Select statement slightly to make processing easier:

$select = $mysqli->query("
  SELECT users.username,
  users.emailaddress,
  users.firstname,
  users.lastname,
  users.dateofbirth,
  events.event_label,
  events.timestamp
  FROM users
    LEFT JOIN events ON users.id=events.user    
  WHERE users.id=$id
");

and then modify your while loop so you are fetching a multidimensional array in php

while ($row = $select->fetch_assoc()) {
    $result[[$row['id']] = $row;
}