CODEWITHSUNDEEP
phpmysql
Fane
Fane

Reputation: 2076

INSERT NULL into sql query using php

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

Upvotes: 0

Views: 55

Answers (2)

Barmar
Barmar

Reputation: 782508

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();

Upvotes: 2

Loïc
Loïc

Reputation: 11942

Try something like this :

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

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

Upvotes: 0

Related Questions