From: David_php at inglesisimo dot com
Operating system: WinXP
PHP version: 4.3.4
PHP Bug Type: MySQL related
Bug description: Entering NULL values into MySQL from PHP.
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?
--
Edit bug report at http://bugs.php.net/?id=28028&edit=1
--
Try a CVS snapshot (php4): http://bugs.php.net/fix.php?id=28028&r=trysnapshot4
Try a CVS snapshot (php5): http://bugs.php.net/fix.php?id=28028&r=trysnapshot5
Fixed in CVS: http://bugs.php.net/fix.php?id=28028&r=fixedcvs
Fixed in release: http://bugs.php.net/fix.php?id=28028&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=28028&r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=28028&r=needscript
Try newer version: http://bugs.php.net/fix.php?id=28028&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=28028&r=support
Expected behavior: http://bugs.php.net/fix.php?id=28028&r=notwrong
Not enough info: http://bugs.php.net/fix.php?id=28028&r=notenoughinfo
Submitted twice: http://bugs.php.net/fix.php?id=28028&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=28028&r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=28028&r=php3
Daylight Savings: http://bugs.php.net/fix.php?id=28028&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=28028&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=28028&r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=28028&r=float
