PDO SQLSTATE[HY093]

Question

<?php
$server   = 'mysql:dbname=test;host=localhost';
$user     = 'root';
$password = '';
$pdo      = new PDO($server, $user, $password);
$pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$test=0;
$teest=3;
$query = $pdo->prepare('SELECT * FROM imgdb LIMIT :staaart,8');
$query -> execute(array('staaart' => $test, 'eeend' => $teest));
$result=$query->fetchAll();
print_r($result);
?>

This is my PHP/PDO and i get this Warning:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampplite\htdocs\pdo_db_connection.php on line 11

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number in C:\xampplite\htdocs\pdo_db_connection.php on line 11 Array ( )

EDIT: Now it works for me

$server   = 'mysql:dbname=test;host=localhost';
$user     = 'root';
$password = '';
$pdo      = new PDO($server, $user, $password);
$pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$test=0;
$teest=2;
$teeest='Bochum';
$query = $pdo->prepare('SELECT * FROM imgdb WHERE stadt = :stadt LIMIT :staaart,:eeend');
$query->bindParam(':stadt', $teeest, PDO::PARAM_STR);
$query->bindParam(':staaart', $test, PDO::PARAM_INT);
$query->bindParam(':eeend', $teest, PDO::PARAM_INT);
$query -> execute();
$result=$query->fetchAll();
print_r($result);

And you really have no idea why? The warning is pretty clear. — Daan, Dec 12 '14 at 14:44
Clearly you are attempting to send two parameters, but used the literal `8` as the limit in the prepare(). — Michael Berkowski, Dec 12 '14 at 14:44
But fixing that will only get you part way. PDO will try to bind those values in `LIMIT` as strings, when you pass the array to `execute()` rather than explicit `bindParam()` calls with `PDO::PARAM_INT`. So you'll still be faced with a syntax error _if_ you are using emulated prepares. — Michael Berkowski, Dec 12 '14 at 14:45
i tried it with :eeend but then: Warning: PDOStatement::execute() [pdostatement.execute]: 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',8' at line 1 in C:\xampplite\htdocs\pdo_db_connection.php on line 12 — Taeq, Dec 12 '14 at 14:51
@Taeq That is because of the issue in my earlier comment. Have a look at the thorough explanation in [the question linked ](http://stackoverflow.com/questions/10014147/limit-keyword-on-mysql-with-prepared-statement). You can't pass those as an array to execute(), lest they be treated as strings. — Michael Berkowski, Dec 12 '14 at 14:54
uh -.- i'm new to pdo. it works thank you michael! but one last question. can i do something like that?: $query = $pdo->prepare('INSERT * FROM :selectdb'); — Taeq, Dec 12 '14 at 15:21

score 0 · Answer 1 · answered Dec 12 '14 at 14:46

You only have one parameter in your prepared query, but are attempting to bind two:

$query = $pdo->prepare('SELECT * FROM imgdb LIMIT :staaart,8');
$query -> execute(array('staaart' => $test, 'eeend' => $teest));

Either prepare both staaart and eeend:

$query = $pdo->prepare('SELECT * FROM imgdb LIMIT :staaart,:eeend');
$query -> execute(array('staaart' => $test, 'eeend' => $teest));

Or execute with just staaart:

$query = $pdo->prepare('SELECT * FROM imgdb LIMIT :staaart,8');
$query -> execute(array('staaart' => $test));

PDO SQLSTATE[HY093]

1 Answers1