在每个用户的给定日期范围内插入缺失的日期

Question

我有一个名为 tblprocesstimekeeping 的表，其中包含以下数据：

假设我有一个日期范围2019-05-01 - 2019-05-15

我想要做的是在每个 empCompID 的 tblprocesstimekeeping 数据中的给定范围之间插入缺失的日期。

这是我的代码：

$begin = new DateTime('2019-05-01'); // Get Begin Date

$end = new DateTime('2019-05-15'); // Get End Date

$end = $end->modify( '+1 day' ); // Increment +1 Day

$interval = new DateInterval('P1D');

$daterange = new DatePeriod($begin, $interval ,$end); // Get Date Range

foreach($daterange as $date){

    $dateNumber = $date->format("Y-m-d"); // counter date + 1 day

    //Select company access id and date from tblprocesstimekeeping

    $query = $this->db->query("SELECT * FROM tblprocesstimekeeping where companyAccessID = '$companyAccessID'");                

        foreach ($query->result() as $row){ 

            $empCompID = $row->empCompID;

            $date = $row->date;                     

            //echo $date .' = '. $empCompID.'<br>';

            if ($date == $dateNumber) {

                echo $date .' = '. $empCompID.' = GOOD <br>';

            } else{

                echo $dateNumber .' = '. $empCompID.' = NO DATA <br>';

            }

    }                   

}

输出应该是：

2019-05-01 = 11007 - NO DATA

2019-05-01 = 20003 - NO DATA

2019-05-02 = 11007 - GOOD

2019-05-02 = 20003 - GOOD

2019-05-03 = 11007 - GOOD

2019-05-03 = 20003 - GOOD

2019-05-04 = 11007 - GOOD

2019-05-04 = 20003 - GOOD

2019-05-05 = 11007 - NO DATA

2019-05-05 = 20003 - NO DATA

2019-05-06 = 11007 - NO DATA

2019-05-06 = 20003 - GOOD

2019-05-07 = 11007 - GOOD

2019-05-07 = 20003 - GOOD

2019-05-08 = 11007 - GOOD

2019-05-08 = 20003 - GOOD

2019-05-09 = 11007 - GOOD

2019-05-09 = 20003 - GOOD

2019-05-10 = 11007 - GOOD

2019-05-10 = 20003 - GOOD

2019-05-11 = 11007 - GOOD

2019-05-11 = 20003 - GOOD

2019-05-12 = 11007 - NO DATA

2019-05-12 = 20003 - NO DATA

2019-05-13 = 11007 - NO DATA

2019-05-13 = 20003 - NO DATA

2019-05-14 = 11007 - GOOD

2019-05-14 = 20003 - GOOD

2019-05-15 = 11007 - GOOD

2019-05-15 = 20003 - GOOD

Answer 1

这是您的问题：您正在表中创建不存在的行。对于不在表中的那一天，您正在创建一个行来指示表中没有一行。这使得这个问题在您采用的方法中有点棘手。您可以构建一个复杂的 MYSQL 查询来处理这个问题，也可以在 PHP 中处理它。更好的方法是通过所有用户和每一天。// List of all users to find$users = ['11007', '20003'];foreach ($daterange as $date) {&nbsp; &nbsp; // Get date in database format&nbsp; &nbsp; $dateNumber = $date->format("Y-m-d"); // counter date + 1 day&nbsp; &nbsp; // Go through all users&nbsp; &nbsp; foreach ($users as $user) {&nbsp; &nbsp; &nbsp; &nbsp; // Find all accesses for that user and day&nbsp; &nbsp; &nbsp; &nbsp; $query = $this->db->query("SELECT * FROM tblprocesstimekeeping WHERE companyAccessID = '$companyAccessID' AND empCompID = '$user' AND date = '$dateNumber'");&nbsp; &nbsp; &nbsp; &nbsp; // If there is a row, then paste it, otherwise past NO DATA&nbsp; &nbsp; &nbsp; &nbsp; if ($query->num_rows()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo $dateNumber.' = '.$user->id.' = GOOD <br>';&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo $dateNumber.' = '.$user->id.' = NO DATA <br>';&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}

Answer 2

你的算法不正确。由于您没有按日期过滤表数据，因此对于日期范围内的每个日期，您都选择了 tblprocesstimekeeping 表中的所有行，并打印所有行的结果（这就是每个日期有 21 行的原因）。我不熟悉 php，所以我不会在这里发布代码，但这里是我的猜测：首先，您需要知道员工的 id 您可以使用以下查询从数据库中获取它们：“select distinct empCompId from FROM tblprocesstimekeeping where companyAccessID = '$companyAccessID'"&nbsp;并将它们保存在某个地方（也许在一个数组中）然后，要完成您想要的操作，您需要执行两次循环// first iterate by dateforeach($daterange as $date)&nbsp; // then, for each date iterate by employee&nbsp; &nbsp; foreach($employees as $empId){&nbsp; &nbsp; &nbsp;// check existance on record&nbsp; &nbsp; &nbsp;If (exists on data base)&nbsp; &nbsp; &nbsp; &nbsp; Echo ‘good’&nbsp; &nbsp; else&nbsp; &nbsp; &nbsp; &nbsp; echo ‘nodata’对于“检查数据库中的存在”，您可以简单地更改查询以按日期和$query = $this->db->query("SELECT * FROM tblprocesstimekeeping where companyAccessID = '$companyAccessID' and date='$dateNumber'&nbsp;");&nbsp;但你应该考虑到这一点：您对数据库的连续调用次数与日期范围内的日期一样多。如果可能，请考虑一种算法，该算法仅对 datadate进行一次调用并返回从日期范围开始到结束的所有数据（在 for foreach $daterange cicle 之前）并且您可以简单地过滤该数据以检查是否存在代码上的特定日期。如果所涉及的记录数量不是那么高（这里只有 21 个），则此解决方案的性能会好得多