note 41620 deleted from function.is-null by tomsommer

Note Submitter: David_MySql -AT- inglesisimo -DOT- -COM- ---- This note is about entering NULL values into MySQL from PHP. Have you ever tried to add NULL values to a table, only to have them stored as 'NULL' (string) or '0' (number)??? Here is one solution to your problem. First of all, the variables I am using have been retrieved from an HTML form using $_POST['some-value']. They have already been trimmed and validated. I have turned any blank POST values into NULL values during the validation process. After successfully connecting to MySQL, if you make the following query, NULL values will appear as 'NULL' (string) or '0' (number), instead of NULL (actually NULL!): ------------- code ---------------- $Add_New_Student_To_Database = "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')"; if ($Query_Result = mysql_query("$Add_New_Student_To_Database")) < if-else code snipped; not relevant here> ------------- code ---------------- So how do you actually enter a real NULL value into MySQL? The answer: // single quotes inside double quotes ($variable === NULL) ? NULL : "'$variable'"; or // Escaped single quotes inside single quotes ($variable === NULL) ? NULL : '\'$variable\''; This reads: If $variable is NULL, then leave it NULL. If not, put a single quote before and after its current string value. After doing this, remove all of the single quotes from your query (except for the first blank pair, which lets MySQL make the next auto-increment number in the Student_ID column). This will mean that NULL values are entered as NULL and string values are entered as 'string'. Problem solved. I know it is annoying to have to include single quotes within the variables' values, but you can only enter a NULL value *without* quoting it (as far as I know!) In my program, I can't know if a student will leave a form field blank, so I can't know when to anticipate a NULL value. In my program, I am directly manipulating $_POST[''] variables: $MiddleName = ($_POST['MiddleName'] == '') ? 'NULL' : "'".trim($_POST['MiddleName'])."'"; This reads: If MiddleName from the from is blank, make it NULL, if not, trim the leading and trailing whitespace from the string value and add single quotes before and after the trimmed value. MiddleName is then ready, NULL or not, for entry into the modified (no single quotes) version of the Insert query above. For security, you could also validate the value by doing some regular expressions to check that the original MiddleName string is a real name, and that it doesn't contain, numbers, backticks, slashes, colons, semi-colons, quotes etc... (Look for SQL Injection in Google if you want to learnhow to avoid MySQL SQL injection hacks. Nasty stuff!) I hope this helps someone. It took me a few days to figure this out! -- David.