|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #28028 Entering NULL values into MySQL from PHP.
Submitted: 2004-04-16 16:31 UTC Modified: 2004-04-16 17:07 UTC
From: David_php at inglesisimo dot com Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.3.4 OS: WinXP
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
Solve the problem:
45 - 31 = ?
Subscribe to this entry?

 [2004-04-16 16:31 UTC] David_php at inglesisimo dot com

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:

// 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']);

// same file, after validation

if ($ErrorMessage == "") // validation code was snipped
		Add_Student($Status, $FirstName, $MiddleName, $LastName1, $LastName2, $HomePhone, $WorkPhone, $Extension, $MobilePhone, $Street, $ExteriorNumber, $InteriorNumber, $Colony, $Delegation, $City, $State, $PostCode);						

//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) 
									   ('', $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'";


($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?


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2004-04-16 17:07 UTC]
Sorry, but your problem does not imply a bug in PHP itself.  For a
list of more appropriate places to ask for help using PHP, please
visit as this bug system is not the
appropriate forum for asking support questions. 

Thank you for your interest in PHP.

This is not a support forum. And this is not a bug nor something that will ever be changed.

PHP Copyright © 2001-2023 The PHP Group
All rights reserved.
Last updated: Sun May 28 01:03:51 2023 UTC