1

I've been using parameters in my PHP application for MySQL Queries.

public function getAllPostsForCategoryFrom($CategoryID, $StartPos)
{
    $sql = "SELECT * FROM Post WHERE CategoryID = :CategoryID ORDER BY PostID DESC LIMIT :StartPos, 5";
    $query = $this->db->prepare($sql);
    $parameters = array(':CategoryID' => $CategoryID, ':StartPos' => $StartPos);
    $query->execute($parameters);
    return $query->fetchAll();
}

In the function above, it seems to pass $StartPos as a string which just throws an error

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', 5' at line 1

I have tried to cast the passed value as an integer as well however, it seems MySQL does not like to accept casts/convert in the limit.

CAST(:StartPos AS UNSIGNED);

I am aware of binding parameters like this:

$sql = SELECT * FROM tbl_news ORDER BY date DESC LIMIT :start, :rows";
$q = $db->prepare($sql);
$q->bindParam(':start', $start, PDO::PARAM_INT);
$q->bindParam(':rows',$rows, PDO::PARAM_INT);

However, I'm unsure how to do that with the kind of parameters I have been using. Would appreciate any pointers on how I can get the value passed as an integer and not a string!

**EDIT: ** I should have mentioned this was MySQL compatible Aurora on AWS

Dharman
  • 30,962
  • 25
  • 85
  • 135
mimmi
  • 113
  • 1
  • 2
  • 15
  • Arithmetic cast/expressions `LIMIT (1*:Param), 5` work for recent versions. – mario Oct 27 '19 at 04:36
  • That's a nice one but its still giving me an error. Ah, is it possible it's not working as this is Aurora on AWS? – mimmi Oct 27 '19 at 04:46

0 Answers0