INSERT NULL into sql query using php

Question

I have the following code:

$var1 = NULL;
$var2 = NULL;
$var3 = NULL;
$var4 = NULL;

/*  (...) code for logical flow condition (...) */

/* After the code, only var1 is different
 */

// $var1 = "something"

$query = "INSERT INTO (...) VALUES ('$var1','$var2','$var3','$var4');

Here is the deal: All fields for all vars are unique. The problem with the code is that the database doesn't insert with "duplicate value for $var2,3,4" because it is inserting the vars as ' ' instead of NULL. I want the values to be NULL BUT mantain it all in a coherent query... in a kind of concatenation if you may say, as to not disrupt the easiness of my logical flow code...

I want

$query = "INSERT INTO (...) VALUES ('$var1','$var2','$var3','$var4');

To be

$query = "INSERT INTO (...) VALUES ('$var1',NULL,NULL,NULL);

instead of how it is:

$query = "INSERT INTO (...) VALUES ('$var1','','','');

So, how can I do this keeping the variables in the query, both when NULL and not NULL? Tyvm for your help

You could override [`to_string`](http://php.net/manual/en/language.oop5.magic.php#object.tostring) method. — vee, Sep 03 '15 at 23:33
Investigate the usage of PDO, that will help you build your query http://php.net/manual/en/book.pdo.php for example http://stackoverflow.com/questions/1391777/how-do-i-insert-null-values-using-pdo — Scuzzy, Sep 03 '15 at 23:33
@vee this is an EXCELLENT idea. I don't know how I didn't think it sooner... — Fane, Sep 03 '15 at 23:34
if you don't pass any value in insert query ,by default php insert null in database (if you allow the filed can be null ) — zheek, Sep 03 '15 at 23:48

score 2 · Accepted Answer · answered Sep 03 '15 at 23:50

2

You need to test whether the value is a string or null, and only add quotes in the SQL if it's a string:

$var1_sql = $var1 === null ? "NULL" : "'$var1'";
$var2_sql = $var2 === null ? "NULL" : "'$var2'";
$var3_sql = $var3 === null ? "NULL" : "'$var3'";
$var4_sql = $var4 === null ? "NULL" : "'$var4'";

$query = "INSERT INTO (...) VALUES ($var1_sql, $var2_sql, $var3_sql, $var4_sql)";

But it would be better to use a prepared query.

$query = "INSERT INTO (...) VALUES (?, ?, ?, ?)";
$stmt = $conn->prepare($query);
$stmt->bind_param("ssss", $var1, $var2, $var3, $var4);
$stmt->execute();

answered Sep 03 '15 at 23:50

Barmar

741,623
53
500
612

ty, you answer pointed me towards the right direction. I just changed `$var=NULL` to `$var="NULL"` and it worked – Fane Sep 04 '15 at 13:47
But when the variable contains something other than `NULL` you have to remember to add the extra quotes. – Barmar Sep 04 '15 at 13:49

score 0 · Answer 2 · answered Sep 03 '15 at 23:35

0

Try something like this :

$var1 = NULL;
$var2 = "'a value'";
$var3 = NULL;
$var4 = "'another val'";

$query = "INSERT INTO (...) VALUES ($var1,$var2,$var3,$var4)";

answered Sep 03 '15 at 23:35

Loïc

11,804
1
31
49

This made the query like this `VALUES (,,,,)` ... (when all vars are null) – Fane Sep 03 '15 at 23:43

INSERT NULL into sql query using php

2 Answers2