-2

I'm trying to increase the offset every time by 3, so I used a variable that is increased by 3 every time a button is clicked (using ajax).

However, I keep getting a syntax error when using OFFSET.

Is this the correct way to do it?

$web = "SELECT * FROM `db` WHERE catid = 9 AND state = 1 ORDER BY ordering LIMIT 0,".$_POST['limit']." OFFSET ".$_POST['limit']."";

limit is increased by 3 every button click, if I leave out OFFSET, it works, but it keeps loading all items again, instead of just 3 new items.

My error:

There was an error running the query [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 'OFFSET 3' at line 1]

Rory McCrossan
  • 331,213
  • 40
  • 305
  • 339
twan
  • 2,450
  • 10
  • 32
  • 92
  • 2
    besides Gordon's answer; you're open to an SQL injection here. – Funk Forty Niner Aug 04 '16 at 12:41
  • 2
    Like @Fred-ii- says, [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Aug 04 '16 at 12:42
  • Prefer prepared statements for SQL incection prevention. Alternatively explicitly convert $_POST['limit'] to an integer in PHP and insert that into the string. That is similar to quoting for strings, but for numbers. Anyway, I'd always propose prepared statements as best solution, since it works the same for all datatypes whatsoever. – cdonat Aug 04 '16 at 13:40
  • I always use protection against SQL injection, but I first like to make the code work. So I didn't add it yet in my question. It's just a matter of adding a simple line. – twan Aug 04 '16 at 13:42

2 Answers2

3

The syntax should be:

 LIMIT ".$_POST['limit']." OFFSET ".$_POST['limit']."";

The 0 conflicts with OFFSET.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You look like your using both limit x,x and offset X in the same query.

Try removing the offset part and just using limit X,x.

Craig Longford DeltaWolf7
  • 11
  • 2
  • You can use both in a query. In fact offset can only be used when limit is inside a query. – twan Aug 04 '16 at 12:46