我的查询工作得很好,但我希望显示今天日期的计数+提前添加2天,并在今天的日期之前订购数据,然后是明天和后天?
因为已经有太多的记录,所以varchar行meeting_date
不能以另一种格式更改
MySQL查询
日期存储为月/日/年,如11/01/2016。
$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));
感谢您的帮助。
Try
$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函数
您应该在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";
您需要这样做:
// 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排序数组之后。