MySQL and PHP - Insert Null Rather Than Empty String

MySQL and PHP - insert NULL rather than empty string

To pass a NULL to MySQL, you do just that.

INSERT INTO table (field,field2) VALUES (NULL,3)

So, in your code, check if $intLat, $intLng are empty, if they are, use NULL instead of '$intLat' or '$intLng'.

$intLat = !empty($intLat) ? "'$intLat'" : "NULL";
$intLng = !empty($intLng) ? "'$intLng'" : "NULL";

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
          VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', 
                  $intLat, $intLng)";

PHP/mySQL INSERT NULL if variable is empty

If you want to insert a NULL value into MySQL, you have to pass a null-value in the SQL query, not the string of null. It will still be a string from a PHP perspective, but not from the MySQL perspective.

if (!empty($_POST['comment'])) {
    $comment = "'".$mysqli->real_escape_string($_POST['comment'])."'";
} else {
    $comment = "NULL";
}

You can also shorten that into a one-liner, using a ternary operator

$comment = !empty($_POST['comment']) ? "'".$mysqli->real_escape_string($_POST['comment'])."'" : "NULL";

Then, because you assign the quotes around the comment-string itself, as you should do, since you alternatively want to pass a null-value, you need to remove the single quotes surrounding the variable from the query. This would otherwise break it too, as you'd get ''comment''.

$sql = "INSERT INTO table (comment) VALUES (".$comment.")";

Of course this assumes that the column comment allows for null-values. Otherwise it will fail, in which case you should either insert empty strings or change the column to accept null values.

---

It should also be noted that this query is exposed to SQL injection attacks, and you should use an API that supports prepared statements - such as PDO or MySQLi, and utilize those to secure your database against these kinds of attacks. Using a prepared statement with MySQLi would look something like this. See how we supply a PHP null to the value in bind_param() if $_POST['comment'] is empty.

// Set MySQLi to throw exceptions on errors, thereby removing the need to individually check every query
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

if (isset($_POST['folge'])) {
    // $comment = !empty($_POST['comment']) ? $_POST['comment'] : null; // Ternary operator
    $comment = $_POST['comment'] ?? null; // Null coalescing operator, since PHP 7

    $sql = "INSERT INTO eventstest (comment) VALUES (?)";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param("s", $comment);
    $stmt->execute();
    $stmt->close();
}

• PHP Ternary Operator
• How can I prevent SQL injection in PHP?

Make a value into NULL instead of empty in php

It's a normal problem with null-able strings. For a quick solution, you can change your code to:

$code = NULL;
if( !empty($_FILES['logo']['name']) ){
 ...
}

For more details, you can check this question:
MySQL and PHP - insert NULL rather than empty string

Insert NULL instead of empty string with PDO

If you want null, just insert null instead of empty string.

$stmt2->bindValue(':title', $title === '' ? null : $title, PDO::PARAM_STR);

MySQL, better to insert NULL or empty string?

By using NULL you can distinguish between "put no data" and "put empty data".

Some more differences:

• A LENGTH of NULL is NULL, a LENGTH of an empty string is 0.

• NULLs are sorted before the empty strings.

• COUNT(message) will count empty strings but not NULLs

• You can search for an empty string using a bound variable but not for a NULL. This query:

  SELECT  *
  FROM    mytable 
  WHERE   mytext = ?
  

  will never match a NULL in mytext, whatever value you pass from the client. To match NULLs, you'll have to use other query:

  SELECT  *
  FROM    mytable 
  WHERE   mytext IS NULL
  

---

Related Topics