LIMIT using offset error

Question

I'm loading items with Ajax, and I use an offset

JS:

var offset = 0;

$(".load-more").click(function() {
    offset+=5;
    $(this).addClass("spin");
    $.ajax({
      url: '/api/fetchitems.php?offset='+offset+'&sort='+sort,
      success: function(data){
        $(".load-more").before(data);
      }
    });
});

PHP/SQL:

$offset = intval($_GET["offset"]);

$stmt = $db->prepare(
 "SELECT s.id,s.date,s.title,s.views,s.image,s.hidpi,s.width,s.description,u.display_name,u.avatar, s.hotness
  FROM showcase AS s
  INNER JOIN users AS u ON s.user_id = u.id
  UNION ALL
  SELECT q.id,q.date,q.title,q.views,0,0,0,q.text,u.display_name,u.avatar, q.hotness
  FROM questions AS q
  INNER JOIN users AS u ON q.user_id = u.id
  ORDER BY hotness DESC
  LIMIT :skip, 5
  ");
$stmt->bindParam(":skip",$offset);
$stmt->execute();
$items = $stmt->fetchAll();
$stmt = null;

I'm getting this error at execute:

Fatal error: Uncaught exception 'PDOException' with message '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 ''5', 5' at line 9' in **** Stack trace: #0 **** PDOStatement->execute() #1 {main} thrown in

When I run the SQL in phpMyAdmin, it works fine.

check this url http://stackoverflow.com/questions/15853266/pdo-bindparam-not-allowing-statement-to-return-results hope it will help you — jilesh, Mar 04 '16 at 10:53
put the query "SELECT s.id.. to a variable $query and echo $query; exit; Then in the success ajax function alert it or bind it in a div. copy that query and execute in a mysql to see whats wrong in that query. This will help you to rectify your error without any1's help — rahul, Mar 04 '16 at 10:54

Martijn · Accepted Answer · 2016-03-04T10:55:50.540

4

Your :skip gets wrapped in quotes as if it's a string (because the default setting is PDO::PARAM_STR), which is wrong, You now get:

LIMIT '5',5;

According to documentation, you can change a type to int:

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

The reason this works in your PhpMyAdmin is because you don't add the single quotes.

edited Mar 04 '16 at 10:55

answered Mar 04 '16 at 10:52

Martijn

15,791
4
36
68

1

according to the docs `PDO::PARAM_STR` is default – Alex Andrei Mar 04 '16 at 10:54
Thanks. Added that to my answer. – Martijn Mar 04 '16 at 10:56

jilesh · Answer 2 · 2016-03-04T11:02:10.180

0

$stmt = $db->prepare(
 "SELECT s.id,s.date,s.title,s.views,s.image,s.hidpi,s.width,s.description,u.display_name,u.avatar, s.hotness
  FROM showcase AS s
  INNER JOIN users AS u ON s.user_id = u.id
  UNION ALL
  SELECT q.id,q.date,q.title,q.views,0,0,0,q.text,u.display_name,u.avatar, q.hotness
  FROM questions AS q
  INNER JOIN users AS u ON q.user_id = u.id
  ORDER BY hotness DESC
  LIMIT :skip, 5 
  ");

$stmt->bindParam(':skip', $offset, PDO::PARAM_INT);

You want to do in this way

edited Mar 04 '16 at 11:02

answered Mar 04 '16 at 10:56

jilesh

436
1
3
13

Hi, because you're new I'm not gonna downvote, but: This answer has no added value. It's better to explain *why* I "want to do it this way" so people can fix similar situation. Apart from that, there is another answer which already explains the problem, your answer doesn't provide more info, or an alternate solution – Martijn Mar 04 '16 at 11:02

LIMIT using offset error

2 Answers2