null INT fields keep defaulting to 0

Anon

From the mysql manual, the best way to insert a null field is "INSERT INTO table VALUES (NULL)".

However, my programs can not do that. I need the user to enter in avg_compensation, so I have to use

mysql_query("INSERT INTO table VALUES ($avg_compensation)");

If they leave the field blank and submit, it isn't seen as null. So it automatically defaults to 0 since it is an INT field. I'm not using default=0 and I'm not using NOT NULL in my table.

Is there something I am doing wrong? Is there some other way to do this? The only way I can make it possible to insert a null is to do something like

if (!isset($avg_compensation)) { $avg_compensation="NULL";}
mysql_query("INSERT INTO table VALUES ($avg_compensation)");

The reason I don't consider this a good option is I have LOTS more fields than avg_compensation and I dont want to have to do an if statement on all of them.

Anon

Correction:

Doing INSERTS works ok as long as I keep the quotations out of the values.

For instance, VALUES('$avg_compensation') will result in a 0 but VALUES($avg_compensation) will work and insert a null.

However, I still have a problem with updates. Updates seem to produce an error if I don't use the quotations.

UPDATE table SET avg_compensation='$avg_compensation' will make it a 0. UPDATE table SET avg_compensation=$avg_compensation still produces a 0, if not an error.