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

Question

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?

Answer 1

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,