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;
    }
}

Fabio · Accepted Answer · 2013-05-16 11:44:21Z

1

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.

answered May 16, 2013 at 11:44

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

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

Comments

Dharman · Accepted Answer · 2019-08-10 10:04:37Z

1

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.

answered Aug 10, 2019 at 10:04

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

Collectives™ on Stack Overflow

Insert NULL instead of empty values using MySQLi

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related