1

When I insert a NULL into a MYSQL INTEGER field via a PHP prepared statement it inserts as 0.

I have searched for similar questions and have update my code but am still having the problem.

In my code, if a text box sends an empty string to PHP; PHP converts it to NULL ie.

$v3 = (strlen($v3)<1 ) ? NULL : $v3;

As an example, a result in the UNIT column could be NULL.

The prepare statement

$stmt = $mysqli->prepare("INSERT INTO address ( `PERSON`, `TYPE`, `UNIT`, `STREET_NUM`, `STREET`, `STREET_TYPE`, `SUBURB`, `STATE` ) VALUES (?,?,?,?,?,?,?,?)"));

Bind the parameter as an integer

$stmt->bind_param('isiissss',$p, $add_type[$a], $unit[$a], $street_num[$a], $street_name[$a], $street_type[$a], $suburb[$a], $state[$a]);

In MYSQL, address.unit is NULLABLE and has no default value.

If I insert directly into the table and omit a value for the unit column; NULL is stored. Which I expected.

I suspect that the bind_param function changes NULL to 0 if the datatype for the column is specified as 'i' (integer), because NULLs passed to VARCHAR columns are stored as NULL. Am I correct and if so how should I pass a NULL to the integer column?

rosscosack
  • 67
  • 8
  • 2
    The table probably has the column set as `not null` with a default of `0`. You could just remove the `not null` and set default to null. If the table is setup correctly, `null` is a valid value. – Jonathan Kuhn Aug 19 '13 at 23:54
  • Your table construct should have included a `not null` switch. Have you set this on your column? – Daryl Gill Aug 19 '13 at 23:55
  • This should answer your question http://stackoverflow.com/questions/4920873/mysqli-prepared-statements-insert-null-using-bind-params – RiggsFolly Aug 19 '13 at 23:58
  • @RiggsFolly no, in that link they are passing a text string of NULL ie 'NULL' where as I am passing a NULL. Also as noted above, "NULLs passed to VARCHAR columns are stored as NULL" which means it is not a problem with how PHP is storing null – rosscosack Aug 20 '13 at 00:04
  • 1
    @rosscosack Yes and that was their ERROR. They should not have been passing `'NULL'` they should pass `NULL` – RiggsFolly Aug 20 '13 at 00:11

1 Answers1

0

To simplify my question (and because I didn't think it would be relevant) I omitted that the values where passing through a $mysqli->real_escape_string function and after some testing I found that it converts a NULL to an empty string

$test = "";
$test = (strlen($test)<1 ) ? NULL : $test;
var_dump($test); // null
echo "<br>";
$test = $mysqli->real_escape_string($test);
var_dump($test);  // string '' (length=0)

This does not solve my problem but it does answer this question

rosscosack
  • 67
  • 8