从两个日期之间的数据库获取数据 [英] Get data from a database that is between two dates

问题描述

我编写的系统会显示所选月份的资料。用户可以选择他们想要查看的月份，然后创建一个类似这样的网址：.com / page.php？month = 03你可以猜测的页面将采用这个想法，然后使用它从数据库中获取数据

现在根据我对vars的URL中的月份，$ startdate $ enddate基于这个例子的开始日期是2011-03-01，然后将29或30加到日期的天数部分，具体取决于月份是31或30天。好的，所以我做这样的日期：

  $ startdate = mktime（0,0,0，date（$ month ），date（01），date（2011））; 
 if（$ month == 04 || $ month == 06 || $ month == 9 || $ month == 11）
 {
 $ enddate = mktime（0,0 ，0，日期（$ month），日期（01）+ 29，日期（2011））; 
} 
 else 
 {
 $ enddate = mktime（0,0,0，date（$ month），date（01）+ 30，date 2011））; 
} 
  

现在，当我尝试从数据库获取这些变量的数据时似乎工作，这是查询：

  $ getresults = mysql_query（SELECT * FROM table1 WHERE date BETWEEN $ startdate AND $ enddate ORDER BY date ASC）; 
  

事情是如果我将vars更改为硬编码日期。有任何想法吗？我认为它与一天的格式化的方式有关系吗？在我的数据库中它的Ymd，也是在php。

相同的感谢任何帮助的人真的卡在这一个。

解决方案

mktime 生成日期作为Unix时间戳（例如'1317046059'不同的格式（例如'2011-09-26'）。要使查询生效，您需要将时间戳转换为适当的格式：

  $ enddate = date（'Ym- d'，mktime（0,0,0，date（$ month），date（01）+ 29，date（2011）））; 
   
 
 
 
 更新：
 
 
 
根据您的新评论，您的日期作为时间戳存储在数据库中，您需要以这种方式格式化日期：
  $ enddate = date（'Ymd h：i：s'，mktime（0,0,0，date（$ month），date（01）+ 29，date（2011）） ）; 
  
 
I am writing a system that will show data from a selected month. The user can select the month they want to look at, this then creates a URL something like this .com/page.php?month=03 the page as you can guess will take the idea and then use it to get data from the database from that month.

Now based on the month in the URL i am making to vars, $startdate $enddate start date based on this example is 2011-03-01, i then add 29 or 30 on to the days part of the date depending if the month has 31 or 30 days. Okay so i do the dates like this:
$startdate = mktime(0,0,0,date("$month"),date("01"),date("2011"));
if( $month==04 || $month==06 || $month==9 || $month==11)
{
    $enddate = mktime(0,0,0,date("$month"),date("01")+29,date("2011"));
}
else
{
    $enddate = mktime(0,0,0,date("$month"),date("01")+30,date("2011"));
}
Now when i try to get the data from the database with these variables it doesn't seem to work, this is the query:
$getresults = mysql_query("SELECT * FROM table1 WHERE date BETWEEN $startdate AND $enddate ORDER BY date ASC");
The thing is if i change the vars to hard coded dates its works a treat... Anyone got any ideas? i was thinking it had something to do with the way the day is formatted? in my database its "Y-m-d" and is also the same in the php.

thank for any help people im really stuck on this one.
解决方案
mktime produces dates as Unix timestamps (eg. '1317046059') but your table stored dates in a different format (eg. '2011-09-26'). To make your queries work, you will need to convert your timestamps into the appropriate format:
$enddate = date( 'Y-m-d', mktime(0,0,0,date("$month"),date("01")+29,date("2011")) );
Update:

Based on your new comment that your dates are stored as timestamps in your database, you will need to format your dates in this manner:
$enddate = date( 'Y-m-d h:i:s', mktime(0,0,0,date("$month"),date("01")+29,date("2011")) );


                        
这篇关于从两个日期之间的数据库获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！