字符串格式的日期预先显示两条记录

Question

我的查询运行良好，但我希望显示今天的日期计数+提前2天添加，并按今天的日期订购数据，然后明天和后天？

由于记录已经太多，所以varchar行meeting_date不能以另一种格式更改。

这是MySQL查询

日期以月/日/年的形式储存，如2016年11月1日。

$today = date("m/d/Y");

$query = "SELECT COUNT(*) as num FROM records WHERE meeting = 'Yes' AND (meeting_date LIKE '%".$today."%') ORDER BY id DESC";
$total_pages = mysql_fetch_array(mysql_query($query));

感谢你的时间和帮助。

Answer 1

你需要这样做：

// get 3 days used in the query
$today = date("m/d/Y");
$day1 =  date("m/d/Y", time() + 86400 ); 
$day2 =  date("m/d/Y", time() + 86400*2 ); 

// updated query without LIKE
 $query = "SELECT COUNT(*) as num FROM records WHERE meeting = 'Yes' AND (meeting_date = '$today' OR meeting_date = '$day1' meeting_date = '$day2') ORDER BY id DESC";

之后，需要使用php对数组进行排序。

Answer 2

试一试

$today = date("m/d/Y");//today's date
$after2days = date('m/d/Y', strtotime("+2 days"));//2 days from now
$query = "SELECT COUNT(*) as num FROM records WHERE meeting = 'Yes' AND meeting_date BETWEEN '".$today."' AND '".$after2days."' ORDER BY meeting_date";

我建议您不要使用mysql_*函数，因为它们是不推荐的。请使用mysqli_*或PDO函数。

Answer 3

在获取日期时，您应该在php中这样做。

$today = date("m/d/Y");
$tommarow= date('m/d/Y', strtotime("+1 days"));
$twoday= date('m/d/Y', strtotime("+2 days"));
$query = "SELECT COUNT(*) as num FROM records WHERE meeting = 'Yes' AND 
(meeting_date = '".$today."' or  meeting_date = '".$tommarow."' or 
meeting_date = '".$twoday."') group by meeting_date ORDER BY meeting_date";