在 PHP Codeigniter 中检索每月 1 到 31 之间的数据行数

Question

我正在尝试获取在特定月份之间创建的数据，例如，我正在尝试获取 2019 年 12 月创建的数据行总数。

这是我的模型代码

//Get january sles
public function getJanSale()
{
$yr = date('Y');
$this->db->where('td_created_at BETWEEN "'. date('d-m-Y', strtotime("1-12-".$yr)). '" and "'. date('d-m-Y', strtotime("31-12-".$yr)).'"');
$query = $this->db->get('zd_item_downloads');
return $query->num_rows();
}

结果返回 0。同时，我得到了一些在 12 月创建的数据，这些数据作为当前时间戳插入我的数据库中。我做错了什么？

Answer 1

你可以在这里尝试以下代码

public function getSale()
{
$year = date('Y');
$month = date('m');
$this->db->where('MONTH(td_created_at) = '. $month. ' AND YEAR(td_created_at) = '. $year);
$query = $this->db->get('zd_item_downloads');
return $query->num_rows();
}

Answer 2

你可以在这里尝试以下代码

public function getJanSale()
{
$year = date('Y');
$month = date('m');
$this->db->where('MONTH(td_created_at) = '. $month. ' AND YEAR(td_created_at) = '. $year);
$query = $this->db->get('zd_item_downloads');
return $query->num_rows();
}

或者更简单的Mysql函数CURDATE()

$this->db->where('MONTH(td_created_at) = MONTH(CURDATE()) AND YEAR(td_created_at) = YEAR(CURDATE()));

Answer 3

对于所有月份总计：

select
SUM(CASE month(td_created_at) WHEN 1 THEN 1 ELSE 0 END) AS 'Jan',
SUM(CASE month(td_created_at) WHEN 2 THEN 1 ELSE 0 END) AS 'Feb',
SUM(CASE month(td_created_at) WHEN 3 THEN 1 ELSE 0 END) AS 'Mar',
SUM(CASE month(td_created_at) WHEN 4 THEN 1 ELSE 0 END) AS 'Apr',
SUM(CASE month(td_created_at) WHEN 5 THEN 1 ELSE 0 END) AS 'May',
SUM(CASE month(td_created_at) WHEN 6 THEN 1 ELSE 0 END) AS 'Jun',
SUM(CASE month(td_created_at) WHEN 7 THEN 1 ELSE 0 END) AS 'Jul',
SUM(CASE month(td_created_at) WHEN 8 THEN 1 ELSE 0 END) AS 'Aug',
SUM(CASE month(td_created_at) WHEN 9 THEN 1 ELSE 0 END) AS 'Sep',
SUM(CASE month(td_created_at) WHEN 10 THEN 1 ELSE 0 END) AS 'Oct',
SUM(CASE month(td_created_at) WHEN 11 THEN 1 ELSE 0 END) AS 'Nov',
SUM(CASE month(td_created_at) WHEN 12 THEN 1 ELSE 0 END) AS 'Dec'
from 
zd_item_downloads

单月：

select
SUM(CASE month(td_created_at) WHEN 12 THEN 1 ELSE 0 END) AS 'Dec'
from 
zd_item_downloads

根据年度总数更新

$this->db->select("
SUM(CASE year(td_created_at) WHEN " . date("Y") . " THEN 1 ELSE 0 END) AS 'current_year_total'")
->from('zd_item_downloads')

管理 php date("y"( 函数以获取上一年计数

2019年12月具体

$this->db->select("
SUM(CASE month(td_created_at) WHEN 12 THEN 1 ELSE 0 END) AS 'Dec'")
->from('zd_item_downloads')
->where("td_created_at BETWEEN '" . date("Y") . "/01/01' AND '" . date("Y") . "/12/31'")