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

Question

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

For instance:

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 will correspond to the degree of completion of the user record. For instance:

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

I wonder if this needs to be done through SQL statements or if you can query the database and calculate the degree of completion through the SQL PHP functions.

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

Answer 1

 $result = mysql_query('SELECT * FROM `MyTable`'); while($row = mysql_fetch_row($result)){ $empty_count = 0; $count = count($row); for($i = 0; $i < $count; $i++) if($row[$i] === '' || $row[$i] === 'NULL') $empty_count++; echo 'User '.$row[0].' = '.((int)(100*(1-$empty_count/($count-1)))).'% complete'; } 

Answer 2

 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 information means: empty or null.

Answer 3

I think this would be better solved in php, where you create a function that determines the column names and assigns weight to each of them, and then calculates% complete based on data obtained from db why the name can count on 20% and age can be 5%, etc., and columns and weight definitions can be saved externally in an xml file so that someone can play with percentages and do not need a programmer to configure the requirement