note 103622 added to mysqli-stmt.bind-param
| From: | d.o_t) | Date: | Sat, 23 Apr 2011 01:05:35 +0000 |
| Subject: | note 103622 added to mysqli-stmt.bind-param | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-179100@lists.php.net to get a copy of this message | ||
It should be noted that MySQL has some issues regarding the use of the IN clause in prepared
statements.
I.e. the code:
<?php
$idArr = "1, 2, 3, 4";
$int_one = 1;
$int_two = 2;
$int_three = 3;
$int_four = 4;
$db = new MySQLi();
$bad_stmt = $db->prepare(SELECT
idAsLetters FROM tbl WHERE
id IN(?));
$bad_stmt->bind_param("s", $idArr);
$bad_stmt->bind_result($ias);
$bad_stmt->execute();
echo "Bad results:" . PHP_EOL;
while($stmt->fetch()){
echo $ias . PHP_EOL;
}
$good_stmt->close();
$good_stmt = $db->prepare(SELECT idAsLetters FROM tbl WHERE
id IN(?, ?, ?, ?));
$good_stmt->bind_param("iiii", $int_one, $int_two, $int_three, $int_four);
$good_stmt->bind_result($ias);
$good_stmt->execute();
echo "God results:" . PHP_EOL;
while($stmt->fetch()){
echo $ias . PHP_EOL;
}
$bad_stmt->close();
$db->close();
?>
will print this result:
Bad results:
one
Good results:
one
two
three
four
Using "IN(?)" in a prepared statement will return just one (the first) row from a
table/view. This is not an error in PHP, but merely how MySQL handles prepared statements.
----
Server IP: 69.147.83.197
Probable Submitter: 80.167.131.35
----
Manual Page -- http://www.php.net/manual/en/mysqli-stmt.bind-param.php
Edit -- https://master.php.net/note/edit/103622
Del: integrated -- https://master.php.net/note/delete/103622/integrated
Del: useless -- https://master.php.net/note/delete/103622/useless
Del: bad code -- https://master.php.net/note/delete/103622/bad+code
Del: spam -- https://master.php.net/note/delete/103622/spam
Del: non-english -- https://master.php.net/note/delete/103622/non-english
Del: in docs -- https://master.php.net/note/delete/103622/in+docs
Del: other reasons-- https://master.php.net/note/delete/103622
Reject -- https://master.php.net/note/reject/103622
Search -- https://master.php.net/manage/user-notes.php