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

Investigate the usage of PDO, that will help you build your query php.net/manual/en/book.pdo.php for example stackoverflow.com/questions/1391777/… — Scuzzy
– Scuzzy, Commented Sep 3, 2015 at 23:33
@vee this is an EXCELLENT idea. I don't know how I didn't think it sooner... — Fane
– Fane, Commented Sep 3, 2015 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
– zheek, Commented Sep 3, 2015 at 23:48

Barmar · Accepted Answer · 2015-09-03 23:50:16Z

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 3, 2015 at 23:50

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fane Over a year ago

ty, you answer pointed me towards the right direction. I just changed $var=NULL to $var="NULL" and it worked

Barmar Over a year ago

But when the variable contains something other than NULL you have to remember to add the extra quotes.

Loïc · Accepted Answer · 2015-09-03 23:35:16Z

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 3, 2015 at 23:35

Loïc

12k2 gold badges35 silver badges52 bronze badges

1 Comment

Fane Over a year ago

This made the query like this VALUES (,,,,) ... (when all vars are null)

Collectives™ on Stack Overflow

INSERT NULL into sql query using php

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related