introducing null value

[carrilo]

I want to introduce to  a null value introduced into a table when I don't introduce any value.

 

I have the following code which I thought would do this but it in fact introduces a text value of "null" if I leave the quotation marks off it introduces a blank value (not null). How can I introduce a real null value?

$al3 = ( !empty($_POST['al3']) ) ? ($_POST['al3']): "NULL";

[cyberRobot]

Try removing the quotes:

http://php.net/manual/en/language.types.null.php

[AbraCadaver]

Or if the column default is NULL then just "" I think.

[carrilo]

I've tried both of these alternatives. In both cases the value introduced is blank/empty but not NULL

[vinny42]

PHP's idea of NULL is not necessarily SQL's idea of NULL.

 

If you want to insert NULL then the text NULL must appear in the query:

 

INSERT INTO table (columnname) VALUES (NULL);

 

This means that if your query puts quotes around it it will fail. If you append a variable containing NULL to a quer string you'll get a bad query.

If you give it an empty string it will store an empty string.

 

You'd have to use a construction like NULLIF(), but that only works if you make a distinction between NULL and an empty string.

 

So what remains is the prepared statement: http://php.net/manual/en/mysqli.prepare.php to which you can feed a variable with value NULL (not the string, but the PHP value (wel, lack of value, NULL indicates that the value is not known))

[carrilo]

Sorry I don't really understand this, could you give me an example

[DFulg]

The correct answer has already been posted:

$al3 = ( !empty($_POST['al3']) ) ? ($_POST['al3']): null;

Make sure that the relevant field excepts null values.

If you are not sure, use the "Explain table" syntax and post it here.

 

Also, never insert user data directly into a mysql query. Always properly sanitize the data first.

[carrilo]

If I write the query like this, with null without quotes the value entered is an empty value, not a null value.

 

Yes it does accept null values

Edited September 14, 2013 by carrilo

[vinny42]

[quote

The correct answer has already been posted:

$al3 = ( !empty($_POST['al3']) ) ? ($_POST['al3']): null;

 

Don't use empty() for this, you don't want the value zero or an empty string to be converted to null. Small difference, huge consequences.

 

 

@carillo, what do not not understand about prepared statements?

[DFulg]

My mistake, this will not work unless null is specified upon insert time since PHP treats null values differently then MYSQL.

You can however do this using a prepared statement in MYSQLi or PDO which should be what you are using anyway.

 

Below is pseudo code to illustrate an example usage:

$stmt = $mysqli->prepare("INSERT INTO table (field1, field2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);

$field1 = "string1";
$field2 = null;

$stmt->execute();

Edited September 14, 2013 by DFulg

[carrilo]

Ok I have found the error, the value should not be in quotes in the query

 

Insert into .... values $al3    (not '$al3')

 

this works with

 $al3 = ( !empty($_POST['al3']) ) ? "'{$_POST['al3']}'" : 'NULL';

[vinny42]

this works with

 

 

if you append $a13 into the query then yes, because you are creating the word NULL in SQL.

 

Still don't use empty() because that's not what you need, or should want. (basically just forget that empty() exists, it is quite a pointless function)

Edited September 14, 2013 by vinny42

[DFulg]

Again, I urge you to use prepared statements in MYSQLi or PDO instead of MYSQL, which is now deprecated.

Also again, never insert user data directly into a SQL statement as this is a security risk. Always properly sanitize data before insertion.

[vinny42]

 

Again, I urge you to use prepared statements in MYSQLi or PDO instead of MYSQL, which is now deprecated.

 

+1