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]), "'");
Want to insert blank, not zero for integer data type
Moderator: General Moderators
-
nathanofearth
- Forum Newbie
- Posts: 2
- Joined: Thu Dec 23, 2010 5:38 pm
Re: Want to insert blank, not zero for integer data type
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.
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.
Re: Want to insert blank, not zero for integer data type
Unless MySQL is not in strict mode, only a warning is issued, and in PHP you have manually to check for any warningscalifdon wrote:I'm surprised you aren't getting type mismatch errors from MySQL.
-
nathanofearth
- Forum Newbie
- Posts: 2
- Joined: Thu Dec 23, 2010 5:38 pm
Re: Want to insert blank, not zero for integer data type
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,";
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,";