如何将此数据分组到数组中？

My table looks like this:

artist_id | song_id | user_id
a1          s1        u1
a1          s2        u1
a2          s9        u1
a3          s15       u2

I'd like my output to look something like:

Array
    (
    [u1] => Array
        (
         [a1] => Array
           (
               [0] => s1
               [1] => s2
           )
         [a2] => Array
           (
               [0] => s9
           )
   )
   [u2] => Array
        (
         [a3] => Array
           (
               [0] => s15
           )

   )

)

Where should I start?

I'm not sure my approach is most efficient:

• Grab and group user_id, loop thru them.

• For each user_id, grab the artist_id.

• For each artist_id grab the song_ids underneath.

Is there a way to make this one query?

You can get the following results set from MySQL query. Use any one of the library PDO or MySQLi.

<?php
$results = array(
                0 => array('artist_id' => 'a1', 'song_id' => 's1', 'user_id' => 'u1'),
                1 => array('artist_id' => 'a1', 'song_id' => 's2', 'user_id' => 'u1'),
                2 => array('artist_id' => 'a2', 'song_id' => 's9', 'user_id' => 'u1'),
                3 => array('artist_id' => 'a3', 'song_id' => 's15', 'user_id' => 'u2')
            );
echo '<pre>';print_r($results);echo '</pre>';
?>

Try with this,

<?php
$new_array = array();

foreach($results as $keys=>$values) {
    $new_array[$values['user_id']][$values['artist_id']][] = $values['song_id'];
}
echo '<pre>';print_r($new_array);echo '</pre>';
?>

Note: You can't get it from single query, it is depends on the results set. i explained with your result set,

As long as I see, you need ALL results in your table, no count, no sum, etc.

So there's nothing you can group by in SQL query, just select them all out and group them in PHP:

$result=array();
$mysqli_result=$mysqli->query("SELECT `artist_id`,`song_id`,`user_id` FROM `songs`");
while($row=$mysqli_result->fetch_assoc())
{
    if(empty($result[$row["user_id"]]))
        $result[$row["user_id"]]=array();
    if(empty($result[$row["user_id"]][$row["artist_id"]]))
        $result[$row["user_id"]][$row["artist_id"]]=array();
    $result[$row["user_id"]][$row["artist_id"]][]=$row["song_id"];
}
print_r($result);