php - 使用 PHP 计算非空列

Question

我有一个表“tbl_userpersonal”，

我想要实现的是检查哪些列已填充，哪些未填充，以便我可以计算或显示配置文件完成百分比。\

到目前为止，我已经尝试了很多不同的技术和代码，我的代码正在对所有列进行计数，但我希望它将空值列计为 0。请帮我解决问题。

到目前为止，即使在任何列中都有一个用户条目，它也会给我 100%，否则为 0%，没有中间值

目前，如果用户的“father_name”&“mother_name”&“DOB”列中有值，user_authtoken =“app_7837hfjd57hdj”，则预期输出应该是3个50% 即 (3/6)*100

现在的情况是

如果 user_authtoken = "app_7837hfjd57hdj"的用户在这些列中的任一列中有值，则输出显示为

6100% 即 (6/6)*100

或者如果 user_authtoken = "app_7837hfjd57hdj"的用户没有条目，则输出给出00%

这是php代码

    $personal = mysqli_query($con,"

     SELECT   father_name,
     mother_name,
     DOB,
     adhar_no,
     address,
     religion,
     CASE WHEN father_name IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN mother_name IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN DOB IS NOT NULL THEN 1 ELSE 0 END + 
     CASE WHEN adhar_no IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN address IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN religion IS NOT NULL THEN 1 ELSE 0 END AS personal
     FROM  user WHERE `user_authtoken` = '$user_ath'

     ") or die(mysqli_error($con));

      $data=mysqli_fetch_assoc($personal);
      $pro_count =  $data['personal'];
      echo $pro_count ; 
      echo '<br>';
      $percentage = ($pro_count /6)*100;
      echo $percentage;

Answer 1

我认为你在表中有空值 not null 试试这个

这将检查 null 和空

$personal = mysqli_query($con,"

     SELECT   father_name,
     mother_name,
     DOB,
     adhar_no,
     address,
     religion,
     CASE WHEN father_name IS NOT NULL AND father_name<>'' THEN 1 ELSE 0 END + 
     CASE WHEN mother_name IS NOT NULL AND mother_name<>'' THEN 1 ELSE 0 END + 
     CASE WHEN DOB IS NOT NULL AND DOB<>'' THEN 1 ELSE 0 END + 
     CASE WHEN adhar_no IS NOT NULL AND adhar_no<>'' THEN 1 ELSE 0 END +
     CASE WHEN address IS NOT NULL AND address<>'' THEN 1 ELSE 0 END +
     CASE WHEN religion IS NOT NULL AND religion<>'' THEN 1 ELSE 0 END AS personal
     FROM  user WHERE `user_authtoken` = '$user_ath'

     ") or die(mysqli_error($con));