CODEWITHSUNDEEP
phpmysql
Miuranga
Miuranga

Reputation: 2463

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

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

Upvotes: 0

Views: 1429

Answers (3)

developerCK
developerCK

Reputation: 4506

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

Upvotes: 2

Alma Do
Alma Do

Reputation: 37365

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.

Upvotes: 1

ddisisto
ddisisto

Reputation: 451

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.

Upvotes: 1

Related Questions