Insert NULL instead of empty values using MySQLi

Question

I have a form with some optional fields. In the database those fields are set to accept NULL. The code below will throw an error if some field is empty. Could you please assist on what is the best way to avoid this? The only solution I was thinking of is to set the vars to ' ' if is empty().

$query = "INSERT INTO gifts (dateRequest, firstName, lastName, note, lastUpdated) 
    VALUES (?, ?, ?, ?, NOW())";
if ($stmt = $dbc->prepare($query)) {
    $dateRequest = $_POST['dateRequest'];
    $firstName = $_POST['firstName'];
    $lastName = $_POST['lastName'];
    $note = $_POST['note'];
    $stmt->bind_param('ssss', $dateRequest, $firstName, $lastName, $note);
    if ($stmt->execute()) {
        $stmt->close();
        header('Location: index.php');
    } else {
        echo $stmt->error;
    }
}

score 1 · Accepted Answer · answered May 16 '13 at 11:44

I would rather suggest to check $_POST paramenters before definied them so if a variable is not empty set values otherwise set as NULL

if(!empty($_POST['dateRequest'])) { $dateRequest = $_POST['dateRequest']; } else { $dateRequest = NULL; }
if(!empty($_POST['firstName'])) { $firstName = $_POST['firstName']; } else { $firstName  = NULL; }
if(!empty($_POST['lastName'])) { $lastName = $_POST['lastName']; } else { $lastName = NULL; }
if(!empty($_POST['lastName'])) { $note = $_POST['note']; } else { $note = NULL; }

This will prevent you to pass empty parameters in your query.

score 1 · Answer 2 · answered Aug 10 '19 at 10:04

Since PHP 7 you can set the default value for a variable using the elvis-operator.

$dateRequest = $_POST['dateRequest'] ?: null;
$firstName = $_POST['firstName'] ?: null;
$lastName = $_POST['lastName'] ?: null;
$note = $_POST['note'] ?: null;

If any of the fields is empty or undefined it will set the value to NULL and insert that into database instead.

As a side note you should read How to get the error message in MySQLi? instead of print out the error messages manually.

Insert NULL instead of empty values using MySQLi

2 Answers2

Linked