How to get a empty value column count in a given row in My SQL

Question

I want get the empty data column count in given row by the id. EX: In my table have 15 column. I want to get empty data column count when I pass the value id of the row.

Is there any my sql function or query to get that count directly? OR I have to do it using PHP after the fetch the all row data ?

Thanks

AFAIK, there is no such query which will tell how many fields of a row are empty directly. — Hanky Panky
– Hanky Panky, Commented Jan 20, 2014 at 6:16
you have to do with php if else conditon to check each column in a loop — Agha Umair Ahmed
– Agha Umair Ahmed, Commented Jan 20, 2014 at 6:18
you need to check the contents are empty or not by using PHP, its better, no queries available in mysql.. — ReNiSh AR
– ReNiSh AR, Commented Jan 20, 2014 at 6:18
What do you mean 'empty'? Is that null or empty string '' ? — Alma Do
– Alma Do, Commented Jan 20, 2014 at 6:19

developerCK · Accepted Answer · 2014-01-20 06:55:54Z

2

if It is null in your column then,

SELECT IF(col1 IS NULL ,1,0)+ IF(col2 IS NULL ,1,0) +IF(col3_id IS NULL ,1,0) +......AS cnt 

FROM yourTable
WHERE id=100000

if it is blank then you can use IF(col1='',1,0), instead of IS NULL

edited Jan 20, 2014 at 6:55

answered Jan 20, 2014 at 6:24

developerCK

4,5263 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Miuranga Over a year ago

Thanks IF(col1='',1,0) works for both NULL and empty fields

ddisisto · Accepted Answer · 2014-01-20 06:24:22Z

1

SELECT IF(`col1`='',1,0) + IF(`col2`='',1,0) + IF(`col3`='',1,0) + IF(`col4` ...
AS EmptyCols
WHERE `id` = 1;

Change each ='' to IS NULL if by 'empty' you instead mean NULL. This is however rather long and hard to maintain for > a few columns - might be a better idea to do the count in PHP, either when you need to retrieve this value, or when the row is inserted/updated as an additional column you can then later query directly.

answered Jan 20, 2014 at 6:24

ddisisto

4413 silver badges5 bronze badges

Comments

Alma Do · Accepted Answer · 2014-01-20 06:34:25Z

Actually, you can do it in MySQL using INFORMATION_SCHEMA database. Let it be test data:

mysql> select * from test.test;
+------+------+------+------+
| id   | foo  | bar  | baz  |
+------+------+------+------+
|    1 |      | NULL |    5 |
|    2 | one  | NULL |    7 |
|    3 | two  | four |    9 |
+------+------+------+------+
3 rows in set (0.00 sec)

And id will be our search column. Then via INFORMATION_SCHEMA.COLUMNS you'll be able to create prepared statement from SQL:

SELECT
  CONCAT(
     'SELECT ', 
      GROUP_CONCAT(
        CONCAT('(', column_name,' IS NULL OR ', column_name,'="")') 
        SEPARATOR '+'), 
     ' FROM test.test WHERE id=@id') 
FROM 
  INFORMATION_SCHEMA.COLUMNS 
WHERE 
  TABLE_NAME='test' 
  AND 
  TABLE_SCHEMA='test' 
  AND 
  COLUMN_NAME!='id';

-and use it like:

mysql> set @sql=(select CONCAT('SELECT ', GROUP_CONCAT(CONCAT('(', column_name,' IS NULL OR ', column_name,'="")') SEPARATOR '+'), ' FROM test.test WH
ERE id=@id') from information_schema.columns where table_name='test' and table_schema='test' and column_name!='id');
Query OK, 0 rows affected (0.01 sec)

mysql> prepare stmt from @sql;
Query OK, 0 rows affected (0.02 sec)

Then set your id and test:

mysql> set @id=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt;
+-------------------------------------------------------------------------+
| (foo IS NULL OR foo="")+(bar IS NULL OR bar="")+(baz IS NULL OR baz="") |
+-------------------------------------------------------------------------+
|                                                                       2 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @id=2;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt;
+-------------------------------------------------------------------------+
| (foo IS NULL OR foo="")+(bar IS NULL OR bar="")+(baz IS NULL OR baz="") |
+-------------------------------------------------------------------------+
|                                                                       1 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thus, you will be able to do it in MySQL - but, really, in your case counting in application seems to be more proper solution. If you'll still use method, described above, I recommend you to add alias for result field.

Collectives™ on Stack Overflow

How to get a empty value column count in a given row in My SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related