Limit and Offset

Hello,

I'm trying to create sql statement to work with my pagination class, but the statement doesn't return results. When trying to run the statement from phpmyadmin I see the keyword OFFSET isn't being recognized for some reason. Usually keywords turn a purple color, but OFFSET stays black.

The statement below

SELECT `ID`, `videoName`, `videoEmbed`, `videoDescription`, `videoHost`, `plays` FROM `videos` WHERE `userName` = ? LIMIT ?  OFFSET ?

Code (markup):

 

Forget offset - just do:

SELECT ID,videoName,videoEmbed,videoDescription,videoHost,plays FROM videos WHERE userName = ? LIMIT ?,?

Code (markup):

The first ? after limit is the offset, the second is the amount of entries you want. You usually get the offset by doing something like this (in PHP) before:

$elements_per_page = 10;
$offset = (isset($_GET['page']) ? ($_GET['page'] * $elements_per_page) : 0);

Code (markup):

 

hm, I'm not sure why this isn't working. The ststatement returns results in phpmyadmin, but not when run from PDO.

  $videoResults = array();
  
  $dbConnect = new PDO($dsn, $dbUserName, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
  
  $sql = $dbConnect->prepare("SELECT `ID`, `videoName`, `videoEmbed`, `videoDescription`, `videoHost`, `plays` FROM `videos` WHERE `userName` = ? LIMIT ?, ?");
  
  try{
  
  $sql->execute(array($user->return_username(), $pager->return_offset(), $pager->return_perpage()));
  $videoResults = $sql->fetchAll();
  
  }catch(\PDOException $e){  }
  
  echo '<p>user: '.$user->return_username().'</p>';
  echo '<p>offset: '.$pager->return_offset().'</p>';
  echo '<p>perpage: '.$pager->return_perpage().'</p>';

PHP:

This echoes outputs

user: JeremyBenson11
offset: 0
perpage: 5

but when I dump $videoResults I get
array (size=0) empty

 

I returned the error message in $e

Says

SQLSTATE[42000]: Syntax error or access violation: 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'

below the same query returns five results in phpmyadmin

SELECT `ID` , `videoName` , `videoEmbed` , `videoDescription` , `videoHost` , `plays` FROM `videos`WHERE `userName` = 'JeremyBenson11' LIMIT 0 , 5

The only difference is I've replace the ? with values, added single quotes around userName.

the error message shows:
''0', '5'' at line 1'

are those extra quotes being placed there by PDO or something?

EDIT:

I think that's the problem. I just put the query statement back into phpmyadmin and put quotes around the numbers for offset and limit and got a similar error...

#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

How do you keep PDO from quoting numeric values? Would like to test it out.

 

You need to define the value as integer, not a string - or rather, you need to tell PDO to not default to everything as strings...
You can do that by assigning it to

PDO::PARAM_INT

Code (markup):

- however, I'm not entirely sure if you can assign it as that using an array.

 

using bind param method... I might rewrite this bit of code. I guess there's a known bug here where some people have a problem with this happening anyway, but I'll try that method out. Strangely I've never had this happen with other numbers, I've used ID numerous times, and numbers for number of plays and whatever. What I did for now was take offset and limit outside of the prepared statement and put those variables inside the query... Is it dangerous to have simple numbers such as offset and limit used that way?

 

It defies the purpose a bit, and if you're really unlucky, and using GET-parameters (URL-parameters) it can be a huge risk - however, just do an int or intval() on the variable before passing it to the query, then at least you're sure it's a number.
The reason this works most of the time is that ordinarily, MySQL doesn't really differ between ints and strings - and PHP, with its lax typecasting can for instance do math on strings (recasting when needed) - it might end up a mess, but usually it just works.

 

I'll try intval. I already tried (int). Casting was my first solution, but it threw the same error.

 

nope, same deal... there's talks at the following links on the same issue. It's happening even when some use the bindParam method. Not sure how to fix it though. I'll have to read through.... The stack overflow post has an answer mentions something about documentation, while a commenter says they're talking about whether or not it's a design flaw, or documentation flaw... to be honest I have no sweet clue what they mean, so I'll have to go through the bug report a bit..

http://stackoverflow.com/questions/5356206/mysql-pdoquote-putting-single-quotes-around-integers

https://bugs.php.net/bug.php?id=44639

 

cool, you and the bug report were right. I converted to bind param method and used intval, and set the third argument to int. I'm just wondering why this bug all of a sudden, lol.

 

Basically it's because the limit-method needs ints, not strings, I think. I've had the same problem in one other setting, although I can't for the life of me remember what that was. I would characterize it as a pretty severe bug, that really should be fixed.