PHP/MySQL Insert Null Values

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?

How to insert NULL Value in PHP

When you concatenate the $managerId variable, you are getting this query string:

"INSERT into user (managerId) VALUES ('')"

So, you are trying to insert an empty string. This is not what you want. The easiest way is to remove the quotes, like

"INSERT into user (managerId) VALUES ($managerId)"

This will work if your managerId field can be null, and still be valid for integer values.

MYSQL and PHP INSERT NULL values

No, it will assign the value passed with the parameter $_GET['date']. If the value is empty '' and the date was of data type varchar it will insert a value of '' which is different than NULL then it will insert an empty. Thats because NULL and empty strings are not equal. They are two different things in SQL.

If you want to insert NULL values, either ignore this column in the insert columns list, then it will assigned with the default value which is NULL. Or write it explicitly in the values of the INSERT statement.

Note that: Your code this way is vulnerable to SQL injection. You should use prepared statements or PDO instead. See this for more details:

Best way to prevent SQL injection?

PHP/MySQL Insert null values

This is one example where using prepared statements really saves you some trouble.

In MySQL, in order to insert a null value, you must specify it at INSERT time or leave the field out which requires additional branching:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', NULL);

However, if you want to insert a value in that field, you must now branch your code to add the single quotes:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', 'String Value');

Prepared statements automatically do that for you. They know the difference between string(0) "" and null and write your query appropriately:

$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);

$field1 = "String Value";
$field2 = null;

$stmt->execute();

It escapes your fields for you, makes sure that you don't forget to bind a parameter. There is no reason to stay with the mysql extension. Use mysqli and it's prepared statements instead. You'll save yourself a world of pain.

Why can't I insert NULL value into column after MySQL upgrade?

Your local MySQL server is running in "strict" mode, which throws an error in case like yours, and several others that are otherwise handled "gracefully" without strict mode. As of MySQL 5.7.5, the default SQL mode includes STRICT_TRANS_TABLES, which is exactly the cause of your headache.

To change your local server's mode, edit your my.cnf (or my.ini if you're running Windows) and then set the mode to:

sql-mode=""

Or, upon connecting to MySQL in your web app, execute this query first right after the connection is estabilished:

SET GLOBAL sql_mode = '';

I'd advise against this because it lets you execute a bit careless statements. In the long run, you're better off with adjusting your schema instead.

Inserting null values to database using prepared statements PHP MYSQL

Your query is wrong:

if ($stmt = $mysqli->prepare("INSERT INTO login VALUES('',?,?,?,?,?,'')")) {

It should be something like:

if (!empty($name) || !empty($pass) || !empty($email))
    {
        $stmt = $mysqli->prepare("INSERT INTO login(`name`,`password`,`email`,`sex`,`city`) VALUES(?,?,?,?,?)");
        $stmt->execute([$name, $pass, $email, $sex, $city]);
        echo "result inserted";

    } else {
        echo 'You have not entered all of the fields.';
    }

In this instance, if the variables are not empty then perform insert. Else if they are empty fire a echo stating the fields haven't been filled in.

If you are happy for the fields to be null simply change !empty() to empty() but as Fred -ii- stated above, ensure your database allows NULL within them fields.

Unable to insert NULL in date field for MySQL

The default value for your column is NULL, so it doesn't have to be in your INSERT statement.

$query_string = "INSERT INTO inward_credit
     (custi_id_fk, driver_id_fk, today_date, amount, mode, cheque_no)
     VALUES
     ('$cust_id1', '$driver_id1', '$today_date', '$credit_collected', '$mode', '$cheque_number')";

     $insert_credit = mysqli_query($con, $query_string);

Edit

If you are wanting to insert NULL, use the following;

Don't quote the variable $chq_date, as that will make NULL a string. See Marc B's Answer

//if/else logic.
if(<<what>>) {
  $chq_date = "2014/09/09";
} else {
  $chq_date = "NULL";
}

$query_string = "INSERT INTO inward_credit
     (custi_id_fk, driver_id_fk, today_date, amount, mode, cheque_date, cheque_no)
     VALUES
     ('$cust_id1', '$driver_id1', '$today_date', '$credit_collected', '$mode', $chq_date, '$cheque_number')";

     $insert_credit = mysqli_query($con, $query_string);