CODEWITHSUNDEEP
phpmysql
Namek
Namek

Reputation: 487

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

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);

Upvotes: 0

Views: 1269

Answers (2)

BaBL86
BaBL86

Reputation: 2622

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());

Upvotes: 6

BlitZ
BlitZ

Reputation: 12168

  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.

Upvotes: 3

Related Questions