php - 我想从数据库中检索数据条件是数据之间的两个日期

Question

这个问题已经有答案了:

How do I query between two dates using MySQL? (12 个回答)

已关闭 7 年前。

CREATE TABLE IF NOT EXISTS `guest_details` (
  `name` varchar(15) NOT NULL,
  `email` varchar(15) NOT NULL,
  `cell` varchar(15) NOT NULL,
  `roomtype` varchar(15) NOT NULL,
  `checkin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `checkout` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `adults` varchar(15) NOT NULL,
  `numberofrooms` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `guest_details` (`name`, `email`, `cell`, `roomtype`, `checkin`, `checkout`, `adults`, `numberofrooms`) 
VALUES
('sumanth', 'sumanth@gmail.c', '99999999999999', 'Single_AC', '2014-09-15 00:00:00', '2014-09-19 00:00:00', 'FGDFG', '2'),
('vijey', 'vijey.sk@gmail.', '222222222222222', 'Double_AC', '2014-09-21 00:00:00', '2014-09-24 00:00:00', 'lpuio', '1');

我需要从数据库中检索 checkin 日期和 checkout 日期之间的数据。我的问题是日期无法比较。我也在检查 mysql。我需要解决的问题是什么。

$res = "select * from guest_details where checkin>=$date1 AND checkout<=$date1";
$res1 = mysql_query($res);
if ($res1) 
/*while($row = mysql_fetch_array($res1))*/ 
{
    echo $date1;        
}
?>

Answer 1

您正在比较 $date1 值，而不用单引号引用它，因此您的结果查询类似于

checkin>= 2014-09-18 AND checkout<= 2014-09-18 

它会产生错误，这必须类似于

select * from guest_details where checkin>='$date1' AND checkout<= '$date1'

更好地为参数使用占位符

$dbh = new PDO('mysql:host=yourhost;dbname=dbname', 'user', 'pass');
$sth = $dbh->prepare("select * from guest_details where
checkin >= :date  AND checkout <= :date 
");
$sth->bindParam(':date', $date1, PDO::PARAM_STR);
$sth->execute();
$result = $sth->fetchAll(); 

foreach($result as $res){

    echo $res['your_date_column_name'];
    echo $res['another_column'];

}

PDO::errorInfo

此外，在开发过程中，您应该在执行之前注意错误

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); 
if (!$sth) { 
    echo "\nPDO::errorInfo():\n"; 
    print_r($dbh->errorInfo()); 
}