SQL LIMIT parametrized in PHP with PDO


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

Here's the error 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 '' at   line 1 'in /home/yuran/public_html/avec/DatabaseContent.php:30 Stack   trace: # 0 /home/yuran/public_html/avec/DatabaseContent.php(30):   PDOStatement-> Run (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 for my function.

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));

    return $q;
asked by anonymous 23.02.2015 / 16:00

1 answer


The problem is that the LIMIT and OFFSET variables are being passed as PARAM_STR and this causes the PDO to add apostrophes to 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); 

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

23.02.2015 / 16:17