Cannot set NULL with PDO

Question

I know there are other posts on this topic but they don't answer the basic question. The previous answers all say to use PDO::PARAM_NULL but doesn't that remove the entire reason for using PDO anyway?

Here is my code:

    $sql = "INSERT INTO users_address (uid,street1,street2,city,state,region,postalcode,country) VALUES (?,?,?,?,?,?,?,?)";
    $stmt = $this->db->conn_id->prepare($sql);
    $stmt->bindParam(1, $uid, PDO::PARAM_INT);
    $stmt->bindParam(2, $street1, PDO::PARAM_STR);
    $stmt->bindParam(3, $street2, PDO::PARAM_STR);
    $stmt->bindParam(4, $city, PDO::PARAM_STR);
    $stmt->bindParam(5, $state, PDO::PARAM_STR);
    $stmt->bindParam(6, $region, PDO::PARAM_STR);
    $stmt->bindParam(7, $postalcode, PDO::PARAM_STR);
    $stmt->bindParam(8, $country, PDO::PARAM_STR);
    $stmt->execute();

Not all of these values are required so sometimes they are null. Both varchar and int database fields have default=null. How can I prepare this so that if a variable happens to be null, the null gets inserted? All of the other answers I have seen pass NULL for the second parameter but how do I know at run time it is a null?

To be really clear. I do not want to implicitly insert a null. I want to insert a variable which MAY be a null if the user did not complete the form. Not all fields are required on the form. For example, in this example, $street2.

What exactly are you trying to insert (i.e., what do `$street1`, `$city`... contain?) and what do you get instead? — Álvaro González, Sep 29 '14 at 14:22
possible duplicate of [How do I insert NULL values using PDO?](http://stackoverflow.com/questions/1391777/how-do-i-insert-null-values-using-pdo) — Daniel W., Sep 29 '14 at 14:24
Not a duplicate Dan and I explained why. :) The fields are coming from a form that was posted. Not all fields are required so some may be null. I get an error saying cannot process nulls. — Doug Wolfgram, Sep 29 '14 at 14:26

Álvaro González · Accepted Answer · 2014-09-29T15:57:03.463

4

In SQL, or at least standard SQL and most SQL flavours —yeah, I'm looking at you, Oracle— NULL is a special value that is completely different from empty strings. In SQL code you use the NULL keyword to represent the former and empty quotes for the latter.

Your input comes from HTML forms. HTML forms are always submitted as plain text: you don't have anything else, no numbers, no dates... and of course no nulls. Whenever you need an actual NULL, you need some post-processing. Happily, PDO will convert PHP nulls to SQL nulls:

$stmt->bindValue(6, $region!='' ? $region : null, PDO::PARAM_STR);

PDO (and SQL) are just unaware of HTML and won't do it for you.

I've also replaced your bindParam() with bindValue() to make it all less verbose.

edited Sep 29 '14 at 15:57

answered Sep 29 '14 at 14:34

Álvaro González

142,137
41
261
360

I did this and even set $mynull = null and passed it as $mynull, but get this warning: "Only variables should be passed by reference." it is only a warning, but I'd prefer cleaner code with no warnings. Do I need to do all the assignments outside the bind? – Doug Wolfgram Sep 29 '14 at 15:49
1

Yes, you are using bindParam() so you need to pass an actual variable. Perhaps you just want to use bindValue(). – Álvaro González Sep 29 '14 at 15:52

score 2 · Answer 2 · answered Sep 29 '14 at 14:28

2

Use PARAM_NULL to insert a null value or the other params you already have:

if (!isset($uid) || strlen($uid) == 0) {
    $stmt->bindParam(1, null, PDO::PARAM_NULL);
} else {
    $stmt->bindParam(1, $uid, PDO::PARAM_INT);
}

answered Sep 29 '14 at 14:28

Daniel W.

31,164
13
93
151

Could I just use a ternary operator on the variable line? I suppose that would work. $stmt->bindParam(1, ($uid) ? $uid : null, PDO::PARAM_INT); – Doug Wolfgram Sep 29 '14 at 14:30
I think that should work. I've read that `PARAM_INT` with null works. – Daniel W. Sep 29 '14 at 14:31
Why don't you insert an empty string instead of null when no information is given? – Daniel W. Sep 29 '14 at 14:34
1

Because empty strings are not allowed in integers and I don't want a 0. – Doug Wolfgram Sep 29 '14 at 15:34
Ok that's right :D When I read your question I thought you can't pass `null` to `PARAM_STR` but you should be able to, like Alvaro answered. – Daniel W. Sep 29 '14 at 16:06

Cannot set NULL with PDO

2 Answers2