PHP - MySQL trying to set value="0" - it's NULL

Question

I'm just trying to set some value = "0", but it's automatically convert into NULL.

$result_checkups = mysql_query("INSERT INTO TABLE1 (COL1, COL2) VALUES (".(!empty($COL1) ? "'$COL1'" : "NULL").", ".(!empty($COL2) ? "'$COL2'" : "NULL").") or die(mysql_error());

It's my code - it look so strange, becouse I need to set NULL, when someone trying to set empty string (""). But now, when COL2="0", then it's NULL behind MySQL (to clear, when COL2="1", it's "1" behind MySQL). Where is problem?

MySQL table:

CREATE TABLE `TABLE1` (
  `COL1` TIMESTAMP NULL DEFAULT NOW(),
  `COL2` TINYINT(1) NULL);

That's not even valid PHP and thus can't have the symptoms described. Try again. — user2864740
– user2864740, Commented Dec 31, 2013 at 9:31

BlitZ · Accepted Answer · 2014-01-01 13:40:41Z

6

Because of

php -r 'echo empty(0);'
1
php -r 'echo empty("0");'
1

0 is empty. Try to change your statement to:

$result_checkups = mysql_query("INSERT INTO TABLE1 (COL1, COL2) VALUES (".(($COL1==='') ?  "NULL" : "'$COL1'").", ".(($COL2==='') ? "NULL" : "'$COL2'").")") or die(mysql_error());

edited Jan 1, 2014 at 13:40

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

answered Dec 31, 2013 at 9:31

BaBL86

2,6301 gold badge16 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:49:40Z

Clean up your data with escape function to prevent sql-injection;
Predefine data BEFORE insertion into query - it is cleaner and more readable.
Read empty() function documentation:

The following things are considered to be empty:

"" (an empty string)

0 (0 as an integer)

0.0 (0 as a float)

"0" (0 as a string)

NULL

FALSE

array() (an empty array)

$var; (a variable declared, but without a value)

So "0" is considered empty. I suggest you to use strlen() instead.

Try this:

$COL1 = mysql_real_escape_string($COL1);
$COL2 = mysql_real_escape_string($COL2);

$COL1 = strlen($COL1) ? $COL1 : 'NULL';
$COL2 = strlen($COL2) ? $COL2 : 'NULL';

$sql  = "INSERT INTO TABLE1 (COL1, COL2) VALUES ({$COL1}, {$COL2})";

$result_checkups = mysql_query($sql) or die(mysql_error());

P.S.: MySQL extension is deprecated. Use MySQLi or PDO instead. Follow this link.

Collectives™ on Stack Overflow

PHP - MySQL trying to set value="0" - it's NULL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related