PDO PHP-查找2个日期之间的所有行-显示空白 [英] PDO PHP - Find all rows between 2 dates - shows blank

问题描述

使用PDO php，我试图获取数据库表中具有本周内start_date的所有行.要计算本周的第一天和最后一天，我使用以下方法:

Using PDO php i am trying to get all the rows in my database tables that have a start_date within the current week. To calculate the first and last date of this week i use the following:

$week_start = date('d-m-Y',time()+( 1 - date('w'))*24*3600);  
$week_end =  date('d-m-Y',time()+( 7 - date('w'))*24*3600); 

然后在我的pdo查询中，我有:

Then within my pdo query i have:

$query = " 
    SELECT 
        *
    FROM 
        bookings as bb 
    INNER JOIN 
        reservation as br ON bb.booking_id=br.bookings_id
    INNER JOIN 
        cars as bc ON br.car_id=bc.car_id 
    WHERE 
        payment_success=1 AND 
        is_block=false AND 
        is_deleted=0 AND
        DATE_FORMAT(start_date, '%d-%m-%Y') 
        BETWEEN '".$week_start."' AND '".$week_end."'
"; 

try { 
    $stmt = DB::get()->prepare($query); 
    $stmt->execute(); 

    $rows = $stmt->fetchAll();
}  
catch(PDOException $ex) { 
    die("Failed to run query: " . $ex->getMessage()); 
} 

foreach($rows as $row):
    $row['booking_id'] etc...
endforeach;

问题是，尽管有许多预订在本周内开始，但我没有在输出中显示任何行

The problem is i am not getting any rows to show up in the output despite having multiple bookings which have a start date within this week

推荐答案

DATE_FORMAT()返回字符串，而不是日期.将3个字符串参数传递给BETWEEN ...谁知道该返回什么.

DATE_FORMAT() returns a string, not a date. Passing 3 string arguments to BETWEEN... who knows what that is going to return.

要取消向后退代码，请使用:

To un-ass-backwards your code, use:

$week_start = date('Y-m-d',time()+( 1 - date('w'))*24*3600);  
$week_end =  date('Y-m-d',time()+( 7 - date('w'))*24*3600); 

以mySQL期望的方式格式化日期，并且:

to format your dates in the way mySQL expects, and:

WHERE 
  start_date BETWEEN '".$week_start."' AND '".$week_end."'

在查询中.

或者，如果您更喜欢面向对象的方法，请执行以下操作:

Or if you would prefer an object-oriented approach, do something like this:

$week_start = new DateTime; 
$week_end = new DateTime;
$week_start->setTimestamp(time()+( 1 - date('w'))*24*3600)); 
$week_end->setTimestamp(time()+( 7 - date('w'))*24*3600);

然后在您的查询中执行

WHERE 
  start_date 
      BETWEEN '".$week_start->format('Y-m-d')."'
      AND '".$week_end->format('Y-m-d')."'

然后，对于其他所有内容，您都可以按照自己的意愿来回显该格式:

Then for everything else, you can echo the format how you wish:

echo $date->format('d-m-Y'); // etc

这篇关于PDO PHP-查找2个日期之间的所有行-显示空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！