#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171

    Updating large amount of data. Real life data update scenario with inevitable loop!


    This is the control panel of a hotel booking website.

    In brief, this is how it works:

    1. The supplier logs in to the system, and gets redirected to control panel.
    2. The supplier selects the hotel, room and package he needs to update the rates for,
    3. The supplier fills the form with appropriate values,
    4. The supplier clicks submit and the allotments (number of available rooms) for the selected package and the other, packages for that room get updated.
    5. NOTE: Each package can have a different rate for each day of the year. This means each package must have 365 rows in allotments table and that gets updated on each form submit,

    I wonder if it is possible to update allotments_new table without using any queries in the loop!



    Tables:
    Code:
    hotels
    ---------
    hotel_id,
    
    
    hotel_rooms
    ----------------
    room_id, hotel_id
    
    
    
    packages
    ---------------
    package_id, room_id, hotel_id
    
    
    
    
    allotments
    -------------------
    CREATE TABLE `allotments_new` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `package_id` int(11) NOT NULL,
     `room_id` int(11) NOT NULL,
     `hotel_id` int(11) NOT NULL,
     `date_added` date NOT NULL,
     `day_date` date NOT NULL,
     `number_of_rooms` int(11) NOT NULL,
     `status` varchar(50) NOT NULL,
     `supplier_id` int(11) NOT NULL,
     `day_of_the_week` varchar(255) NOT NULL,
     `day_rate` decimal(11,2) NOT NULL,
     `source` text NOT NULL,
     `minimum_number_of_nights` int(11) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `day_date_index` (`day_date`),
     KEY `day_rate_index` (`day_rate`),
     KEY `room_id_index` (`room_id`),
     KEY `index_minimum_number_of_nights` (`minimum_number_of_nights`),
     KEY `id_package_id` (`package_id`),
     KEY `id_hotel` (`hotel_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=8502054 DEFAULT CHARSET=latin1

    These are the posted values after submitting the form:

    Code:
    Array ( [year] => 2012 [package_id] => 9210 [room_id] =>
     187165 [hotel_id] => 1960 [rates_or_allotments] => 
    allotments [brn_low] => on [brn_shoulder] => on 
    [brn_high] => on [brn_special] => on [brn_weekends] => 
    on [textfield] => 6 [jan] => Array ( [0] => 6 [1] => 6 [2] =>
     6 [3] => 6 [4] => 6 [5] => 6 [6] => 6 [7] => 6 [8] => 6 [9] 
    => 6 [10] => 6 [11] => 6 [12] => 6 [13] => 6 [14] => 6 
    [15] => 6 [16] => 6 [17] => 6 [18] => 6 [19] => 6 [20] => 
    6 [21] => 6 [22] => 6 [23] => 6 [24] => 6 [25] => 6 [26] 
    => 6 [27] => 6 [28] => 6 [29] => 6 [30] => 6 ) [feb] => 
    Array ( [0] => 5 [1] => 5 [2] => 5 [3] => 5 [4] => 5 [5] => 
    5 [6] => 5 [7] => 5 [8] => 5 [9] => 5 [10] => 5 [11] => 5 
    [12] => 5 [13] => 5 [14] => 5 [15] => 5 [16] => 5 [17] => 
    5 [18] => 5 [19] => 5 [20] => 5 [21] => 5 [22] => 5 [23] 
    => 5 [24] => 5 [25] => 5 [26] => 5 [27] => 5 [28] => 5 ) 
    [mar] => Array ( [0] => 6 [1] => 6 [2] => 6 [3] => 6 [4] =>
     6 [5] => 6 [6] => 6 [7] => 6 [8] => 6 [9] => 6 [10] => 6 
    [11] => 6 [12] => 6 [13] => 6 [14] => 6 [15] => 6 [16] =>
     6 [17] => 6 [18] => 6 [19] => 6 [20] => 6 [21] => 6 [22]
     => 6 [23] => 6 [24] => 6 [25] => 6 [26] => 6 [27] => 6 
    [28] => 6 [29] => 6 [30] => 6 ) [apr] => Array ( [0] => 5 
    [1] => 5 [2] => 5 [3] => 5 [4] => 5 [5] => 5 [6] => 5 [7] 
    => 5 [8] => 5 [9] => 5 [10] => 5 [11] => 5 [12] => 5 [13] 
    => 5 [14] => 5 [15] => 5 [16] => 5 [17] => 5 [18] => 5 
    [19] => 5 [20] => 5 [21] => 5 [22] => 5 [23] => 5 [24] =>
     5 [25] => 5 [26] => 5 [27] => 5 [28] => 5 [29] => 5 ) 
    [may] => Array ( [0] => 6 [1] => 6 [2] => 6 [3] => 6 [4] =>
     6 [5] => 6 [6] => 6 [7] => 6 [8] => 6 [9] => 6 [10] => 6 
    [11] => 6 [12] => 6 [13] => 6 [14] => 6 [15] => 6 [16] => 
    6 [17] => 6 [18] => 6 [19] => 6 [20] => 6 [21] => 6 [22] 
    => 6 [23] => 6 [24] => 6 [25] => 6 [26] => 6 [27] => 6 
    [28] => 6 [29] => 6 [30] => 6 ) [jun] => Array ( [0] => 6 
    [1] => 6 [2] => 6 [3] => 6 [4] => 6 [5] => 6 [6] => 6 [7] 
    => 6 [8] => 6 [9] => 6 [10] => 6 [11] => 6 [12] => 6 [13] 
    => 6 [14] => 6 [15] => 6 [16] => 6 [17] => 6 [18] => 6 
    [19] => 6 [20] => 6 [21] => 0 [22] => 6 [23] => 0 [24] =>
     6 [25] => 0 [26] => 6 [27] => 0 [28] => 6 [29] => 6 ) [jul] 
    => Array ( [0] => 1 [1] => 1 [2] => 1 [3] => 1 [4] => 1 [5] 
    => 1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 [10] => 1 [11] =>
     1 [12] => 1 [13] => 1 [14] => 1 [15] => 1 [16] => 1 [17] 
    => 1 [18] => 1 [19] => 1 [20] => 1 [21] => 1 [22] => 1 
    [23] => 1 [24] => 1 [25] => 1 [26] => 1 [27] => 1 [28] => 
    1 [29] => 1 [30] => 1 ) [aug] => Array ( [0] => 1 [1] => 1 
    [2] => 1 [3] => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 [8] 
    => 1 [9] => 1 [10] => 1 [11] => 1 [12] => 1 [13] => 1 [14] 
    => 1 [15] => 1 [16] => 1 [17] => 1 [18] => 1 [19] => 1 
    [20] => 1 [21] => 1 [22] => 1 [23] => 1 [24] => 1 [25] =>
     1 [26] => 1 [27] => 1 [28] => 1 [29] => 1 [30] => 1 ) 
    [sep] => Array ( [0] => 1 [1] => 1 [2] => 1 [3] => 1 [4] =>
     1 [5] => 1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 [10] => 1 
    [11] => 1 [12] => 1 [13] => 1 [14] => 1 [15] => 1 [16] =>
     1 [17] => 1 [18] => 1 [19] => 1 [20] => 1 [21] => 1 [22] 
    => 1 [23] => 1 [24] => 1 [25] => 1 [26] => 1 [27] => 1 
    [28] => 1 [29] => 1 ) [oct] => Array ( [0] => 1 [1] => 1 [2] 
    => 1 [3] => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 [8] => 1 
    [9] => 1 [10] => 1 [11] => 1 [12] => 1 [13] => 1 [14] => 1
     [15] => 1 [16] => 1 [17] => 1 [18] => 1 [19] => 1 [20] => 
    1 [21] => 1 [22] => 1 [23] => 1 [24] => 1 [25] => 1 [26] 
    => 1 [27] => 1 [28] => 1 [29] => 1 [30] => 1 ) [nov] => Array ( [0] => 1 [1] => 1 [2] => 1 [3] => 1 [4] => 1 [5] => 
    1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 [10] => 1 [11] => 1 
    [12] => 1 [13] => 1 [14] => 1 [15] => 1 [16] => 1 [17] => 
    1 [18] => 1 [19] => 1 [20] => 1 [21] => 1 [22] => 1 [23] 
    => 1 [24] => 1 [25] => 1 [26] => 1 [27] => 1 [28] => 1 
    [29] => 1 ) [dec] => Array ( [0] => 1 [1] => 1 [2] => 1 [3] 
    => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 
    [10] => 1 [11] => 1 [12] => 1 [13] => 1 [14] => 1 [15] => 
    1 [16] => 1 [17] => 1 [18] => 1 [19] => 1 [20] => 1 [21] 
    => 1 [22] => 1 [23] => 1 [24] => 1 [25] => 1 [26] => 1 
    [27] => 1 [28] => 1 [29] => 1 [30] => 1 ) [submit_x] => 55
     [submit_y] => 17 )
    I thought maybe I can update the date range using something like the code below, but looks like using loops in inevitable as I have to point to each and every posted value:

    PHP Code:
    $upodate "SELECT day_date
    , number_of_rooms
    , day_date
    , day_of_the_week
    FROM
     allotments_new
    WHERE day_date
    BETWEEN 
         (        
              SELECT CONCAT( YEAR( CURDATE( ) ) , '-01-01' )
         )
    AND 
         (
              CONCAT( YEAR( CURDATE( ) ) +1, '-01-01' )
         )
    AND package_id=
    $_POST['package_id']
    ORDER BY `allotments_new`.`day_date` ASC"

  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    2012? What was the rate for Feb 29th?

    Anyway, isn't this more of a user-interface design issue?

    Supplier logs in to calendar. Selects date or date range. Makes changes and hits 'Update'. The update script would still most likely loop through an array but most of the time it would be an array of just one or a handful of items.

    Either way, one or 365, the task seems trivial for MySQL.
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    Originally Posted by cafelatte
    Either way, one or 365, the task seems trivial for MySQL.
    What does that mean?
    Thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Originally Posted by zxcvbnm
    What does that mean?
    The premise of the question is flawed.

    365 rows is not a 'large amount of data'.

IMN logo majestic logo threadwatch logo seochat tools logo