PHP分页查询失败

Question

我在我的php脚本中收到一个分页错误。当直接在mysql工作台中运行时，查询运行得很好，并返回正确的结果。

返回错误:您的SQL语法中有一个错误；请查看与您的MySQL服务器版本对应的手册，以了解在第2行中使用“LIMIT 0，20”附近的正确语法。

    $getpositive = "select case_number, c.name as subject, a.name, u.first_name, u.last_name from cases as c join cases_cstm as cc on c.id = cc.id_c
                left join accounts as a on a.id = c.account_id left join users as u on u.id = c.assigned_user_id where rating_c ='1';";
$db -> PS_Pagination($getpositive, 20, 5, "");
$db -> setDebug(true);
$rs = $db->paginate();
$positive_rating_rows = mysql_num_rows($rs);

然后，我在一个表中显示这些结果：

while($val = mysql_fetch_assoc($rs)) 
        {          

        ?>
                </tr>
                <tr>
                    <td width="7%"><?=$val['case_number']?></td>
                    <td width="40%"><?=$val['subject']?></td>
                    <td width="40%"><?=$val['name']?></td>
                </tr>

下面是我的分页函数：

public function PS_Pagination($sql, $rows_per_page = 10, $links_per_page = 5, $append = "") {
    //$this->conn = $connection;
    $this->sql = $sql;
    $this->rows_per_page = (int)$rows_per_page;
    if (intval($links_per_page ) > 0) {
        $this->links_per_page = (int)$links_per_page;
    } else {
        $this->links_per_page = 5;
    }
    $this->append = $append;
    $this->php_self = htmlspecialchars($_SERVER['PHP_SELF'] );
    if (isset($_GET['page'] )) {
        $this->page = intval($_GET['page'] );
    }
}

public function paginate() {
    //Check for valid mysql connection
    if (! $this->IsConnected()) {
        $this->SetError("No connection");
        return false;
    }

    //Find total number of rows
    $all_rs = @mysql_query($this->sql );
    if (! $all_rs) {
        if ($this->debug)
            echo "SQL query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
        return false;
    }
    $this->total_rows = mysql_num_rows($all_rs );
    @mysql_close($all_rs );

    //Return FALSE if no rows found
    if ($this->total_rows == 0) {
        if ($this->debug)
            //echo "Query returned zero rows.";
        return FALSE;
    }

    //Max number of pages
    $this->max_pages = ceil($this->total_rows / $this->rows_per_page );
    if ($this->links_per_page > $this->max_pages) {
        $this->links_per_page = $this->max_pages;
    }

    //Check the page value just in case someone is trying to input an aribitrary value
    if ($this->page > $this->max_pages || $this->page <= 0) {
        $this->page = 1;
    }

    //Calculate Offset
    $this->offset = $this->rows_per_page * ($this->page - 1);

    //Fetch the required result set
    //echo $this->sql . " LIMIT {$this->offset}, {$this->rows_per_page}";
    $rs = @mysql_query($this->sql . " LIMIT {$this->offset}, {$this->rows_per_page}" );
    if (! $rs) {
        if ($this->debug)
            echo "Pagination query failed. Check your query.<br /><br />Error Returned: " . mysql_error();
        return false;
    }
    return $rs;
}

Answer 1

在查询的末尾有一个;，然后尝试将LIMIT 0,20添加到查询的末尾。所以它看起来像这样：blah blah blah where something=value; LIMIT 0,20。这行不通的。卸下要修复的;。

附注，您可能对SQL_CALC_FOUND_ROWS语法感兴趣，因为这将极大地优化您的分页方法。

Answer 2

SQL语句的末尾有一个分号。我猜想PS_Pagination方法只是将LIMIT 0, 20附加到您的查询中，所以它看起来像SELECT blah blah; LIMIT 0, 20，这不是有效的SQL。