[RESOLVED] Updating float column to NULL stores 0

HeidiR

I have a mysql float column, which is optional and is defined as "float default NULL" in mysql. In php, I have an update query that updates the DB from form fields. If the float field value on the form is deleted(empty), mysql stores the float column as 0 (zero) in the DB.
I have tried setting the value in the update statement to NULL (php) and the empty string "". However, both produce the same result, which is a 0 in the DB.

What am I missing???

bradgrafelman

Can you show us the PHP code you're using that sets these NULL values? It sounds like you aren't handling these NULL values properly, e.g. if you have a NULL value in PHP, you actually have to tell MySQL to set the appropriate column to NULL.

HeidiR

Thank for the info. A snip-it of the code is below:

$q = "UPDATE cg_tbl SET cg_amount='" . $_POST['cg_amount'] . '" WHERE cg_num='$key'";

$rc=mysql_query($q);

$POST['cg_amount'] is the form field, which is defined as "float default NULL" in mysql. When $POST['cg_amount'] contains a value, the DB is updated correctly. When the value of $_POST['cg_amount'] is empty (NULL), mysql stores the float column as 0 (zero) in the DB.
I tried putting an if statement before the UPDATE statement to ensure I was passing mysql a NULL as follows, but had the same result. Zero in the DB.

if(empty($_POST['cg_amount'])) 
   $amt=NULL;
else
   $amt=$_POST['cg_amount'];

$q = "UPDATE cg_tbl SET cg_amount='" . $amt . '" WHERE cg_num='$key'";

$rc=mysql_query($q);

Googling around, it appears that others have run across the same issue, but I haven't yet come across a good solution other then not displaying the 0 value back to the form.

bradgrafelman

Then problem is that the PHP variable $amt contains the NULL value.

Look at what the resulting query is going to be:

UPDATE cg_tbl SET cg_amount='' WHRE cg_num='123'

You're not setting cg_amount equal to a NULL value, you're setting it equal to an empty string (something entirely different). Since float columns can't contain strings, the empty string is converted into a number - namely zero.

One problem is that you're using quotes for the values of numeric columns. The string '123.45' is not the same as the number 123.45!

As I said before, what you need to do is instead of setting the PHP variable equal to a NULL value, you should set it equal to the string "NULL". That would make your query look like:

UPDATE cg_tbl SET cg_amount=NULL WHRE cg_num=123

(notice that the erroneous quotes have been removed and that the values now make sense).

HeidiR

Thank for the suggestion. I changed the code as follow:

if(empty($_POST['cg_amount'])) 
   $amt="NULL"; 
else 
   $amt=$_POST['cg_amount']; 

$q = "UPDATE cg_tbl SET cg_amount=" . $amt . ' WHERE cg_num='$key'"; 

$rc=mysql_query($q);

However, when the value of $_POST['cg_amount'] is empty (NULL), mysql stores the float column as 0 (zero) in the DB as before. I'm still missing something.

bradgrafelman

Can you echo out the SQL query that causes the DB to store 0 instead of a NULL value and show it to us?

Also, note that user-supplied data should never be placed directly into a SQL query string, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you mus tfirst sanitize the data with a function such as [man]mysql_real_escape_string/man (for string data) or casting values to the appropriate type (for numeric values).

HeidiR

Here is the echoed query string and code:

UPDATE cg_tbl SET cg_amount='null' WHERE cg_num='4c278975c454820110214160813'

if(empty($_POST['cg_amount'])) 
   $amt="NULL"; 
else 
   $amt=$_POST['cg_amount']; 

$q = "UPDATE cg_tbl SET cg_amount=" . $amt . ' WHERE cg_num='$key'"; 

$rc=mysql_query($q);

bradgrafelman

I highly doubt that is the code you're using because your SQL query string still has quotes around the float value.

HeidiR

Removing php from the equation, I pasted the following sql command into phpmyadmin.

UPDATE cg_tbl SET cg_amount='null' WHERE cg_num='4c278975c454820110214160813'

The result was a 0 in the float column cg_amount.

bradgrafelman

Again, that's because you have quotes around what is supposed to be a numeric value. This is incorrect.

Example above still applies; '123.45' is a string and not a number, and 'null' is a string as well and not the NULL value.

HeidiR

Got it. Thanks.

bradgrafelman

Glad I could help; don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu above.