0

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.

Christoph
  • 123
  • 1
  • 3
  • 16

3 Answers3

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

Community
  • 1
  • 1
Robert
  • 19,800
  • 5
  • 55
  • 85
-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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
-3

Try with

"INSERT INTO `table` (`var`) VALUES (" . (is_null($newvar)) ? "NULL" : $newvar . ")";
Julio Soares
  • 1,200
  • 8
  • 11
  • 2
    That's bad in so many ways. – Gogol Oct 15 '15 at 11:36
  • "NULL" != NULL my friend – Robert Oct 15 '15 at 11:36
  • 2
    and that's why it works my friend. It is there to be seen by the query parser not the php variable – Julio Soares Oct 15 '15 at 11:36
  • @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 Oct 15 '15 at 11:52
  • @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. – x13 Oct 15 '15 at 11:55
  • I don't know how to see it and am getting very frustrated with the arrogance of people here. I don't like to say how people should or not write their code. mysql does not means unsafe as pdo does not means safe. People ask how to make something they already have to work, not to rewrite everything from the scratch. It takes time to people even get what is so different about mysql, pdo, mysqli and why they don;t want to get user input into query. But all that matters here seems to be the more professoral we can be the better – Julio Soares Oct 15 '15 at 11:58