Submit null instead of an empty string?

[Berre]

Is there an easy way to submit value null to the db instead of an empty string?

 

INSERT INTO Blabla(text) VALUES('')

This results in adding an empty string

 

INSERT INTO Blabla(text) VALUES(null)

This results in adding a null value

 

When using PHP I have to use a lot of extra (messy) code and add the quotations to each variable...

 

if it is empty then $var = "null", else $var = "'my text'" (with single quotations) and then add to db using:

INSERT INTO Blabla(text) VALUES($var)

 

I can of course live with having empty strings in the db, but I'd prefer null values.

[Pikachu2000]

$var = "null"; assigns a string with the value of 'null' to $var. $var = null assigns a null value. So if you want a NULL value, use NULL without quoting it, both in php and in SQL syntax.

 

$var = "null";
var_dump($var);
// returns: string 'null' (length=4)

$var = null;
var_dump($var);
// returns: null

[DavidAM]

When using PHP I have to use a lot of extra (messy) code and add the quotations to each variable...

 

Yeah, that's about how it is done. I use a function to handle this for me:

function quoteStringNull($value, $delim = "'") {
  if (empty($value)) return 'NULL';
  else return $delim . mysql_real_escape_string($value) . $delim;
}

$sql = 'UPDATE aTable SET aColumn = ' . quoteStringNull($newValue) . ' WHERE ID = ' . $theID;

 

The function actually does a little more than that, but that is the idea. I have a second function called quoteString(), which returns an empty string (instead of NULL) when the value is empty. I call the appropriate function depending on whether the column allows NULL or not.

 

The key is, the value returned by these functions has the delimiters I need for the SQL statement inside the returned value, so I don't have to put them in the SQL string. That way, the function will return the string NULL, without the delimiters around it.

 

Obviously, the quoteStringNull() is not useful for the WHERE clause, only for the VALUES of an INSERT or UPDATE.

[Berre]

I guess I'll make a generic function like that.

 

Thanks