SQL LIMIT parameterized in PHP with PDO

Asked

Viewed 2,041 times

15

A few days ago, I stopped using the functions mysql_*(already obsolete), and I switched to PDO. I have a function that does query database, but I’m having some problems using the LIMIT with Prepared statements.

Here’s the mistake I get:

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 ''0', '4' line 1' in /home/yuran/public_html/avec/Databasecontent.php:30 Stack trace: #0 /home/yuran/public_html/avec/Databasecontent.php(30): Pdostatement->execute(Array) #1 /home/yuran/public_html/avec/inc/header.php(43): Databasecontent->fetchSomeRows(Object(PDO), 'Topics', 'topic_id', 'DSC', 0, 4) #2 /home/yuran/public_html/avec/index.php(7): require_once('/home/yuran/pub...') #3 {main} thrown in /home/yuran/public_html/avec/Databasecontent.php on line 30

This is the code of my function.

<?php
private $sql = "SELECT * FROM ";
public function fetchSomeRows($conn, $table, $rowOrder, $direction, $initialLimit, $finalLimit)
{
    $this->sql .= "{$table} ORDER BY :roworder :direction LIMIT :initiallimit, :finallimit";
    $q = $conn->prepare($this->sql);
    $q->execute(array(':roworder' => $rowOrder,':direction'=>$direction,':initiallimit'=>$initialLimit,':finallimit'=>$finalLimit));
    $q->setFetchMode(PDO::FETCH_ASSOC);

    return $q;
}    
?>
php mysql sql pdo
  • 1

    In fact you could have switched to the functions mysqli that are almost identical and solved your problem more simply and without trauma.

  • You must inform another part of the code from which the property comes $this->sql. Because the exception reports a syntax error

  • @rray in the case of table names is true that it is not possible to bind so I used {$table} but I didn’t know the same thing happened with LIMIT.

  • 3

    I made a mistake, so I removed the comment, I tested the response of @André Ribeiro and it worked, living and learning :)

1 answer

14


The problem is that the LIMIT and OFFSET variables are being passed as PARAM_STR and this causes the PDO to add apostrophes in the numbers, generating a syntax error.

To solve just use bindParam to specify the type of variables being passed.

$q->bindParam(':finallimit', (int)$finalLimit, PDO::PARAM_INT); 
$q->bindParam(':initiallimit', (int)$initialLimit, PDO::PARAM_INT); 
$q->execute();

Note: The (int) there are to ensure that the variables are passed to function as int.

  • 2

    +1, did not know it, seldom.

  • Thank you very much Andre, the solution worked. However another problem came up with an identical error that I do not know how to solve. Please note that I have adapted your solution according to my code, where :roworder and :Direction are strings. However when I try to run it from error.

  • Here is the mistake : Pastebin

  • 1

    @Yuran Since column names and parameters are string type but cannot be between apostrophes/quotes, you cannot use bindParam for this. In your case you can place these variables directly in the query string. Ex.: {$table} ORDER BY {$rowOrder} {$direction}.

Browser other questions tagged php mysql sql pdo

You are not signed in. Login or sign up in order to post.