Want to insert blank, not zero for integer data type

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nathanofearth
Forum Newbie
Posts: 2
Joined: Thu Dec 23, 2010 5:38 pm

Want to insert blank, not zero for integer data type

Post by nathanofearth »

I have a form with many integer values which are being sent to a db. These rows are set up as a smallint type, with a default of Null and 'Yes' for Null Allowed. The problem I have is that when the form is submitted, any of the fields without a value are being stored as '0', but I just want them to be blank.

I'm using a conditional like the following:
$torque_2 = empty($_REQUEST[Vapor_2]) ? "Null" : $database->mysqlProtect($_REQUEST[Vapor_2]);

I have this in my update statement
Vapor_2 = '$torque_2',

When I look at the debug log, the following line is being executed for the update statement:
Vapor_2 = 'NULL',

But it is still storing a 0 in the db, not a blank entry. All the fields work fine when integers are entered. The only problem I'm having is that the blank rows are turning into zeros.

Any assistance is greatly appreciated.

Update:
I have also tried the statement below, trying to remove the quotes around Null. I'm still getting a zero in the db.
$torque_2 = trim(empty($_REQUEST[Vapor_2]) ? "Null" : $database->mysqlProtect($_REQUEST[Vapor_2]), "'");
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Want to insert blank, not zero for integer data type

Post by califdon »

I'm surprised you aren't getting type mismatch errors from MySQL. You must not have quotes around Null, because that would make it a string. Likewise, if you use Trim(), it will output a string, even an empty string, I believe. In other words you need to have either an INT value or a Null.

I'm puzzled by your 2 different ways of referring to the $_REQUEST array value, one using the $database class. You don't need to do that.

Also, it's generally not good practice to use $_REQUEST at all. Ordinarily you should use either $_POST or $_GET, as appropriate. It makes the script more readable to humans and there can be some security issues, I believe.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Want to insert blank, not zero for integer data type

Post by Darhazer »

califdon wrote:I'm surprised you aren't getting type mismatch errors from MySQL.
Unless MySQL is not in strict mode, only a warning is issued, and in PHP you have manually to check for any warnings :)
nathanofearth
Forum Newbie
Posts: 2
Joined: Thu Dec 23, 2010 5:38 pm

Re: Want to insert blank, not zero for integer data type

Post by nathanofearth »

We found a solution.

If the value is supposed to be blank, we set the variable equal to the entire line for the update statement, that way NULL doesn't have quotes. Works fine, now blanks in db instead of zero.

$torque_2 = "Vapor2 = NULL,";
Post Reply