php - Get Multiple value from Multiple table in mysql? -

i make music online 's website . stuck when try track , playlist ,album created user order time created . have 4 table including : user,track,album,playlist :

+---------------+------------------+ | field         | type             |  +---------------+------------------+ | album_id      | int(10) unsigned | | user_id       | int(10) unsigned | | album_title   | varchar(255)     | | album_created | datetime()       |  +---------------+------------------+ | field         | type             |  +---------------+------------------+ | playlist_id   | int(10) unsigned | | user_id       | int(10) unsigned | | playlist_title| varchar(255)     | | plist_created | datetime()       |  +---------------+------------------+ | field         | type             |  +---------------+------------------+ | track_id      | int(10) unsigned | | user_id       | int(10) unsigned | | track_title   | varchar(255)     | | track_created | datetime()       |  +---------------+------------------+ | field         | type             |  +---------------+------------------+ | user_id      | int(10) unsigned | | user_name    | varchar(255      | | password     | varchar(255)     | | email        | varchar(255      | 

here code , track ,album,playlist in 1 array

return yii::app()->db->createcommand()         ->select('u.*,t.*,p.*,a.*')         ->from('user u ')         ->join('track t', 'u.user_id = s.user_id')         ->leftjoin('playlist p', 'u.user_id = p.user_id')         ->leftjoin('album a', 'u.user_id = a.user_id')         ->andwhere('t.user_id ='.$id.' or a.user_id ='.$id.' or p.user_id ='.$id )         ->queryall();  

i want result example :

[0] => array([album_id] => 11 , [user_id] => 1 , [album_title] => aaa ,[album_created] => 2014-06-03 ) [1] => array([song_id] => 13 , [user_id] => 1 , [song_title] => aaa ,[song_created] => 2014-06-02 ) [2] => array([song_id] => 22 , [user_id] => 1 , [song_title] => zyx ,[song_created] => 2014-05-30 ) [3] => array([playlist_id] => 1 , [user_id] => 1 , [playlist_title] => xxxx ,[playlist_created] => 2014-05-25 ) 

to desired output, need union of track, playlist , album looks like:

   id   |   user_id   |   type   |   title   |   created    --------|-------------|----------|-----------|----------- 

type either album, playlist or song in case. can achieved using cdbcommand::union() , cdbcommand::gettext() follows:

return yii::app()->db->createcommand()     ->select("a.album_id, a.user_id, 'album' type, a.album_title, a.album_created created")     ->from('album a')     ->andwhere('a.user_id ='.$id)     ->union(         yii::app()->db->createcommand()             ->select("s.song_id, s.user_id, 'song' type, s.song_title, s.song_created created")             ->from('song s')             ->andwhere('s.user_id ='.$id)             ->gettext()     )->union(         yii::app()->db->createcommand()             ->select("p.playlist_id, p.user_id, 'playlist' type, p.playlist_title, p.plist_created created")             ->from('playlist p')             ->andwhere('p.user_id ='.$id)             ->gettext()     )->queryall(); 

to make life easier, should make view union. bit of tweaking may able use cactiverecord (and therefore relations() , cactivedataprovider) view.