作者:Jay_5 | 来源:互联网 | 2022-12-09 15:31
我有多个日期,我需要计算数据库中特定日期的记录数.我正在使用下面的代码,但是..问题是它会在循环内每次都有借口.我相信有更好的方法可以做到这一点.提前致谢.
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";
}
KIKO Softwar..
5
您正在执行此查询:
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);
}
请注意,我定义了一个添加天数的函数,这似乎很有用.
1> KIKO Softwar..:
您正在执行此查询:
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);
}
请注意,我定义了一个添加天数的函数,这似乎很有用.