How to count the number of empty columns in MYSQL

Question

I have a MYSQL client registration database.

I need to make a query to know the amount of blank columns each record has.

For example, in total I have 15 columns (name, email, address, etc) and "So" only registered the name, so the return of this query should be 14, because the total is 15 columns and he only filled 1.

The result can come directly from the MYSQL query or, if needed, with the help of PHP (using foreach, for, or any other type)

Thank you!

Edit* So I found the following temporary solution

SELECT * FROM clientes WHERE idcliente = 4;

and in PHP

$result = $db->query($sql);
$resultados= $result->fetchAll( PDO::FETCH_ASSOC );
$count=0;
foreach($resultados[0] as $valor){
    if($valor!=''){
        $cont++;
    }
}

There I use $Count as the amount of fields filled.

Answer 1

By checking the mysql documentation will see that BOOL or BOOLEAN is only a synonym for the TINYINT(1), which will be represented by the values 0 and 1. That is, when summing the boolean values you will have the amount of true values.

With that in mind and considering that your table has the columns id, A, B, C, ..., you can do:

SELECT
    id,
    (A = '') + (B = '') + (C = '') + ... AS total
FROM <tabela>

^{See working on DB-Fiddle}

Thus, the conditions A = '' (and analogues) will return true when the spine is empty (and not zero), thus adding up the amount of empty columns.

If we consider the example table:

| id  | a   | b   | c   |
| --- | --- | --- | --- |
| 1   | a   |     |     |
| 2   | a   | b   |     |
| 3   | a   | b   | c   |
| 4   |     | b   | c   |
| 5   |     | b   |     |
| 6   | a   |     | c   |

The output of SQL would be:

| id  | total |
| --- | ----- |
| 1   | 2     |
| 2   | 1     |
| 3   | 0     |
| 4   | 1     |
| 5   | 2     |
| 6   | 1     |

View on DB Fiddle