PHP How to insert NULL in Database? [duplicate]

Question

I have a normal HTML-File that give a string via. POST to my PHP-file wich will put this to a MySQL-database.

How do I achieve that I can write a "real" NULL in the database and not " " (a empty string) or something like that?

The MySQL column is nullable.

My form:

<form method="post" action="putInDatabase.php">

    <label>Text</label>
    <textarea name="text" ></textarea>

    <label>Picture URL (optional)</label>
    <input name="image" />
    <br>

    <input id="submit" name="submit" type="submit" value="submit">

</form>

My PHP-File:

<?php

  $text = "";
  $image = null;

  if (isset($_POST["submit"]))
  {
    $text = $_POST["text"];

    $image = $_POST["image"];
  }

  $text = strtr ($text, array ('"' => '\"'));


  $con = mysql_connect("censored :)");

  if (!$con)
  {
    die('ERROR' . mysql_error());
  }
  mysql_select_db("_DATABASE_HERE_", $con);


  $insertSQL = "INSERT INTO `_DATABASE_HERE_`.`_NAME_HERE_` (`Text`, `PictureURL`) VALUES ('$text', '$image ');";  

  $res = mysql_query($insertSQL); 
  $res = mysql_query($sql);

  mysql_close($con);

  echo "Success!";

?>

first you need to make sure that the column is set as nullable ,if it does you can just write NULL on the insert statement. — Gal Sisso
– Gal Sisso, Commented Feb 6, 2015 at 16:07
you need to switch to PDO or MySQLi prepared statements!!! — cmorrissey
– cmorrissey, Commented Feb 6, 2015 at 16:08
Instead of using the mysql functions, use PDO with prepared statements. This article will show you all you need to know how to do it the right way from the start: stackoverflow.com/questions/60174/… — Andy Lester
– Andy Lester, Commented Feb 6, 2015 at 16:08
@Fred-ii- No, I add a URL in my "Image" form element. But I want to set it NULL if the user don't write anything in that element. — DominikTV
– DominikTV, Commented Feb 6, 2015 at 16:09

Marc B · Accepted Answer · 2015-02-06 16:18:38Z

2

You'd need to pre-process your text. And since you're vulnerable to sql injection attacks this should be considered MANDATORY:

if (isset($_POST['text']) && !empty($_POST['text'])) {
   $safetext = "'" . mysql_real_escape_string($_POST['text']) . "'";
} else {
   $safetext = 'null';
}

$sql = "INSERT ... VALUES ($safetext, ...)";

Note how the quotes are added inside the if(). If there's some text to be done, the sql-escaped text is surrounded by quotes. if there's no text at all, then the string null is added in. This boils down to the difference between null and 'null'.

null is an sql null, "unknown value". 'null' is a string with the literal characters n, u, l, and l in it. In SQL terms, they're two completely different things.

The above code would produce

INSERT ... VALUES (null, ...)
INSERT ... VALUES ('Miles O\'Brien', ...)

edited Feb 6, 2015 at 16:18

answered Feb 6, 2015 at 16:09

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

1 Comment

DominikTV Over a year ago

I want the sql null :)

Pawan Kartik · Accepted Answer · 2015-02-06 16:19:42Z

0

Try this:

$variable_name = "NULL";

Now, while inserting into the database, use $variable_name.

That should do it.

EDIT: You need to either use prepare statements, if you are gonna switch to PDOs in future or escape the user input.

answered Feb 6, 2015 at 16:19

Pawan Kartik

342 bronze badges

Collectives™ on Stack Overflow

PHP How to insert NULL in Database? [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related