dongsou0083 2016-08-01 13:42
浏览 25
已采纳

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

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..

  • 写回答

2条回答 默认 最新

  • dqdjfb2325 2016-08-01 14:33
    关注

    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;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?