如何在codeigniter中显示4个表中的最新项目

Question

i created a div on my homepage to display the most recent items from four tables on my database, but i'm having problems with the structure of the query, i tried this for the model:

   $sql = "(SELECT name,type, screenshot,url from music WHERE status = 1   ) 
    UNION ALL (SELECT name,type, screenshot,url from lyrics WHERE status = 1   )
    UNION ALL(SELECT name,type, screenshot,url from mixtapes WHERE status = 1  ) 
    UNION ALL (SELECT name,type, screenshot, vurl from videos WHERE  status = 1  ) ORDER BY 'date_added' desc LIMIT 8";
     $query = $this->db->query($sql);
     return $query->result();  

this query retrieves only from the music table, and the limit keyword applies only to the items retrieved from the music table, but i want the query run as one query, so that the items are retrieved based on the latest items from the four tables and the limit keyword limits the results to eight items, please how do i construct the query, or is there any other way to do it? please help..

Answer 1

If you want two recent items of each list; you can use something like this:

(SELECT name,type, screenshot,url from music 
    WHERE status = 1 order by date_added desc limit 0,2) 
UNION
(SELECT name,type, screenshot,url from lyrics  
    WHERE status = 1 order by date_added desc limit 0,2)
UNION
(SELECT name,type, screenshot,url from mixtapes 
    WHERE status = 1 order by date_added desc limit 0,2) 
UNION
(SELECT name,type, screenshot,url from videos 
    WHERE status = 1 order by date_added desc limit 0,2) 

Or, if you want the recent 8 items from all 4 tables:

SELECT * FROM (
    (SELECT name,type, screenshot,url from music WHERE status = 1) 
    UNION
    (SELECT name,type, screenshot,url from lyrics WHERE status = 1) 
    UNION
    (SELECT name,type, screenshot,url from mixtapes WHERE status = 1) 
    UNION
    (SELECT name,type, screenshot,url from videos WHERE status = 1) 
) ORDER BY date_added DESC LIMIT 0,8;