Description:
------------
Hi,

I am making a PHP-MySQL application to store details about my students (I'm an English teacher). I have an HTML form POST to PHP (4.3.4) and then I validate the $_POST values and enter them into a students table in a MySQL (4.0.18-nt) database.

My specific problem is with entering NULL values. I have a workaround solution, but it is annoying and I hope the PHP developers could provide a better/faster way of entering null values. So this could be an unintended bug or a feature request...

Before I post the actual code, I will mention that the idea here is to receive the students' names, phone numbers, etc, from the form, check them, and enter them into the database. All values can be left blank, except for Status, FirstName, and LastName1. My code checks the values, and if they are blank (e.g. $_POST['postcode'] == ''), it makes the value NULL. If there is some data, the value is trimmed, validated, etc. I need these NULL values, as opposed to zero values or empty strings, to simplify the search functions I have yet to write...

My workaround code:


Reproduce code:
---------------
HTML form POSTs to PHP:

---<snip>---
// receiving PHP file

$Colony = ($_POST['Colony'] == '') ? 'NULL' : "'".trim($_POST['Colony'])."'";
$Delegation = ($_POST['Delegation'] == '') ? 'NULL' : "'".trim($_POST['Delegation'])."'";
$City = ($_POST['City'] == '') ? 'NULL' : "'".trim($_POST['City'])."'";
$State = ($_POST['State'] == '') ? 'NULL' : "'".trim($_POST['State'])."'";
$PostCode = ($_POST['PostCode'] == '') ? 'NULL' : trim($_POST['PostCode']);

---<snip>---
// same file, after validation

if ($ErrorMessage == "") // validation code was snipped
		{			
		require_once('database-queries.php');
		Add_Student($Status, $FirstName, $MiddleName, $LastName1, $LastName2, $HomePhone, $WorkPhone, $Extension, $MobilePhone, $Street, $ExteriorNumber, $InteriorNumber, $Colony, $Delegation, $City, $State, $PostCode);						
		}

---<snip>---
//The actual insert statement made inside database-queries.php after connecting to the DB.

"Insert into students 
									   (Student_ID, Status, FirstName, MiddleName, LastName1, LastName2, HomePhone, WorkPhone, Extension, MobilePhone, Street, ExteriorNumber, InteriorNumber, Colony, Delegation, City, State, PostCode) 
									   values 
									   ('', $Status, $FirstName, $MiddleName, $LastName1, $LastName2, $HomePhone, $WorkPhone, $Extension, $MobilePhone, $Street, $ExteriorNumber, $InteriorNumber, $Colony, $Delegation, $City, $State, $PostCode)";

--<end of code snippets>--


Expected result:
----------------
As mentioned above, this code works as intended (due to a workaround I had to figue out), but is ugly. As you saw in the INSERT statement, I am substituting variables for their values, *WITHOUT* single quoting them. The variables themselves already contain single quotes if they contain values, and no quotes if they contain NULL or a number.

I had to do this because, if my string variables don't have quotes in them, writing...

Insert into table values ('$variable1', '$variable2', .....)

...doesn't work as intended. A value like $var1=NULL will be entered as 'NULL' in a varchar column, or '0' in a numerical column. This isn't what I want! I did a lot of tests using MySQL Control center to easily see the values that the database actually stored.

Actual result:
--------------
Ok so how can one enter NULL values into MySQL without prequoting string variables, and not NULL variables?

I know that writing...

($var === NULL) ? NULL : "'$var'";

or

($var === NULL) ? NULL : '\'$var\'';

...will work, but why do I have to pre-quote my string variables? Isn't there another way to enter a null value into an insert statement, without removing the normal single quotes from that insert statement?