ID: 30908
User updated by: phoeniks2k at mail dot ru
Reported By: phoeniks2k at mail dot ru
-Status: Bogus
+Status: Open
Bug Type: Documentation problem
Operating System: All
PHP Version: 5.0.2
New Comment:
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
Previous Comments:
------------------------------------------------------------------------
[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.
------------------------------------------------------------------------
[2004-11-26 14:42:18] phoeniks2k at mail dot ru
Description:
------------
I can't find how to bind NULL with mysqli_stmt_bind_param()
Even when i use string and pass NULL it's converted to '' (empty
string), int is converted to 0.
But i need to insert NULLs.
If it's a doc miss, please describe how to use NULLs, othewise fix
mysqli extension
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=30908&edit=1
