2

I have this :

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'pass');

$max = 10;
$min = 0;
$q = $_GET['q'];

$result = $pdo->prepare("SELECT * FROM fruits WHERE name LIKE ? LIMIT ?, ?");
$result->execute(array('%'.$q.'%', $min, $max));

However it doesn't work (returns nothing) while when I replace LIMIT by LIMIT 0, 10 and remove $min and $max from the array it works. What am I doing wrong? I tried using '0' instead of 0 but it doesn't work either...

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Vilarix
  • 735
  • 1
  • 10
  • 16
  • 3
    [PDO prepared statement causes an error in LIMIT statement](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991623) – Your Common Sense May 11 '13 at 15:48

2 Answers2

2

PDO::execute escapes all params as STRING.

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'pass');

$max = 10;
$min = 0;
$q = (isset($_GET['q']) && is_string($_GET['q'])) ? $_GET['q'] : '';

$stmt = $pdo->prepare('SELECT * FROM fruits WHERE name LIKE ? LIMIT ?, ?');
$stmt->bindValue(1, "%{$q}%", PDO::PARAM_STR);
$stmt->bindValue(2, $min    , PDO::PARAM_INT);
$stmt->bindValue(3, $max    , PDO::PARAM_INT);
$stmt->execute();
mpyw
  • 5,526
  • 4
  • 30
  • 36
1

My guess is that the numbers are being binded as strings. From the manual:

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

So you should use bindValue instead of the execute shortcut.

Galen
  • 29,976
  • 9
  • 71
  • 89