0

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);
Kiran RS
  • 981
  • 14
  • 31
Namek
  • 477
  • 1
  • 8
  • 20

2 Answers2

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());
BlitZ
  • 12,038
  • 3
  • 49
  • 68
BaBL86
  • 2,602
  • 1
  • 14
  • 13
3
  1. Clean up your data with escape function to prevent sql-injection;
  2. Predefine data BEFORE insertion into query - it is cleaner and more readable.
  3. 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.

Community
  • 1
  • 1
BlitZ
  • 12,038
  • 3
  • 49
  • 68