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 comment
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 comment
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 comment
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 comment
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 comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.