Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

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

    Running a massive script without keeping the user waiting for it.


    Ok,

    Lets say if user decides to update item A, over 2 million records linked to that item also need to get updated as well.

    Now in this case, which one would you do:

    1 - Somehow run the query in the background (run the update script with ajax, jquery,...) and show message on screen the records are updating now, allow a few minutes to see the changes.

    2 - Run the script and keep user waiting

    Thanks
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,866
    Rep Power
    6351
    I bet there's a third option:

    C) Optimize your database so that either 2,000,000 records a re updated in a timely manner, or you don't need to update that many.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by ManiacDan
    I bet there's a third option:

    C) Optimize your database so that either 2,000,000 records a re updated in a timely manner, or you don't need to update that many.
    \

    Highly doubt its possible! In case you curious:

    There are 3000+ hotels
    Each have rooms (up to 100)
    Each room has packages (up to 20)

    With me so far? One last thing:

    There is a table called allocations,

    It has 1 row per day (of the year) per package.
    (id, package_id, room_id, hotel_id, day_date, day_rate,...).

    Each update for each room, updates all packages and all allocations linked to that rooms packages.

    They want to do just too much with 1 click.

    Crazy
    Last edited by zxcvbnm; June 17th, 2012 at 11:36 PM.
  6. #4
  7. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,102
    Rep Power
    1990
    That doesn't sound like a great DB design. What data is shared in all of these tables that needs to be updated all of the time? I'll bet that it's something that could be either stored in one only table, or it can be moved somewhere else to make everything a lot easier.

    With DB optimisation, the key is to find any data that's shared across the tables and extract that out to one central point instead of having it in 2,000,000 different points.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    78
    Rep Power
    0
    There are 3000+ hotels
    Each have rooms (up to 100)
    Each room has packages (up to 20)

    you want to reserve hotel rooms??

    let's say you have x hotels with p rooms.

    each room have package(please define)? possible, but "pkg" will tie up y number of allocation options. So your 2mil permutation is slightly off???

    SWAG: you want to update allocation tbl in some way that affects the rooms and hotels? btw, each hotel only has their own rooms, so watch cartesian product on SQL queries...

    maybe dan notso maniac

    Highly doubt its possible!
    What is possible? DB update 1 million rows? That is no biggie...

    What are trying to do? Other than being cryptic? If the user is kicking off this whole situation, what are they doing? and why are u allowing?

    Comments on this post

    • requinix disagrees : stop casually insulting dan. and please use quote tags more often, your posts are confusing enough
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,866
    Rep Power
    6351
    Each update for each room, updates all packages and all allocations linked to that rooms packages.
    but...why? This design doesn't require a booking to be updated when the room's details change. What actual updates are being performed?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by ManiacDan
    but...why? This design doesn't require a booking to be updated when the room's details change. What actual updates are being performed?
    This is a sample a supplier's control panel. The drop down menus show the list of this suppliers hotels, hotel rooms and packages related to each room.

    The grid behind the drop down menu is an annual calendar.

    It shows the number of allotments per room, per day of the year.

    The supplier may decide to add x number of allotments for any day(s) of the year. So he simply fills the box(ex), (for day or days he wants to edit) with specific number of allotments and hits submit.

    In here it might help if I give a little info on the database:

    Hotels table is pretty simple, only column to note here is the hotel_id.

    Hotelrooms table is simple too, it has room_id, hotel_id,..

    Packages id is the same,

    allotments table is the tricky one:
    Code:
    CREATE TABLE `allotments` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `test_comun_delete` int(11) NOT NULL,
     `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=8476032 DEFAULT CHARSET=latin1
    What happens next the script goes through the allotments table and makes update or add based on the posted data. Please note here a lot of queries have to check the database before updating.

    A few things:

    * Changing allotments value per package effects all the values for all the other packages linked to that room in allotments table.

    For example:

    PHP Code:
    ////Check if other packages have allotments
    $sql_other_packages "SELECT id FROM packages WHERE room_id=:room_id AND id<>:id";
    $args_other_packages = array('room_id'=>$this_room_id,'package_id'=>$this_package);
    $other_packages DB::Load()->Execute($sql_other_packages,$args_other_packages)->returnArray();


    foreach(
    $other_packages as $values=> $other_packages_row)
        {
            
    //Check allotments table for this package    
            
    $sql_other_packages_all "SELECT id FROM allotments_new WHERE package_id=:package_id AND day_date=:day_date";
            
    $args_other_packages_all = array('day_date'=>$row['DD'],'package_id'=>$other_packages_row['id']);
            
    $other_packages_all DB::Load()->Execute($sql_other_packages_all,$args_other_packages_all)->returnNumAffectedRows();
            
    //If exists
            
    if($other_packages_all==1)
                {
                    
    $this_other_package_id DB::Load()->Execute($sql_other_packages_all,$args_other_packages_all)->returnArray();
                    
    $sql_other_packages_all_update "UPDATE allotments_new SET number_of_rooms=:number_of_rooms,
                                                      status=:status WHERE day_date=:day_date AND package_id=:package_id "
    ;
                    if(
    $the_month[$day_without_zero]>0)
                        {
                            
    $status='b';    
                        }
                    else
                        {
                            
    $status 'r';    
                        }
                    
                    
    $args_update = array('status'=>$status,'number_of_rooms'=>$the_month[$day_without_zero], 
                    
    'day_date'=>$row['DD'], 'package_id'=>$other_packages_row['id']);
                    
    DB::Load()->Execute($sql_other_packages_all_update,$args_other_packages_all_update)->returnNumAffectedRows();
                }
            else
                {
                    
    //Insert
                    
                    
                
    }
            
        }
    ////Check if other packages have allotments 
    Did you notice the monstrous loop and queries?

    * Each package has 365 rows in allotments table per year. Various reasons for that. For example, each day can have a specific rate, season or minimum number of nights.


    I have done many applications previously but this one they specifically asked for 365 rows in allotments table per package.

    If you require more information, please do not hesitate to ask.
  14. #8
  15. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    As a general answer to the question, if an operation is going to take more than a few seconds you don't keep the user waiting without feedback, particularly in a web environment.

    I didn't read through your design, but if a single operation requires updating a massive number of rows it usually, but not always, indicates a design issue with the database. Often one of two things:

    1) You have a piece of data duplicated between rows in a table when it would be more appropriate to have the data in a different, but related, table.

    2) You're storing calculated values (that can be derived from other data already in the database).
    Last edited by E-Oreo; June 18th, 2012 at 11:11 PM.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    129
    Rep Power
    11
    * Each package has 365 rows in allotments table per year. Various reasons for that. For example, each day can have a specific rate, season or minimum number of nights.
    This is the part that seems like it could be fixed. I am kind of confused on all of it because I haven't really tackled a project of this caliber but it seems as if this table would be full of repetitive data.

    Would it not be easier to have the possible packages in one table with an id. and then in the allotments table you could have 365 rows representing days of the week and a column with the package id available for that day? That way when changing the package available for that day all you are doing is changing one integer and not 5-6 rows. And if you need to change it to a package that does not exist yet you just create it and add that id to the table?

    I have a feeling that this seems to simple and I have mis understood your schema.
  18. #10
  19. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by drumn4life0789
    This is the part that seems like it could be fixed. I am kind of confused on all of it because I haven't really tackled a project of this caliber but it seems as if this table would be full of repetitive data.

    Would it not be easier to have the possible packages in one table with an id. and then in the allotments table you could have 365 rows representing days of the week and a column with the package id available for that day? That way when changing the package available for that day all you are doing is changing one integer and not 5-6 rows. And if you need to change it to a package that does not exist yet you just create it and add that id to the table?

    I have a feeling that this seems to simple and I have mis understood your schema.
    Hey due, here I explain a bit more:

    Each package could have a different value (rate, min_number_of_nights, status,...) for every single day of the year (for next 5 years).

    Each package should have 365 rows per year in allotments table so the unique value (day_date, rate, package_id, status,..) can be placed there.

    The other thing is that hotels ARE allowed to assign each day of the year as different season. FOr example 2012-01-01 can be high season, 2012-01-02 can be low season and so on and so far.

    How do can I show different rates for every day of the year without specifying day_date and package_id?

  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    78
    Rep Power
    0
    stop casually insulting dan
    I was offhanded complimenting...

    Your DB schema is not very normalized, as posted before. Sure it works, but in a crappy manner, as you are storing, ok, whatever

    Did you notice the monstrous loop and queries?
    that is big red flag something is amiss...

    Looks to me like you have an allotment row for any room at any hotel for any pkg????????

    ...but this one they specifically asked for 365 rows in allotments table per package.
    Ok, the DB schema is not good, when they ask for that.

    Changing allotments value per package effects all the values for all the other packages linked to that room in allotments table.
    What? The DB design is fundamentally bad, and you need to fix DB, or chase your tail, to put more lipstick on a pretty ugly pig?

    If you need a DB design that does this and more, I can help you. Though the legacy data migration to new design, is a bear.

    How many systems use this DB? How many ppl can update an allot that needs to update other allot pkg???

    Comments on this post

    • ManiacDan disagrees : Looks to me like you have no reading comprehension???????
  22. #12
  23. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,866
    Rep Power
    6351
    Well the first way to make this operation a little quicker is to only perform operations where there is a change. I don't see where you process the POST data, but if you only change the database rows for which the post data actually changed, you should be a lot better off.

    Now as for your humongous loop:

    1) args_other_packages is wrong, it doesn't specify the same keys as $sql_other_packages requires. I don't know how this code is functioning as written.

    2) You can combine sql_other_packages and sql_other_packages_all into a single query with a JOIN condition, removing thousands of queries from this page.

    3) You execute sql_other_packages_all twice for no reason whatsoever. If you need the row count AND the data, return the data and check count($other_packages_all), don't run the query twice.

    4) I bet you can combine all these update statements into a single query, but Rudy would be best able to handle that syntax.

    5) Is the code you pasted inside yet another loop? I see $row with no reference to where it was created.

    Comments on this post

    • Northie agrees : sensible points
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  24. #13
  25. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by ManiacDan
    Well the first way to make this operation a little quicker is to only perform operations where there is a change. I don't see where you process the POST data, but if you only change the database rows for which the post data actually changed, you should be a lot better off.

    Now as for your humongous loop:

    1) args_other_packages is wrong, it doesn't specify the same keys as $sql_other_packages requires. I don't know how this code is functioning as written.

    2) You can combine sql_other_packages and sql_other_packages_all into a single query with a JOIN condition, removing thousands of queries from this page.

    3) You execute sql_other_packages_all twice for no reason whatsoever. If you need the row count AND the data, return the data and check count($other_packages_all), don't run the query twice.

    4) I bet you can combine all these update statements into a single query, but Rudy would be best able to handle that syntax.

    5) Is the code you pasted inside yet another loop? I see $row with no reference to where it was created.
    I agree a fair bit of this is caused by lack of query writing skills. I am currently working with Rudy's support.

    Yes the code I pasted is inside another loop. I believe that part can also be fixed with the help of built-in mysql date functions. At the moment I have a calendar table which contains id, day_date from now till next 10 years. A lot of calculations use that table. I am sure it is something that I can use using mysql date function somehow.

    What I crave when writing this is using "SELECT, UPDATE if NULL or ignore if not NULL" query. I see Rudy mentioning some common date functions in chapter 9 SimplySql I'm just about to read. I am pretty sure that can get rid of some of these loops.

    Thank you

    Edit: Incase you wondered, this is where it all happens:

    PHP Code:
    $sql_check = "SELECT 
      annual_calendar.day_date AS DD
    , annual_calendar.day_of_the_week AS DOW
    , Q1.RATE AS RATE
    , Q1.number_of_rooms AS number_of_rooms
    , Q1.package_id
      FROM 
      annual_calendar 
      LEFT OUTER 
      JOIN 
      (SELECT allotments_new.day_rate AS RATE, allotments_new.number_of_rooms, day_date, package_id
        FROM allotments_new
        WHERE allotments_new.package_id =:package_id
        GROUP BY day_date) AS Q1 
        ON Q1.day_date = annual_calendar.day_date
        
        WHERE annual_calendar.day_date <=:next_year 
        AND 
        annual_calendar.day_date>=:today 
        GROUP BY annual_calendar.day_date
        ORDER BY `annual_calendar`.`day_date` ASC";
    $next_year = $this_year+1;
    $args_check = array('next_year'=>$next_year."-01-01", 'today'=>$this_year."-01-01", 'package_id'=>$this_package);
    $data = DB::Load()->Execute($sql_check,$args_check)->returnArray();
    //$q = DB::Load()->formatSQL($sql_check,$args_check);
    foreach($data as $i => $row)
        {
            $date_to_check = explode('-',$row['DD']);
            $date_to_check[2]."-".$date_to_check[1]."-".$date_to_check[0];
            if($date_to_check[2]<10)
                {
                    $day_without_zero = substr($date_to_check[2], 1);    
                }
            else
                {
                    $day_without_zero = $date_to_check[2];    
                }
            $day_without_zero = $day_without_zero-1;
                
            
            if($date_to_check[1]==1)
                {
                    $the_month=$_POST['jan'];
                }
            if($date_to_check[1]==2)
                {
                    $the_month=$_POST['feb'];
                }
            if($date_to_check[1]==3)
                {
                    $the_month=$_POST['mar'];
                }
            if($date_to_check[1]==4)
                {
                    $the_month=$_POST['apr'];
                }
            if($date_to_check[1]==5)
                {
                    $the_month=$_POST['may'];
                }
            if($date_to_check[1]==6)
                {
                    $the_month=$_POST['jun'];
                }
            if($date_to_check[1]==7)
                {
                    $the_month=$_POST['jul'];
                }
            if($date_to_check[1]==8)
                {
                    $the_month=$_POST['aug'];
                }
            if($date_to_check[1]==9)
                {
                    $the_month=$_POST['sep'];
                }
            if($date_to_check[1]==10)
                {
                    $the_month=$_POST['oct'];
                }
            if($date_to_check[1]==11)
                {
                    $the_month=$_POST['nov'];
                }
            if($date_to_check[1]==12)
                {
                    $the_month=$_POST['dec'];
                }
                    
                        
                
                    
            if($_GET['rates_or_allotments']!='allotments')
                {
                    if($row['RATE']==NULL)
                        {
                            if($the_month[$day_without_zero]<1)
                                {
                                    $the_month[$day_without_zero]=0;
                                }
                            $sql_update = "INSERT INTO allotments_new (day_rate, day_date, package_id, room_id, hotel_id)
                                          VALUES (:day_rate, :day_date, :package_id, :room_id, :hotel_id)";
                                          
                            $args_update = array('hotel_id'=>$this_hotel, 'day_rate'=>$the_month[$day_without_zero], 'day_date'=>$row['DD'], 'package_id'=>$this_package, 'room_id'=>$this_room_id, 'hotel_id'=>$this_hotel);
                            DB::Load()->Execute($sql_update,$args_update);
                            $pp=DB::Load()->formatSQL($sql_update,$args_update);
                            $code=1;
                        }
                    else
                        {
                            //UPDATE 
                            if($the_month[$day_without_zero]<1)
                                {
                                    $the_month[$day_without_zero]=0;
                                }
                            $sql_update = "UPDATE allotments_new
                                          SET day_rate=:day_rate
                                          WHERE day_date=:day_date AND
                                          room_id=:room_id AND 
                                          package_id=:package_id
                                          ";
                            $args_update = array('day_rate'=>$the_month[$day_without_zero], 'day_date'=>$row['DD'], 'room_id'=>$this_room_id, 'package_id'=>$this_package);
                            DB::Load()->Execute($sql_update,$args_update);
                            $printers = DB::Load()->formatSQL($sql_update,$args_update);
                            $code=2;
                        }
                }
            if($_GET['rates_or_allotments']=='allotments')
                {
                    if($row['number_of_rooms']==NULL)
                        {
                            //INSERT
                            if($the_month[$day_without_zero]<1)
                                {
                                    $the_month[$day_without_zero]=0;
                                }
                            $sql_update = "INSERT INTO allotments_new (number_of_rooms, day_date, package_id, room_id, hotel_id)
                                          VALUES (:number_of_rooms, :day_date, :package_id, :room_id)";
                                          
                            $args_update = array('hotel_id'=>$this_hotel, 'number_of_rooms'=>$the_month[$day_without_zero], 'day_date'=>$row['DD'], 'package_id'=>$this_package, 'room_id'=>$this_room_id);
                            DB::Load()->Execute($sql_update,$args_update);
                            $print_for = DB::Load()->formatSQL($sql_update,$args_update);
                            $code=3;
                            
                            
                            
                            
    ////Check if other packages have allotments
    $sql_other_packages = "SELECT id FROM packages WHERE room_id=:room_id AND id<>:id";
    $args_other_packages = array('room_id'=>$this_room_id,'package_id'=>$this_package);
    $other_packages = DB::Load()->Execute($sql_other_packages,$args_other_packages)->returnArray();


    foreach($other_packages as $values=> $other_packages_row)
        {
            //Check allotments table for this package    
            $sql_other_packages_all = "SELECT id FROM allotments_new WHERE package_id=:package_id AND day_date=:day_date";
            $args_other_packages_all = array('day_date'=>$row['DD'],'package_id'=>$other_packages_row['id']);
            $other_packages_all = DB::Load()->Execute($sql_other_packages_all,$args_other_packages_all)->returnNumAffectedRows();
            //If exists
            if($other_packages_all==1)
                {
                    $this_other_package_id = DB::Load()->Execute($sql_other_packages_all,$args_other_packages_all)->returnArray();
                    $sql_other_packages_all_update = "UPDATE allotments_new SET number_of_rooms=:number_of_rooms,
                                                      status=:status WHERE day_date=:day_date AND package_id=:package_id ";
                    if($the_month[$day_without_zero]>0)
                        {
                            $status='b';    
                        }
                    else
                        {
                            $status = 'r';    
                        }
                    
                    $args_update = array('status'=>$status,'number_of_rooms'=>$the_month[$day_without_zero], 
                    'day_date'=>$row['DD'], 'package_id'=>$other_packages_row['id']);
                    DB::Load()->Execute($sql_other_packages_all_update,$args_other_packages_all_update)->returnNumAffectedRows();
                }
            else
                {
                    //Insert
                    
                    
                }
            
        }
    ////Check if other packages have allotments
                        }
                    else
                        {
                            //UPDATE 
                            if($the_month[$day_without_zero]<1)
                                {
                                    $the_month[$day_without_zero]=0;
                                }
                            $sql_update = "UPDATE allotments_new
                                          SET number_of_rooms=:number_of_rooms,
                                          status=:status
                                          WHERE day_date=:day_date AND
                                          room_id=:room_id 
                                          ";
                            if($the_month[$day_without_zero]>0)
                                {
                                    $status='b';    
                                }
                            else
                                {
                                    $status = 'r';    
                                }
                            $args_update = array('status'=>$status,'number_of_rooms'=>$the_month[$day_without_zero], 'day_date'=>$row['DD'], 'room_id'=>$this_room_id);
                            DB::Load()->Execute($sql_update,$args_update);
                            $print_for = DB::Load()->formatSQL($sql_update,$args_update);
                            $code=4;
                            
                        }
                    
                }
        }
                    
                    //
                    $edit_successful=true;?><?php    
                
    }

          
    ?>
    Last edited by zxcvbnm; June 19th, 2012 at 07:29 PM.
  26. #14
  27. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    The issue here is:

    By looking at this you can see that there are approximately 365 posted values by the user and each of these values can be different from each other, so update on every row is compulsory and it is not an option.

    I thought I could use something like this query, avoid using 'annual_calendar table' and somehow do a select update, but I can't as the post values vary for each day!

    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"

    So I wonder if loop in inevitable in this case! I am going to post a similar thing in mysql forum maybe I hear from Rudy on this.

    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 )
    Last edited by zxcvbnm; June 21st, 2012 at 10:12 PM.
  28. #15
  29. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,866
    Rep Power
    6351
    But are they different from what you already had? that was my point. If the user opens this page and changes one day, you do this loop and all its queries 365 times.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo