zero updating as NULL

[bravo14]

I have a site that records a riders score, and occasionally I will need to record 0.  If I update with 0, the database shows NULL instead.

 

The snippets of code below show the declaration of the variable and then the adding to an array for an update query

 

 

if(empty($_POST['points'])){$points=NULL;} else {$points=$_POST['points'];}

 

 

$columns[] = "points = " . ($points === null ? "NULL" : "'$points'");

 

My question is how do I update 0 and not NULL

[jcbones]

Here is where your problem is:

empty()

 

 

Returns FALSE if var exists and has a non-empty, non-zero value. Otherwise returns TRUE.

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)

[jcbones]

Sorry, here is some ways to correct it.

 

 

1. Set a default value to the data column, of 0 of course.

 

2. In your columns array, instead of setting it to the word NULL, just set it to 0.

[Barand]

You could try NULLIF()

$db->query("DROP TABLE IF EXISTS bravo14");
$db->query("CREATE TABLE bravo14 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR(20),
points INT)");

$a = '';
$b = 0;
$c = 1;

$sql = "INSERT INTO bravo14 (name, points) VALUES
('aaaaaa', NULLIF('$a', '')),
('bbbbbb', NULLIF('$b', '')),
('cccccc', NULLIF('$c', ''))
";
$db->query($sql)

mysql> SELECT * FROM bravo14;
+----+--------+--------+
| id | name   | points |
+----+--------+--------+
|  1 | aaaaaa |   NULL |
|  2 | bbbbbb |      0 |
|  3 | cccccc |      1 |
+----+--------+--------+

[bravo14]

I have trued to use NULLIF() in the following way.

 

$points=NULLIF($_POST['points'], '');

I get the following error

 

Fatal error: Call to undefined function NULLIF() in /home/sites/starkeracing.co.uk/public_html/admin/edit-fixture.php on line 19

 

Any ideas?

 

[Barand]

NULLIF() is not a PHP function, it's a SQL function

 

You will notice I used it as a function inside the MySQL query

[adam_bray]

This should do what you need -  

$points = ( is_numeric($_POST['points']) && $_POST['points'] > 0 ) ? $_POST['points'] : 0;
$columns[] = "points = " . $points;

As said above, NULLIF() is a SQL function, you've tried to use it in PHP so the error tells you it doesn't exist.