How to set column value as zero if value is empty using php/mysqli?

Question

When concatenating two columns(3,4).If first value is empty or null make zero(0,4) and if second value is empty or null make it zero like 3,0.How to do in sql query using php.

CONCAT_WS(',',IFNULL(a.PD_right, ''),IFNULL(a.PD_left, '')) as PD_RL

In the above code,concatenating PD_right and PD_left.

Now am getting like

if two values are empty it showing like (,) only comma.
If two values are there it will show like 3,4
if first value is empty it will show like ,4
if second value is empty it will show like 3,

How to fix these issues.?My database columns are blank.it iS not NULL or null

If I understand correctly, Simply replace empty string with '0' should work, CONCAT_WS(',',IFNULL(a.PD_right, '0'),IFNULL(a.PD_left, '0')) as PD_RL — Gasol
– Gasol, Commented Oct 25, 2018 at 16:56

Gasol · Accepted Answer · 2018-10-25 17:59:33Z

If I understand correctly, Simply replace empty string with '0' like CONCAT_WS(',',IFNULL(a.PD_right, '0'),IFNULL(a.PD_left, '0')) as PD_RL should works.

If you want detect either NULL or empty string '', Try IF function instead.

I did simple test on my machine, here is output:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.12 Homebrew

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost) [(none)]> SET @left = 3, @right = 4;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> SELECT CONCAT_WS(',', IF(@left IS NULL OR @left = '', '0', @left), IF(@right IS NULL OR @right = '', '0', @right)) as PD_RL;
+-------+
| PD_RL |
+-------+
| 3,4   |
+-------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> SET @left = 3, @right = NULL;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> SELECT CONCAT_WS(',', IF(@left IS NULL OR @left = '', '0', @left), IF(@right IS NULL OR @right = '', '0', @right)) as PD_RL;
+-------+
| PD_RL |
+-------+
| 3,0   |
+-------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> SET @left = NULL, @right = 4;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> SELECT CONCAT_WS(',', IF(@left IS NULL OR @left = '', '0', @left), IF(@right IS NULL OR @right = '', '0', @right)) as PD_RL;
+-------+
| PD_RL |
+-------+
| 0,4   |
+-------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> SET @left = NULL, @right = NULL;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> SELECT CONCAT_WS(',', IF(@left IS NULL OR @left = '', '0', @left), IF(@right IS NULL OR @right = '', '0', @right)) as PD_RL;
+-------+
| PD_RL |
+-------+
| 0,0   |
+-------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> SET @left = '', @right = '';
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> SELECT CONCAT_WS(',', IF(@left IS NULL OR @left = '', '0', @left), IF(@right IS NULL OR @right = '', '0', @right)) as PD_RL;
+-------+
| PD_RL |
+-------+
| 0,0   |
+-------+
1 row in set (0.00 sec)

Bud Damyanov · Accepted Answer · 2018-10-25 18:15:09Z

0

Change this line of query:

  CONCAT_WS(',',IFNULL(a.PD_right, ''),IFNULL(a.PD_left, '')) as PD_RL

to

  CONCAT_WS(',',IFNULL(a.PD_right, 0),IFNULL(a.PD_left, 0)) as PD_RL

Alternatively you can test the values before concatenation with CASE ...WHEN...THEN...END syntax.

See more here.

answered Oct 25, 2018 at 18:15

Bud Damyanov

32.1k7 gold badges48 silver badges53 bronze badges

1 Comment

Diamond Over a year ago

in my database columns are blank.that the case this code will not work

Collectives™ on Stack Overflow

How to set column value as zero if value is empty using php/mysqli?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related