Jump to content

zero updating as NULL


bravo14

Recommended Posts

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

Link to post
Share on other sites

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)
Link to post
Share on other sites

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.

Link to post
Share on other sites

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 |
+----+--------+--------+
Link to post
Share on other sites

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?

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.