I want to retrive data from database condition is two dates between data

Question

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');

i need to retrive data from database between the checkin and checkout dates.My problem is dates are not compare. i am checking in mysql also.What is the problem i need solution.

$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;        
}
?>

Date string must be quoted `'$date1'`,you need to fetch results from query and better to use PDO — M Khalid Junaid, Sep 18 '14 at 07:55

score 0 · Answer 1 · answered Sep 18 '14 at 08:06

You are comparing $date1 values without quoting it with single quotes so your resultant query is like

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

which generates error this must be something like

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

And more better to use placeholders for your parameters

$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

Also during development you should care for errors before execute

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

Mohammad Nurul Huda Rimon · Answer 2 · 2014-09-18T09:52:01.107

$from_date = $_POST['from_date'];
$to_date   = $_POST['to_date'];

$sql = "select * from guest_details where curdate() between '$from_date' and '$to_date' ";
$query = mysql_query($sql);

while($res_data = mysql_fetch_assoc($query)){
   echo $res_data['fields_name'];   
}

N.B. Where CURDATE() returns the current date and compare with the value of $from_date and $to_date. curdate() work as like : if(curdate() <= $from_date and $to_date) return true; otherwise false. compare with the pc current date.

I want to retrive data from database condition is two dates between data

2 Answers2