ID: 30908
Updated by: [EMAIL PROTECTED]
Reported By: phoeniks2k at mail dot ru
-Status: Open
+Status: Closed
Bug Type: Documentation problem
Operating System: All
PHP Version: 5.0.2
New Comment:
This bug has been fixed in the documentation's XML sources. Since the
online and downloadable versions of the documentation need some time
to get updated, we would like to ask you to be a bit patient.
Thank you for the report, and for helping us make our documentation
better.
"It's not allowed to compare marker with NULL by "? IS NULL" too."
Previous Comments:
------------------------------------------------------------------------
[2005-04-05 19:43:53] phoeniks2k at mail dot ru
Here placeholder is *NOT* an indentifier. Not a column name, but a
variable/ Here is a rewritten example
prepare is smth like SET @BOUND_VAR = ?;
bind sets variable to NULL
$code = NULL;
$stmt->bind_param('i', $code);
SELECT id, val
FROM table_name
WHERE id = 1
AND (val = @BOUND_VAR OR @BOUND_VAR IS NULL)
Now @BOUND_VAR is threated as 0 instead of NULL. that's incorrect
------------------------------------------------------------------------
[2005-04-05 16:10:50] [EMAIL PROTECTED]
It is already documented:
mysqli_prepare:
"However, markers are not allowed for identifiers (such as table or
column names), in the select list that names the columns to be returned
by a SELECT statement), or to specify both operands of a binary operator
such as the = equal sign."
------------------------------------------------------------------------
[2004-11-29 15:56:52] phoeniks2k at mail dot ru
Copy-paste sucks )))
But query
SELECT id, val FROM table_name WHERE id = 1 AND (val = NULL OR NULL IS
NULL)
returns 2 rowa with NULL as val AND 2 as val )))
------------------------------------------------------------------------
[2004-11-29 15:54:04] phoeniks2k at mail dot ru
Sorry I misused a little.
Troubles are not in INSERTs, but in SELECTS
<?php
$mysqli = new mysqli('localhost', 'p', 'p', 'p');
$code = 1;
$language = NULL;
$stmt = $mysqli->prepare("SELECT id, val FROM table_name WHERE id = ?
AND (val = ? OR ? IS NULL)");
$stmt->bind_param('iss', $code, $language, $language);
//$stmt->bind_param('i', $code);
$stmt->execute();
$stmt->bind_result($col1, $col2);
while ($stmt->fetch()) {
printf("%s %s\n", $col1, $col2);
}
$stmt->close();
$mysqli->close();
?>
Table contains data
+----+--------+
| id | val |
+----+--------+
| 1 | 2 |
| 1 | [NULL] |
+----+--------+
The statement above returns 0 rows.
But query
SELECT id, val FROM table_name WHERE id = 1 AND (val = NULL OR NULL IS
NULL)
returns 1 row with NULL as val (as it should realy do)
AND
SELECT id, val FROM table_name WHERE id = 1 AND (val = 2 OR 2 IS NULL)
returns 1 row with 2 as val (as it should realy do)
------------------------------------------------------------------------
[2004-11-26 15:44:11] [EMAIL PROTECTED]
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves.
A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external
resources such as databases, etc.
If possible, make the script source available online and provide
an URL to it here. Try avoid embedding huge scripts into the report.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/30908
--
Edit this bug report at http://bugs.php.net/?id=30908&edit=1
