时间戳

首页 > 解决方案 > 在分页限制中使用联合

php - 在分页限制中使用联合

问题描述

请有人可以通过“联合”选择语句的分页来帮助mysql查询。使用页面限制和偏移限制

如果我弄错了，下面是我的 sql。

 $sqlQuery = "SELECT title, content, img, postlink, created_at, 'forum' as type FROM forum_post WHERE title LIKE CONCAT('%', :keyword, '%')
    UNION  SELECT title, content, img, postlink, created_at, 'music' as type FROM music_post WHERE title LIKE CONCAT('%', :keyword, '%') UNION 
    SELECT title, content, img, postlink,created_at, 'video' as type FROM video_post WHERE title LIKE CONCAT('%', :keyword, '%') ORDER BY created_at DESC LIMIT   ".($lower_limit)." ,  ". ($page_limit). "";

这是 pdo 中的完整 sql 代码

here is the full sql query `public function all_search($keyword, $lower_limit, $page_limit) {
    $sqlQuery = "SELECT * FROM(SELECT title, content, img, postlink, created_at, 'forum' as type FROM forum_post WHERE title LIKE CONCAT('%', :keyword, '%')
    UNION  SELECT title, content, img, postlink, created_at, 'music' as type FROM music_post WHERE title LIKE CONCAT('%', :keyword, '%') UNION 
    SELECT title, content, img, postlink,created_at, 'video' as type FROM video_post WHERE title LIKE CONCAT('%', :keyword, '%')) tab LIMIT   ".($lower_limit)." ,  ". ($page_limit). "";
    $stmt = $this->conn->prepare($sqlQuery);
    $stmt->execute(['keyword'=>$keyword]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return $result;
}`

标签： phpmysql

解决方案

尝试这个

$sqlQuery = "SELECT * FROM(SELECT title, content, img, postlink, created_at, 'forum' as type FROM forum_post WHERE title LIKE CONCAT('%', :keyword, '%')
        UNION  SELECT title, content, img, postlink, created_at, 'music' as type FROM music_post WHERE title LIKE CONCAT('%', :keyword, '%') UNION 
        SELECT title, content, img, postlink,created_at, 'video' as type FROM video_post WHERE title LIKE CONCAT('%', :keyword, '%'))tab LIMIT   ".($lower_limit)." ,  ". ($page_limit). "";

推荐阅读