I'm trying to update a specific column to null but it is updated to none, Why?-CodePudding

<?php
$connection = ...;

$id = ...;
$name = null;

mysqli_query($connection, "UPDATE USERS SET NAME = '$name' WHERE ID = '$id'");

After running this command the NAME column in the database will be empty (such as like this ""), But it must be a null value, Why not change it to null?

NAME column is CHAR

CodePudding user response：

You are passing the PHP variable value into the SQL query which is a string. Casting null to a string results in an empty string.

Your SQL query then has ' around the value, meaning the final SQL query looks like:

UPDATE USERS SET NAME = '' WHERE ID = 42

By fixing your query you should also fix your security: your code is vulnerable to SQL injections and you should use prepared statements.

CodePudding user response：

In case your name gets updated from null to a string, try using the below code:

<?php
$connection = ...;

$id = ...;
$name = null;

$sql = "UPDATE USERS SET NAME = '$name' WHERE ID = '$id'";
if ($name == NULL) {
    $sql = "UPDATE USERS SET NAME = NULL WHERE ID = '$id'";
}

mysqli_query($connection, $sql);