SQL LIMIT parametrized in PHP with PDO

Question

SQL LIMIT parametrized in PHP with PDO

Navigation

#1 by (14 votes)

15

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.

<?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

asked by anonymous 23.02.2015 / 16:00

1 answer

How to sort array of strings disregarding accents? How to find out the mobile operator of Android?

score 14 · Accepted 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); 
$q->execute();

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