0

I have a table which has costs for rooms based on from_date and to_date:

Format of db:

planid | roomtype_id | capacity_id | hotel_id |date_from | date_to | cost

and I want to get the cost of room based on arrival date which is stored in a input field when the arrival_date falls in between date_from and date_to

My code: 

$result1 = mysql_query("SELECT * FROM room_price WHERE roomtype_id = '$roomtype_id' AND capacity_id = '$capacity_id' AND hotel_id = '$hotel_id' AND date_from '$date' BETWEEN date_from AND date_to");

The query doesn't gives any output.

BRoebie
  • 374
  • 3
  • 13
Saikat Bepari
  • 121
  • 1
  • 14
  • Did you even google about this? – Deepanshu Goyal Nov 30 '15 at 12:48
  • You **really** should not be writing code that relies on `mysql_` functions anymore. The MySQL extension has been deprecated for years (ever notice these red warning boxes in the documentation?) and is about to be dropped in the upcoming PHP7 release, which can be released any time now. Also see [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). On an up-to-date server, this code has a life span of about a month, after which **it will stop working**. – Oldskool Nov 30 '15 at 12:55

2 Answers2

2

Problem is that , your\ are using two dates in mysql table colum, so you have to check as i have given code below:

$result1 = mysql_query("SELECT * FROM room_price WHERE roomtype_id = '$roomtype_id' AND capacity_id = '$capacity_id' AND hotel_id = '$hotel_id' AND date_from >='$date' And date_to<=$date");
Rajveer gangwar
  • 715
  • 4
  • 14
  • $result1 = mysql_query("SELECT * FROM room_price WHERE roomtype_id = '$roomtype_id' AND capacity_id = '$capacity_id' AND hotel_id = '$hotel_id' AND date_from <= '$date' AND date_to >= '$date'"); – Saikat Bepari Nov 30 '15 at 11:15
  • hmmm i forget to add quote to last date variable$result1 = mysql_query("SELECT * FROM room_price WHERE roomtype_id = '$roomtype_id' AND capacity_id = '$capacity_id' AND hotel_id = '$hotel_id' AND date_from >='$date' And date_to<='$date'"); – Rajveer gangwar Nov 30 '15 at 11:26
  • give me your mysql table create query – Rajveer gangwar Nov 30 '15 at 11:29
0

try this query:

$result1 = mysql_query("SELECT * FROM room_price WHERE roomtype_id = '$roomtype_id' AND capacity_id = '$capacity_id' AND hotel_id = '$hotel_id' AND BETWEEN 'your_from_Date' AND 'your_to_date'");
BRoebie
  • 374
  • 3
  • 13
Shridhar Patil
  • 362
  • 1
  • 17