BIG problem with null & variables

[lelyea]

My problem is inserting a null value when a post variable is empty . . .
In this simplified version I have created a table with 3 columns:
DROP TABLE IF EXISTS application.value;
CREATE TABLE application.value (
key int(10) unsigned NOT NULL auto_increment,
value char(3) default NULL,
value2 varchar(3) default NULL,
PRIMARY KEY (key)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

Simplified version:

<?php
include("database_inc.php");
$value = "NULL";
$value2 = NULL;
$dbs = "application";
@ $db = new mysqli($hst, $usnm, $pwd, $dbs);
$query = "INSERT INTO value Values(null, '$value', '$value2' ) ";
if(!mysqli_query($db, $query)){
	echo mysqli_error($db);	
	}
$db->close();
?>

The above code writes to the DB, but inserts the characters NUL instead of the value null into column 2 ($value) and nothing into column 3 ($value2).

IS NOTHING THE SAME AS NULL??

The DB Admin is insisting that inserting nothing is not the same as null.

I realize I could do two different INSERT statements based on IF, however the form has many fields, posts many variables, and the actual query is:

$sql = "INSERT INTO IU_NAME VALUES('$key', '$key', null, null, null, null, null, null, null, null, null, '$LAST_NAME', '$FIRST_NAME', '$MIDDLE_NAME', '$BIRTH_NAME', '$NEWID', null, null, null, '$SUFFIX', null, null, null, null, null, null, '$CURRENT_ADDRESS', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, '$NOTNULL', null, null, null, null)";

Any help is greatly appreciated!

Len

[laserlight]

An empty string is not the same as NULL.

[quaffapint]

Out of curiosity, why do you want to insert a null? Why not just insert the values that are populated and let the db insert nulls into the remaining fields?

[lelyea]

Thanks for your replies . . . I am REAL open here! How do I make my INSERT statement decide what to, or not to insert? I have to avoid writing multiple insert statements. The actual insert statement above is one of six, and is the smallest of the bunch.

[quaffapint]

You can just tell it what to insert, such as...

INSERT INTO table1(row1, row3) VALUES('value1', value3 );

[lelyea]

I don't think I get the picture yet. Suppose user1 fills out all 3 of the fields (no null values) and then user2 leaves field 2 blank (1 null) and user3 leaves field 3 blank (1 null) and user4 leaves both field2 and field3 blank (2 nulls)? I don't want to force them to put anything in the fields.

[laserlight]

If you want to cater for that, then you need to check if the field is empty, and if so, put a NULL in the corresponding field. An empty string could well be good enough, in my opinion.

[quaffapint]

If this is user data - would it not be empty string and not null?

[laserlight]

Also, you might want to specify the columns in the insert statement, and hence leave those unspecified columns to take on the default values, or null, e.g.,

$query = "INSERT INTO value (`value`, `value2`) VALUES ('$value', '$value2' ) ";

Actually, you should be using prepared statements, but I am afraid that I have no idea how to bind NULL to a prepared statement parameter with MySQLi.

[laserlight]

Also, you might want to specify the columns in the insert statement, and hence leave those unspecified columns to take on the default values, or null, e.g.,

$query = "INSERT INTO value (`value`, `value2`) VALUES ('$value', '$value2' ) ";

Actually, you should be using prepared statements, but I am afraid that I have no idea how to bind NULL to a prepared statement parameter with MySQLi.

[lelyea]

Well, that is what I thought:
"If this is user data - would it not be empty string and not null?"

and by using :

$SUFFIX = $_POST['suffix'];
if(!$SUFFIX) {
$SUFFIX = NULL;
}

I insert an empty string. Like I said, the DB admin complained. I do find it hard to believe that there is no way to assign something to a $variable in PHP which will insert a null when inserted as INSERT into TABLENAME Values(null, '$variable') using the single quotes. I have tested code and it is not possible to query with SELECT * FROM tablename WHERE columnname IS NULL when it is in fact an empty string and not an actual null value. I have spent many hours here and on Google and find LOTS of users struggling with the same issue.

[laserlight]

Eh, $SUFFIX is a PHP variable, and so what happens is that when you embed it into the SQL statement, the NULL becomes an empty string in SQL since the SQL statement quotes the value with single quotes. The NULL that we have been talking about is with respect to SQL, not PHP.

What you want to do is something like this:

$value = empty($_POST['value']) ? 'NULL' : "'" . mysql_real_escape_string($_POST['value']) . "'";
$query = "INSERT INTO value (`value`) VALUES ($value)";

But as I noted, it would be better to use prepared statements, if you can figure out how to bind the SQL NULL to the relevant prepared statement parameter.

[lelyea]

I really appreciate all the help. I tested this last and it still inserts NUL into the column, not a null value.

[laserlight]

lelyea wrote:

I really appreciate all the help. I tested this last and it still inserts NUL into the column, not a null value.

Then you are doing something wrong in your PHP code, or interpreting the results wrongly.

I suggest that you try to code the SQL statement manually, e.g.,

INSERT INTO value (`value`) VALUES (NULL)

Check the result. If you get what you expected, then your PHP code is wrong. If "it still inserts NUL into the column", then clearly NUL is your database viewer's way of displaying NULL.

[lelyea]

Yes, this works:

INSERT INTO value (value) VALUES (NULL)

it is a literal constant. What doesn't work is a variable:

$NULL = NULL;
INSERT INTO value (value) VALUES ('$NULL')

True both with SQL Server and MySQL. Seems like a shortcoming to me . . . If you type the line $NULL = NULL; into a php editor you will see that it indeed assignes the value of NULL to that variable. Testing with:
<?php
error_reporting(E_ALL);
$notnull = 1;
$isnull = NULL;
var_dump(is_null($notnull), is_null($isnull));
?>
Also proves that $isnull is indeed null. There is a bug somewhere in the mix.

[laserlight]

lelyea wrote:

it is a literal constant. What doesn't work is a variable:

$NULL = NULL;
INSERT INTO value (value) VALUES ('$NULL')

That is what I explained in post #11. What happens is that when the PHP code is interpreted, you get this SQL statement:

INSERT INTO value (`value`) VALUES ('')

Now it is obvious that you are not inserting NULL, but an empty string. As such, what you need to do is this:

$value = 'NULL';
$query = "INSERT INTO value (`value`) VALUES ($value)";

Notice that I have removed the single quotes and instead of assigning NULL to the PHP variable, I have assigned the PHP string literal 'NULL'. But this means that when the user does enter something, you have to add the single quotes back.

If you used the PDO extension instead, I would suggest something like this:

$stmt = $db->prepare("INSERT INTO value (`value`) VALUES (:value);
$stmt->bindValue(':value', 0, PDO::PARAM_NULL);

then if you actually need to insert a string change the second statement to:

$stmt->bindParam(':value', $_POST['value'], PDO::PARAM_STR);

This avoids the need to add the single quotes back. MySQLi also has support for prepared statements, but I cannot figure out how to bind NULL.