PHP sending "" instead of NULL to mySql

[david_r]

I am having an issue with populating values from a form into a mySql database. When the code attempts to send the insert query to the database, it returns the error,

ERROR: Incorrect decimal value for column 'purchase_price' at row 1

I think I can understand what is happening here. PHP is basically sending "" to the server as the value for purchase_price. The easiest solution would to simply use INSERT IGNORE or turn off strict mode on the server. However, I want the NULL values in the database and INSERT IGNORE inserts $0.00 for the purchase_price.

I could also just not send that variable to the server. However, there are many more variables than just these two. Which ones that will be entered by the user are unknown ahead of time so I don't know how to drop variables out of the insert INTO ... VALUES command on the fly.

When I go directly to the mySql command line, this query behaves exactly as would be expected:
insert into inv_property (address, purchase_price) VALUES( '456 Main St',NULL);
the value of purchase_price is NULL, not 0.00.

I thought I could simply unset or set to NULL any variable that is = "" but that doesn't seem to work either.

So, I guess I would like to be able to either pass "NULL" to the server or to remove the variables from the insert command on the fly.

$sql = "INSERT INTO $db_table(address, purchase_price) values 
('".mysql_real_escape_string(stripslashes( $_REQUEST['address']))."',
'".mysql_real_escape_string(stripslashes( $_REQUEST['purchase_price']))."')";

if($result = mysql_query($sql)) ..........

?>

[ballies]

try this

$sql = "INSERT INTO $db_table SET address = '" .mysql_real_escape_string(stripslashes( $_REQUEST['address'])). "'";
			
			/* if we don't have a new purchase_price via POST we insert the old one */
			if ( $_REQUEST['purchase_price'] != '' ):
				$sql .= ", purchase_price = '" .mysql_real_escape_string(stripslashes( $_REQUEST['purchase_price'])). "'";
			endif;

if($result = mysql_query($sql)) ..........

I am using "$_POST" instead of "$_REQUEST" so sorry if this does not help

[david_r]

Thank you ballies. That works. I'm going to file it away.

I decided to strip NULL value variables out os $_REQUEST so that I can dynamically build the insert query.