Counting how many MySQL fields in a row are filled (or empty)

Question

I need to put together a method that would allow me to quantify how many fields in a row have been filled by a user.

For example:

User    Name    Age    Country    Gender    Height
1       Mike    34     USA        Male      6
2       Bill    23     CA                   5
3       Jane    31     USA        

In the above example, I would like to query the database and return a value that would reflect the degree of completion of the user's record. Such as:

User 1 = 100% complete
User 2 = 80% complete
User 3 = 60% complete

I wonder if this needs to be done via SQL clauses or if via PHP SQL functions one could query the DB and calculate the degree of completion.

Any suggestions how to do this? I am using PHP 5 (codeigniter) and SQL 5.0.77, but any roadmap would be greatly appreciated.

Answer 1

select 
    User,
    (
        case Name when '' then 0 else 1 end
        +
        case when Age is null then 0 else 1 end
        +
        case Country when '' then 0 else 1 end
        +
        case Gender when '' then 0 else 1 end
        +
        case when Height is null then 0 else 1 end
    ) * 100 / 5 as complete

Use the case according to what no info means: empty or null.