1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

MySQL: Special Price Scenario

Discussion in 'MySQL' started by cancer10, Jan 28, 2011.

  1. cancer10

    cancer10 New Member

    I am working on a hotel booking engine. There is a special price scenario where the customer will see a special price based on the dates the admin defines.

    If the date the customer is booking a hotel room falls on the date range specified, the price they will see is what the admin has defined.

    So if the admin defines prices for a date rage from 1st Jan to 31st Jan in the following way,

    1st Jan 2011 to 15th Jan 2011 = $100 / per day
    16th Jan 2011 to 16th Jan 2011 = $500 / per day
    17th Jan 2011 to 31st Jan 2011 = $100 / per day

    Code:
    CREATE TABLE `special_prices` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `date_start` DATE DEFAULT NULL,
      `date_end` DATE DEFAULT NULL,
      `special_price` DECIMAL(10,2) DEFAULT NULL,
      `status_id` TINYINT(4) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MYISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
    
    
    INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES ( NULL,'2011-01-1','2011-01-15','100','1');
    
    INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES ( NULL,'2011-01-16','2011-01-16','500','1');
    
    INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES (NULL,'2011-01-17','2011-01-31','100','1');
    
    [​IMG]


    When I run the following query:
    Code:
    SELECT
      `id`,
      `date_start`,
      `date_end`,
      `special_price`
    FROM `special_prices`
    WHERE date_start >= '2011-01-12' AND date_end <= '2011-01-18'
    The result I get:
    Code:
    id	date_start	date_end	special_price	
    2	2011-01-16	2011-01-16	500.00	
    Ideally the query should fetch all the 3 rows since they meet my query. Does anyone of you know why is this happening?


    Any help is appreciated.
     

Share This Page