PDO Bind Offset

Question

I am trying to get this SQL to run, however when I use PDO it just displays nothing, I think its something to do with the ":offset" because if I remove that and type a number, it works fine. the offset variable is correct and functions as its supposed to

$newsQuery = $pdo->prepare("SELECT * FROM News ORDER BY News_ID DESC LIMIT 6 OFFSET :offset");
      echo $offset;
      $newsQuery->bindParam(":offset", $offset,PDO::PARAM_INT);
      $newsQuery->execute();

Alex Tartan · Accepted Answer · 2016-03-29T17:45:41.663

2

Try this:

$newsQuery->bindParam(":offset", intval($offset), PDO::PARAM_INT);

For some reason, PDO::PARAM_INT is not enough. It is still passed as string. So, forcing an integer conversion bypasses this inconvenience.

Note: I've recently faced this myself.

edited Mar 29 '16 at 17:45

answered Mar 29 '16 at 17:27

Alex Tartan

6,736
10
34
45

this gives me an error "Fatal error: Cannot pass parameter 2" – lmprowse Mar 29 '16 at 17:29
try `LIMIT 6, :offset` instead of `Limit 6 OFFSET :offset ` . That should work – Alex Tartan Mar 29 '16 at 17:32
Nope, my ide is flagging an error on "(int) $offset" saying only variables can be passed by reference – lmprowse Mar 29 '16 at 17:34
hmmm... `intval($offset)`? – Alex Tartan Mar 29 '16 at 17:34
Thank you! I added that, then put the OFFSET back into the statement and its working now :D – lmprowse Mar 29 '16 at 17:37
You have to understand the difference betweem bindparam and bindvalue – Your Common Sense Mar 29 '16 at 17:41
@YourCommonSense, lesson learnt! (gonna pay a bit more attention to that. I usually use `bindValue` and with that i can also pass values) – Alex Tartan Mar 29 '16 at 17:50

score -1 · Answer 2 · answered Mar 29 '16 at 17:25

-1

Just replace below line

$newsQuery->bindParam(":offset", $offset,PDO::PARAM_INT);

With

$newsQuery->bindParam("offset", $offset,PDO::PARAM_INT);

Where ever you have to bind :parameter do not use : sign while bind time.

answered Mar 29 '16 at 17:25

Ahesanali Suthar

341
3
23

PDO Bind Offset

2 Answers2