2

I want to pass a GET var into the query to set the OFFSET. 

try {

  $sql_offset =  isset($_GET['offset']) ? $_GET['offset'] : 0;

  $main_query = "SELECT * FROM Orders
                LIMIT 150 OFFSET :offset";

  $result = $db->prepare($main_query);
  $result->bindParam(':offset', $sql_offset, PDO::PARAM_INT);
  $result->execute();

} catch (Exception $e) {

  $error = $e->getMessage();
  echo "<h2>".$error."</h2>";
  die();

}

The query fails and the error message received is:

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 ''450'' at line 2

Note: If I place 450 directly into the query it works fine.

It seems to me that the problem is that it is reading the param as a string '450' and not an int 450. I tried solving that with the PDO::PARAM_INT - didn't help.
I parsed the value with (int)$sql_offset and int_val($sql_offset) and didn't help. Meaning, I was still receiving the same error message.

Question: am I right? Is the problem that it is seeing as a string? If so, how can I fix?
Or is there a different problem here?

Arcv
  • 279
  • 3
  • 17
Ben
  • 515
  • 5
  • 18
  • Try this `$main_query = "SELECT * FROM Orders LIMIT :offset, 150";` – Dileep Kumar Jun 22 '17 at 07:29
  • 2
    Try `$result->bindValue(':offset', (int) $sql_offset, PDO::PARAM_INT);` – Sebastian Brosch Jun 22 '17 at 07:30
  • Are you using native or emulated prepared statements? – Álvaro González Jun 22 '17 at 07:38
  • @SebastianBrosch Thanks! that worked. If you want to you can post the answer and I'll accept it. :-) – Ben Jun 22 '17 at 07:45
  • No. Next time please use the search on StackOverflow! Álvarao González marked this question as duplicate. I got the solution also from this duplicate. https://stackoverflow.com/help/how-to-ask – Sebastian Brosch Jun 22 '17 at 07:50
  • @SebastianBrosch For sure I did a search before I posted and must have missed this or thought it didn't apply. As an aside - the others title was very specific in the wording with "bindValue" and "LIMIT" while I was looking for bindParam and OFFSET. Anyway, no worries and thanks for your help! – Ben Jun 22 '17 at 07:53

1 Answers1

0

Try to parse value in int then check:

$sql_offset =  (int)(isset($_GET['offset']) ? $_GET['offset'] : 0);
B. Desai
  • 16,414
  • 5
  • 26
  • 47