PHP - how to add to variable NULL value and save in into database?

Question

I am trying set variable to NULL:

$q = NULL.",";
$q .= 'abc';

and save in to database:

mysql_query("INSERT INTO table (col_a, col_b) VALUES (".$q.")")

But this generates error message:

INSERT INTO table (col_a, col_b) VALUES (,'abc')You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ','abc')' at line 2

How to save NULL into database from variable?

score 5 · Accepted Answer · edited May 23 '17 at 12:27

you can get rid of the column where you want its value to be null, eg

$q = 'abc';
mysql_query("INSERT INTO table (col_b) VALUES ($q)")

or if value is dynamic, the only problem with your current code is that you haven't include NULL in your string,

$q = "NULL,";
$q .= "'abc'";
mysql_query("INSERT INTO table (col_a, col_b) VALUES ($q)")

the your code is vulnerable with SQL Injection, please read the article below to learn how to prevent from it.

How can I prevent SQL injection in PHP?

score 2 · Answer 2 · answered Dec 25 '12 at 01:34

2

You're concatenating NULL and trying to parse it, which in the statement, would actually read:

, abc

Instead of the expected

NULL, abc

So just write a literal "NULL" in your statement.

answered Dec 25 '12 at 01:34

David Harris

2,697
15
27

score 2 · Answer 3 · answered Dec 25 '12 at 01:35

2

Pass the literal, unquoted string inside the query:

$q = "NULL,";

answered Dec 25 '12 at 01:35

Niet the Dark Absol

320,036
81
464
592

score 2 · Answer 4 · answered Dec 25 '12 at 01:35

$q = "NULL,";
$q .= '"abc"';

Gives you NULL,"abc" which you can include in your query.

The error you posted couldn't have come from the code in the question.. where did the quotes around abc come from? You need to enclose it in quotes inside the quotes for the php variable for them to show up in the SQL too.

PHP - how to add to variable NULL value and save in into database?

4 Answers4