7

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?

Dharman
  • 26,923
  • 21
  • 73
  • 125
Alpaus
  • 636
  • 1
  • 6
  • 20
  • See also: [Insert NULL instead of empty values using MySQLi](https://stackoverflow.com/q/16586255/1839439) – Dharman Aug 10 '19 at 16:50

2 Answers2

8

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

$stmt2->bindParam(':title', $title === '' ? null : $title, PDO::PARAM_STR);
xdazz
  • 154,648
  • 35
  • 237
  • 264
  • Thank you, this was the obvious staring me in the face (can't see the forest for the trees....) – Alpaus Feb 03 '12 at 05:18
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(?, '')).

Tim Landscheidt
  • 1,330
  • 1
  • 14
  • 20