CODEWITHSUNDEEP
phpmysqlsql
Diamond
Diamond

Reputation: 117

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

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

  1. if two values are empty it showing like (,) only comma.

  2. If two values are there it will show like 3,4

  3. if first value is empty it will show like ,4

  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

Upvotes: 0

Views: 892

Answers (2)

Bud Damyanov
Bud Damyanov

Reputation: 31889

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.

Upvotes: 0

Gasol
Gasol

Reputation: 2537

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)

Upvotes: 2

Related Questions