获取非空值的计数

Question

I've looked everywhere on how to do this and it seems like an awful long approach to output 1 number.

This is the layout of my database:

UserId         Info1    Info2    Info3   Info4   Info5   Info6   Info7
-------------------------------------------------------------------------------

user1      |   xyz   |         | abc   |  xyz  |       | abc   | 
user2      |         |  abc    |       |  def  |  ghi  | wxy   | jkl

What i want to do is return a number for example: 

User1 has completed 4 Tasks 
User2 has completed 5 Tasks       

There are 95 columns in this row so as you can understand it would be a real pain to count each one individually. However i only want to count the ones that are not empty and return a number.

<?
$sql="SELECT * FROM builder WHERE userid = '".$userid."' IS NOT NULL";
$query=mysql_query($sql);    
$num=mysql_num_fields($query);
echo $num;?>

This outputs how many rows there are in total regardless of whether they are empty or not so the number is always the same which isn't what I want.

Any suggestions?

Answer 1

You can count the values in MySQL using this construct:

select ((info1 is not null) +
        (info2 is not null) +
        (info3 is not null) +
        (info4 is not null) +
        (info5 is not null) +
        (info6 is not null) +
        (info7 is not null)
       ) as NumNotNull

MySQL treats boolean expressions as integers, in a numeric context. TRUE is "1", so you can just add them up to get the total that match.