Posting NULL to MySQL Database instead of blank

[bschaffnerunr]

I am trying to get my php page to write to (and update, depending on the page) a row of information in my database.  The problem I am running into is that the row can contain 3 various size/price combinations, but some are optional, and thus need to have NULL values in the db.  In my error checking on the main page, one size/price is required, the other two are optional, but if a size/price is entered, all info must be filled out accordingly (i.e. Not allowed to only have Size 2, but must also have Price 2, & can't have Price 3/Size 3 without Price 2/Size 2, etc.).

 

Everything works fine there, but the problem is that my mysql_query() function is not appropriately writing NULLs to the database.  I have set up the database to allow for null values on those 4 fields (artworkSize2, artworkPrice2, artworkSize3, artworkPrice3) and to have default values of NULL, so that is set up correctly.  The code I have been trying to use is as follows:

$artworkSize2 = !empty($artworkSize2) ? "'$artworkSize2'" : NULL;
$artworkPrice2 = !empty($artworkPrice2) ? "'$artworkPrice2'" : "";
$artworkSize3 = !empty($artworkSize3) ? "'$artworkSize3'" : NULL;
$artworkPrice3 = !empty($artworkPrice3) ? "'$artworkPrice3'" : "";

I have tried various combinations of this code (setting all to NULL, then "NULL", then "'NULL'", then "", then '', then "''", etc.) and even other constructs to achieve the same thing:

if (!$artworkSize2 || $artworkSize2 == "") {
$artworkSize2 = NULL;
}
etc...

No matter what I do, the database (accessed through PHPMyAdmin) keeps posting blank values for the Sizes (varchar255) and 0.00 for the prices (decimal(10,2)).  The only way I can get the db to store a NULL value is if I manually go into PHPMyAdmin, into the database, go to the table, edit the row, then manually check the null value for each record which is time consuming and not at all feasible for the administration of the site.  Any ideas as to why this is not posting as it should?

 

NOTE: The blanks being posted to the db are just that.  It is not any blank spaces in it, just a blank value that is not a NULL.  A screenshot of part of the data in the db can be found below.  The top row shows what NULLs look like when I manually assign it, but after using the form on my site, it is stored like the bottom one (ID=13).

 

[kicken]

When you build your SQL query you need to make sure you use the keyword NULL as the value for the column. ie:

 

$val = !empty($val)?"'".mysql_real_escape_string($val)."'":'NULL';

$sql = 'INSERT INTO table (val) VALUES ('.$val.')';

That way if the value is blank, you end up with a query that looks like: INSERT INTO table (val) VALUES (NULL)

[bschaffnerunr]

How would you use this in an update statement?  Here is what I have so far with your recommendations.  Still getting the same results (storing blanks in the db upon update instead of 'NULL').

	$artworkSize2 = !empty($artworkSize2)?"'".$artworkSize2."'":NULL;
	$artworkSize3 = !empty($artworkSize3)?"'".$artworkSize3."'":NULL;
	$artworkPrice2 = !empty($artworkPrice2)?"'".$artworkPrice2."'":NULL;
	$artworkPrice3 = !empty($artworkPrice3)?"'".$artworkPrice3."'":NULL;
	// Add this piece into the database now
	$sql = mysql_query("UPDATE tblArt SET artworkTitle='$artworkTitle', artworkMedium='$artworkMedium', artworkSize1='$artworkSize1', artworkPrice1='$artworkPrice1', artworkSize2='$artworkSize2', artworkPrice2='$artworkPrice2', artworkSize3='$artworkSize3', artworkPrice3='$artworkPrice3', artistID='$artistID', isSold='$isSold', soldOnline='$soldOnline', artworkNotes='$artworkNotes' WHERE artworkID='$pid'") or die (mysql_error());
    	header("location:artwork_list.php");
	exit();

[trq]

$artworkSize2 = !empty($artworkSize2)? "'$artworkSize2'" : 'NULL';

 

You then need to remove all the quotes surrounding your variables within your query.