CODEWITHSUNDEEP
phpmysqlinsertnull
Oliver
Oliver

Reputation: 23550

MySQL - How to insert a possibly null value

I have some values in a PHP script that I must insert in a MySQL table. Here is for example 3 dates d1, d2, and d3.

I have that default statement to insert the values into the database:

insert into mytable set a=1, b=2, d1='$val', d2='$val2', d3='$val3'

Is there a very simple way I can write the SQL statement to insert into d1, d2 and d3 NULL values instead of a date if one (or more) of $val1, $val2 and $val3 is nil ? Without having to deal with new PHP variables?

Upvotes: 1

Views: 532

Answers (4)

Oliver
Oliver

Reputation: 23550

I found that way to be the simpliest way to do what I wanted to do:

INSERT INTO mytable SET a=1, b=2, d1=IF('$val' = '', NULL, '$val');

Upvotes: 0

glglgl
glglgl

Reputation: 91149

The simplest thing would be to define

function esc_null($src) {
    return $src === null ? 'NULL' : ("'" + mysql_real_string_escape($src) + "'");
}

"insert into mytable set a=1, b=2, d1=" . esc_null($val) . ", d2=" . esc_null($val2) . ", d3=" . esc_null($val3)

This as well ensures integrity of your queries.

Upvotes: 1

Tadeck
Tadeck

Reputation: 137440

Assuming you have it all in $data array:

$query_string = "insert into mytable set a=1, b=2, d1=" .
    (isset($data['val1']) ? "'" . $data['val1'] . "'" : 'NULL') . ", d2=" .
    (isset($data['val2']) ? "'" . $data['val2'] . "'" : 'NULL') . ", d3=" .
    (isset($data['val3']) ? "'" . $data['val3'] . "'" : 'NULL') . ";";

The string will contain eg. d2=NULL instead of d2='xyz', if the value in array does not exist or is equal to null.

You have to also make sure that the strings you want to paste into the query are properly sanitized (eg. using mysql_real_escape_string()). My answer treats your variables as already prepared and sanitized, as you only wanted to know how to insert NULL values instead of the variables you have.

Because the question was only about entering NULL values in the query based on the value of variables, I do not think discussing sanitization in detail here would be a good idea. You can find a lot more detailed info on SQL Injection and how to prevent it in PHP here: Best way to stop SQL Injection in PHP. Good luck! :)

Upvotes: 4

Trott
Trott

Reputation: 70163

There's probably a better way to do this, but if all else fails, you can do this with each of your values:

$val = isset($val) ? "'$val'" : "NULL";

Then change your PHP query string to this:

insert into mytable set a=1, b=2, d1=$val, d2=$val2, d3=$val3

You should consider using prepared statements and PDO instead of building a query string like that, especially if there is user input involved in your variables.

Upvotes: 3

Related Questions