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 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;
    
    点赞 评论 复制链接分享
  • donjd86266 donjd86266 2016-08-01 14:04

    the easiest way:

    $sql = "
        SELECT * FROM
        (
            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
        ) t
        ORDER BY 'date_added' desc
        LIMIT 8
    ";
    $query = $this->db->query($sql);
    return $query->result();  
    
    点赞 评论 复制链接分享

相关推荐