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?

See also: Insert NULL instead of empty values using MySQLi

Dharman
– Dharman ♦

2019-08-10 16:50:32 +00:00
Commented Aug 10, 2019 at 16:50 — Dharman
– Dharman ♦, Commented Aug 10, 2019 at 16:50

Your Common Sense · Accepted Answer · 2022-08-16 18:07:03Z

9

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

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

edited Aug 16, 2022 at 18:07

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Jan 29, 2012 at 7:07

xdazz

161k38 gold badges255 silver badges278 bronze badges

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

Comments

Tim Landscheidt · Accepted Answer · 2012-01-29 11:13:26Z

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(?, '')).

answered Jan 29, 2012 at 11:13

Tim Landscheidt

1,4001 gold badge17 silver badges20 bronze badges

Comments

quinz · Accepted Answer · 2022-08-16 17:46:28Z

-1

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

edited Aug 16, 2022 at 17:46

quinz

1,3384 gold badges22 silver badges34 bronze badges

answered Aug 10, 2022 at 2:22

Daniel Banegas duotics

1

Collectives™ on Stack Overflow

Insert NULL instead of empty string with PDO

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related