计算行中有多少MySQL字段已填充（或为空） [英] Counting how many MySQL fields in a row are filled (or empty)

问题描述

我需要组合一个方法，以便我量化一行中已有多少字段被用户填充。

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.

例如：

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

我不知道这是否需要通过SQL子句完成，或者如果通过PHP SQL函数可以查询DB并计算完成度。

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.

任何建议如何做到这一点？我使用PHP 5（codeigniter）和SQL 5.0.77，但任何路线图将非常感谢。

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

推荐答案

$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';
}

这篇关于计算行中有多少MySQL字段已填充（或为空）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！