PHP: Set variable to NULL and write NULL to MySQL [duplicate]

Question

Since years I've a simple question: How to set values to NULL and write NULL to MySQL?

$var = trim(mysqli_real_escape_string($_POST["input_from_user"]));
if(strlen($var) < 1) {
$newvar = NULL;
}
else {
$newvar = $var;
}

and:

INSERT INTO `table` (`var`) VALUES ('$newvar')

...alyways writes an empty value into MySQL-Table, but not NULL! Of course: The field "var" is declared to allow "NULL" in MySQL.

@AlanMachado one of them actually answers the question but not everyone sees it right away. — x13
– x13, Commented Oct 15, 2015 at 11:56

Community · Accepted Answer · 2017-05-23 12:22:28Z

1

With PDO you can use bindValue() method to prepared statement

bindValue(':param', null, PDO::PARAM_INT);

Here you can find how to do it in mysqli

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Oct 15, 2015 at 11:32

Robert

20.3k6 gold badges58 silver badges85 bronze badges

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

Comments

Your Common Sense · Accepted Answer · 2015-10-15 11:34:24Z

-3

You have to use prepared statements for all your database interactions in the first place. When used, they will deliver your NULL value to database all right, either with mysqli and PDO.

answered Oct 15, 2015 at 11:34

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Comments

Julio Soares · Accepted Answer · 2015-10-15 11:35:58Z

-3

Try with

"INSERT INTO `table` (`var`) VALUES (" . (is_null($newvar)) ? "NULL" : $newvar . ")";

answered Oct 15, 2015 at 11:35

Julio Soares

1,2809 silver badges11 bronze badges

6 Comments

Gogol Over a year ago

That's bad in so many ways.

Robert Over a year ago

"NULL" != NULL my friend

Julio Soares Over a year ago

and that's why it works my friend. It is there to be seen by the query parser not the php variable

x13 Over a year ago

@JulioSoares it took me a while to see that this actually works. Too bad that almost no-one can bring up the attention span to also see it. I recommend you approve my edit so that everyone can see that this actually works. Even though Robert was wrong, you should not revenge-downvote, ever.

x13 Over a year ago

@Robert the quotes are around the string NULL, these quotes won't appear in the query string. This answer actually is the most simple and correct.

|

Collectives™ on Stack Overflow

PHP: Set variable to NULL and write NULL to MySQL [duplicate]

3 Answers 3

Comments

Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

6 Comments

Linked

Related