Insert NULL instead of empty string with PDO

Question

I have a table which has some nullable fields and when the user enters nothing into the HTML form field, I want to insert NULL into that field, not an empty string (this is important as some of my SELECTs on these tables later use conditions such as WHERE x IS NOT NULL).

However, this version of bindParam code inserts an empty string into the nullable field instead of NULL.

$stmt2->bindParam(':title', $title, PDO::PARAM_STR);

I've been reading quite a bit and figured out that this is the answer to insert null into the field instead:

$stmt2->bindParam(':title', $title, PDO::PARAM_NULL);

But this means I need to pre-check all parameters that are being passed to nullable fields to determine if they are empty, and if so pass the PDO::PARAM_NULL instead of PDO::PARAM_STR. I can of course do this, but was hoping there might be a setting which would just tell PDO if it encounters an empty string, insert null instead.

I stumbled across this

$this->dbh->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING);

But it has no effect and with further research I'm thinking this only affects record on the way out, not on the way in.

Any options other than pre-checking the variables?

Answer 1

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

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

Answer 2

Besides xdazz's more appropriate answer, you can often solve something like this in SQL: Just rephrase the query INSERT INTO ... VALUES (?) to something like INSERT INTO ... VALUES (NULLIF(?, '')).

Answer 3

This works really well. Another option is with

IF $val == null || $val == 'null' || $vall ==''
BindValue with PDO::PARAM_NULL
ELSE
BindValue with PDO::PARAM_STR