#30908 [Fbk->Opn]: How to use NULLS in mysqli prepared statements

ID:               30908
User updated by:  phoeniks2k at mail dot ru
Reported By:      phoeniks2k at mail dot ru
-Status:           Feedback
+Status:           Open
Bug Type:         Documentation problem
Operating System: All
PHP Version:      5.0.2
New Comment:

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)


Previous Comments:
------------------------------------------------------------------------

[2004-11-26 15:44:11] georg@php.net

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