Counting how many MySQL fields in a row are filled

问题:

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.

回答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';
}

回答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 info means: empty or null.

回答3:

i think this would be better solved in php where you make a function that defines the column names and assigns a weight to each one and then calculatea the %complete based on the data retrieved from the db that why a name can count for 20% and an age can count for 5% etc. and the columns and weight definitions can even be stored externally in say an xml file so someone else can play with percentages and dont need a programmer to tweak the requirements