CODEWITHSUNDEEP
phpmysql
Frederik Heyninck
Frederik Heyninck

Reputation: 3543

How do i query the price between a start day/month and end day/month

The day is 4, the month is 10. How do i query a price where the month is between the start_month and end_month range AND they day is also between a range?

The start_day, start_month, end_day and end_month can be variable in the database.

Screenshot of the database: Database

My current query:

$price = FrontendModel::getDB()->getVar('SELECT p.price FROM appartments_pricing as p WHERE (p.days_start >= ? AND p.months_start >= ?) AND (p.days_end <= ? AND p.months_end <= ?) AND p.appartment_id = ?', array($day,$month, $day, $month, $appartment_id));

Upvotes: 0

Views: 133

Answers (2)

nachito
nachito

Reputation: 7035

If you want the day to lie within the range days_start <= day <= days_end, then you need to switch your query around (same goes for month):

$price = FrontendModel::getDB()->getVar('SELECT p.price FROM
appartments_pricing as p WHERE (p.days_start <= ? AND p.months_start <= ?) AND
(p.days_end >= ? AND p.months_end >= ?) AND p.appartment_id = ?',
array($day,$month, $day, $month, $appartment_id));

Upvotes: 0

jancha
jancha

Reputation: 4967

okay another option:

mysql> select
           ap_id, price,
           date(concat('2000', '-', months_start, '-', days_start)) as start,
           date(concat('2000', '-', months_end, '-', days_end)) as end
           from x
           having date('2000-10-4') > start and date('2000-10-4') < end;

+-------+-------+------------+------------+
| ap_id | price | start      | end        |
+-------+-------+------------+------------+
|    32 |    15 | 2000-01-01 | 2000-12-31 |
+-------+-------+------------+------------+

Upvotes: 1

Related Questions