Problem with mysql LIMIT and PDO

Question

Trying to do a terrible pagination using PDO, but I can not find an answer suitable for my situation:

$pageVar = 10; $startRowLimit = ($page * $pageVar) - $pageVar; // returns correct value $articlesQuery = "SELECT `ID`,`Title`,`Text`,`Poster`,`Date`, ( SELECT `ID` FROM users WHERE article.`Poster` = users.`Username` ) FROM article WHERE `Visible` = 1 ORDER BY `Date` DESC LIMIT ? , ? "; $articles = $mysqli->selectAll($articlesQuery, array($startRowLimit, $pageVar)); 

Here's the selectAll function:

  public function selectAll($query, $params){ $this->sql = $this->dbh->prepare($query); $this->sql->execute($params); $result = $this->sql->fetchAll(PDO::FETCH_ASSOC); return $result; } 

How can I make it work? I tried to do

 array((int) $startRowLimit, (int) $pageVar) 

But that does not work. Any idea?

Answer 1

You do not need to use a sign to restrict it, since they do not come from user input. Just put your int variables in the request

 DESC LIMIT {$offset} , {$var} 

In any case, the correct syntax is:

 $sql = "SELECT * [...] LIMIT :start, :results"; $stmt = $dbh->prepare($sql); $stmt->bindParam(':start', $_GET['start'], PDO::PARAM_INT); $stmt->bindParam(':results', $_GET['results'], PDO::PARAM_INT);