mysql/PHP: updating empty int field inserts value of 0

Question

I have run into a quirk in MYSQL that is proving to be very vexing.

I am updating an existing record using a sql statement. All the updates work fine except when it gets to zip code, if the value for the zip is empty, it inserts a 0. If the field does have a value it inserts the correct value without any problem.

The datatype for the zip field is int(11).

Here is sql statement in query:

$sql= "UPDATE memberinfo SET firstname = '$firstname', lastname = '$lastname', zipcode='$zipcode', emailadr='$emailadr' WHERE memberid= '$memberid'";

When you echo that out before updating if zipcode is empty, it shows zipcode = '',

The 0 is getting inserted by MYSQL in response to something or another.

I tried removing the apostrophes around the zip value and MYSQL threw an error so that is not a fix.

Has anyone run across this or can you suggest a solution?

Thanks for any suggestions.

Answer 1

$sql= "UPDATE memberinfo SET firstname = '$firstname', lastname = '$lastname', zipcode=" . (!$zipcode ? 'NULL' : (int)$zipcode) . ", emailadr='$emailadr' WHERE memberid= '$memberid'";

In this case - if $zipcode is empty (empty string or 0) - NULL will be inserted, and the actual value otherwise

PS: make sure your zipcode field is NULLable

PPS: now you get 0 because mysql casts empty string to integer and that's 0