MySQL: Can't update existing NULL value

Question

I have a weird issue which I'm sure is because I don't understand all the idiosyncrasies of MySQL :(

I have a table with a column that has the default value of NULL, "TeamID". When I add a new row without giving a value for that column, it is NULL. Perfect. Except when I wish to update that row, the following code doesn't seem to change the value from NULL (or even cause any error):

    $STH = $this->_db->prepare("UPDATE UserDetails SET 
            TeamID = ':teamID' WHERE UserID = ':userID';");
    $STH->execute($params);

To restate the problem: I have a problem overwriting TeamID with a non-nullable value if it's already NULL. I can't see where there's any error in the code itself, so I'm imagining it's something to do with the NULL value.

One problem coding with PHP/MySQL is that you can't step through your code and look at the contents of the database at the same time -- because PHPMyAdmin gets stepped through, too.

Thanks for any help!

':teamID' => :teamID, lose the quotes, around every single placeholder, doesn't matter which type. — Wrikken
– Wrikken, Commented Feb 29, 2012 at 18:01
PDO does some emulation in some cases, I usually disable that, might have something to do with it (mind you, what if I want to store the string :var in a field? ':var' should insert the literal :var, not go looking for a parameter :var). Casting-wise this would be: ':var' (enter value=>) 'NULL' (cast to int =>) 0 And @Farray was a bit earlier with the answer, although maybe a little less explicit/clear. — Wrikken
– Wrikken, Commented Feb 29, 2012 at 18:09

Farray · Accepted Answer · 2012-02-29 17:57:16Z

2

Part of your problem may lie in using quote marks around the parameters. Since you're using a PDO prepared statement with params, you don't have to quote your strings. I'm not sure how PDO represents null, but you might end up in a situation where you try to set SET TeamID = '' or SET TeamID = 'null' and, depending on the column type, that may not be valid.

answered Feb 29, 2012 at 17:57

Farray

8,5883 gold badges35 silver badges37 bronze badges

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

Comments

Marcus Adams · Accepted Answer · 2012-02-29 18:04:53Z

1

Don't put quotes around named parameters with PDO:

$STH = $this->_db->prepare("UPDATE UserDetails SET 
            TeamID = ':teamID' WHERE UserID = ':userID';");
$STH->execute($params);

UserID will never equal the string ':userID', so nothing gets updated.

To ensure that your parameters are interpreted, remove the quotes, like this:

$STH = $this->_db->prepare("UPDATE UserDetails SET 
            TeamID = :teamID WHERE UserID = :userID");
$STH->execute($params);

answered Feb 29, 2012 at 18:04

Marcus Adams

54k9 gold badges98 silver badges146 bronze badges

1 Comment

Farray Over a year ago

MySql (in its default config?) will equate 1 with '1', so UserID = '12345' would return the appropriate record. Same thing goes for setting. You can UPDATE...SET col = '1' even if col's type is int. However, you'd get an error with UPDATE...SET col = '' on the same column.

Collectives™ on Stack Overflow

MySQL: Can't update existing NULL value

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related