php - 如何使用一个查询而不是多个

Question

我有多个日期，我需要计算数据库中特定日期的记录数。我正在使用下面的代码，但它正在工作，但是......问题是每次在循环内都会原谅一个新的查询。我相信有更好的方法可以做到这一点。提前致谢。

  for ($i = 0; $i <= $total_difference; $i++){
     $date = strtotime("+$i day", strtotime($start_date));
     $check_date = date("Y-m-d", $date);

     // Here i have to check the record against $check_date
    $query = $this->db->select("SELECT COUNT(task_date) as COUNT FROM 
    time_management");
    $this->db->from('time_management');
    $this->db->where('task_date',$check_date);
    $result = $this->db->get()->row_array();
    $count = $result['COUNT'];
    echo "count for $check_date is $count";

 }

Answer 1

您正在执行此查询:

SELECT COUNT(task_date) as COUNT 
FROM time_management 
WHERE task_date = ?

问号是日期。

您需要做的是 GROUP BY task_date 并像这样设置开始日期和结束日期:

SELECT task_date as TASKDATE,
       COUNT(task_date) as COUNT 
FROM time_management 
WHERE task_date BETWEEN ? AND ?
GROUP BY task_date

两个问号是您要计数的第一个和最后一个日期。

正如 vivek_23 所说，在结果中包含 task_date 很方便，所以我添加了它。

我将让您将其转换为 Codeigniter 使用的特定方法，这应该很容易(我不使用它)。

如果您需要零计数结果(不会出现在该查询的结果中)，您可以使用 PHP 添加它们。这比试图强制 MySQL 为不在数据库中的日期提供零计数要容易。像这样:

// suppose you start with these
$start_date       = '2018-05-04';
$total_difference = 6; 

// and suppose the result of your query looks like this
$counts = ['2018-05-04' => 5,
           '2018-05-06' => 3];

// then you can insert zero count results like this:
for ($days = 0; $days <= $total_difference; $days++) {
  $date = addDaysToDate($start_date,$days);
  if (!isset($counts[$date])) $counts[$date] = 0;
}

function addDaysToDate($date,$days)
{
  $timestamp = strtotime("+$days day",strtotime($date));
  return date("Y-m-d",$timestamp);
}

请注意，我定义了一个函数来添加天数，这似乎很有用。