Pagination using PDO with MySQL

问题:

I have some problems with the pagination. I executed the query directly on my database console and works fine..

public function method($arg, $db)//$db is a PDO connection link
{
    try
    {
        $next = $arg * 9;
        $top = 9;
        $sql = "SELECT col01, col02, col03 ";
        $sql .= "FROM table ";
        $sql .= "ORDER BY col01 ASC ";
        $sql .= ($next === 0)? "LIMIT ".$top : "LIMIT ".$next.", ".$top;    
        $return = $db->prepare($sql);
        $return->execute();

        $return->setFetchMode(PDO::FETCH_ASSOC);
        $this->minis = $return->fetch();
        return true;
    }
    catch(PDOExcepction $e)
    {
        return false;
    }
}

What am I doing wrong?

回答1:

You are only returning the first row because you only call fetch() once. Call it in a loop and accumulate the results into an array:

while ($row = $return->fetch()) {
  // Append the current row onto your array
  $this->minis[] = $row;
}
return true;

回答2:

PDOStatement::fetch — Fetches the next row from a result set

http://php.net/manual/en/pdostatement.fetch.php

You are only returning the first result with your single call to fetch(). You need to call fetch until there are no more results. Read the examples in the manual again.

回答3:

$this->minis = $return->fetchAll();

it'l return all the data in a multi dimensional array.