php - 计算两个表中一行中有多少个 MySQL 字段被填充(或为空)

Question

我需要组合一个方法，使我能够量化用户已填充一行中的多少个字段。并返回一个反射(reflect)用户记录完成程度的值。这样

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

这里有同样的问题Counting how many MySQL fields in a row are filled (or empty)

但是如果我需要从 4 个带有 id 的表中查询怎么办？

我这样做是为了尝试

<?php
   $userId = $pro->id;
   $completeProfile = $db->prepare("SELECT * FROM hired_person_info WHERE id=?");
   $completeProfile = $db->prepare("SELECT * FROM hired_education_info WHERE id=?");
   $completeProfile = $db->prepare("SELECT * FROM hired_job_info WHERE id=?");
   $completeProfile = $db->prepare("SELECT * FROM hired_ts_info WHERE id=?");
   $completeProfile->bind_param('i', $userId);

if ($completeProfile->execute()) {   
$result = $completeProfile->get_result();
    while ($row = $result->fetch_row()) {
       $empty_count = 0;
       $count = count($row);
         for ($i = 0; $i < $count; $i++)
           if ($row[$i] === '' || $row[$i] === 'NULL')
           $empty_count++;

          $com = ((int)(100 * (1 - $empty_count / ($count - 1))));
           if ($com > 50) {
              echo "<span class='f_left width_autor textLeft GreenFont padding_8R'>".((int)(100 * (1 - $empty_count / ($count - 1))))."%</span>";
        } else {
             echo "".((int)(100 * (1 - $empty_count / ($count - 1))))."%";
     }
    }
  }
?>

但是当我尝试运行代码时，即使我尝试完成一些配置文件以使其达到 60%，它也总是给出 8% 完成率，但如果我完成配置文件，它不会只给出 8% 或 100%

现在我怎样才能以正确的方式编写查询以使其按预期工作。谢谢。

Answer 1

正如我在评论中提到的，我认为，因为您正在覆盖您的 $completeProfile 并且不执行它们，只执行最后一个。尝试这样的事情(未测试，因为我没有您的表结构和您的自定义数据库处理程序函数)。

$userId = $pro->id;

$query1 = "SELECT * FROM hired_person_info WHERE id=?";
showUserPercentage($userId, $query1, $db);

$query2 = "SELECT * FROM hired_education_info WHERE id=?";
showUserPercentage($userId, $query2, $db);

$query3 = "SELECT * FROM hired_job_info WHERE id=?";
showUserPercentage($userId, $query3, $db);

$query4 = "SELECT * FROM hired_ts_info WHERE id=?";
showUserPercentage($userId, $query4, $db);

function showUserPercentage($userId, $query, $db) {
    $completeProfile = $db->prepare();
    $completeProfile->bind_param('i', $userId);
    if ($completeProfile->execute()) {
        $result = $completeProfile->get_result();
        while ($row = $result->fetch_row()) {
            $empty_count = 0;
            $count = count($row);
            for ($i = 0; $i < $count; $i++) {
                if ($row[$i] === '' || $row[$i] === 'NULL') {
                    $empty_count++;
                }
            }
            $com = ((int) (100 * (1 - $empty_count / ($count - 1))));
            if ($com > 50) {
                echo "<span class='f_left width_autor textLeft GreenFont padding_8R'>" . ((int) (100 * (1 - $empty_count / ($count - 1)))) . "%</span>";
            } else {
                echo "" . ((int) (100 * (1 - $empty_count / ($count - 1)))) . "%";
            }
        }
    }
}