PHP PDO inserts NULL instead of empty potsgresql

Question

I am using PHP PDO with the Postgresql backend. I have a table with fields with a null value, and when the user does not enter anything in the HTML form field, I want to insert NULL into this field, and not an empty string (this is important, since some of my SELECT in these tables later use such conditions like WHERE x NOT NULL).

However, this version of bindParam code inserts an empty string into a field with a null value instead of NULL.

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

I read quite a lot and realized that this is the answer to insert a zero in the field:

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

but this means that I need to pre-check all the parameters that are passed to fields with a zero value to determine whether they are empty, and if so pass PDO :: PARAM_NULL instead of PDO_PARAM_STR. Of course, I can do this, but I hoped there could be a parameter that would just say PDO if it encounters an empty string, insert a zero instead.

I came across this

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

but it has no effect with further research. I think this only affects the output record, not the path.

Any parameters other than pre-checking variables?

thanks Aaron

Answer 1

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

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

Answer 2

Besides the more appropriate xdazz answer, you can often solve something similar in SQL: just rephrase the INSERT INTO ... VALUES (?) Query with about INSERT INTO ... VALUES (NULLIF(?, '')) .