1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL: Special Price Scenario

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

  1. cancer10

    cancer10 New Member

    Joined:
    Jul 12, 2008
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Webmaster
    Location:
    India
    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