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

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,574
    Rep Power
    171

    Another query in loop situation. Is there a better way to do this with php & mysql


    I don't have enough skills to do this within 1 query. Please guide if you know how to. Many thanks:

    PHP Code:
    $query "
    SELECT hotelname,
           allotments_new.minimum_number_of_nights AS mn,
           destinationname,
           day_rate,
           packages.type                           AS PT,
           packages.id                             AS PID,
           allotments_new.status                   AS ***,
           hotels.hotel_id                         AS HID,
           hotel_photos.photo
    FROM   allotments_new
           INNER JOIN hotels
                   ON hotels.hotel_id = allotments_new.hotel_id
                      AND hotels.status = 'active'
           INNER JOIN packages
                   ON allotments_new.package_id = packages.id
                      AND packages.status = 'active'
           INNER JOIN destinations
                   ON hotels.destination_id = destinations.destination_id
           INNER JOIN hotel_photos
                   ON hotel_photos.hotel_id = hotels.hotel_id
    WHERE  day_date = '"
    .date('Y-m-d')."'
           AND number_of_rooms > 0
           AND allotments_new.status = 'b'
    GROUP  BY allotments_new.hotel_id
    ORDER  BY package_order,
              hotels.hotel_id
    LIMIT  8
    "
    ;
    $packages DB::Load()->Execute($query)->returnArray();
    $count=0;
    foreach(
    $packages as $val=>$row)
        {
            
    $hotel_ids .= $row['HID'].',';
            
    $count++;
            if(
    $row['mn']>1)
                {
                    
    $class "line-through";    
                    
    $checkout date('Y-m-d'strtotime('+ '.$row['mn'].' day'));
                    
    $second_query "
    SELECT Sum(day_rate)                           AS TOTAL,
           allotments_new.minimum_number_of_nights AS mn,
           day_rate,
           allotments_new.status                   AS ***
    FROM   allotments_new
    WHERE  day_date >= '"
    .date('Y-m-d')."' 
           AND day_date < '"
    .$checkout."'
           AND number_of_rooms > 0
           AND allotments_new.status = 'b'
           AND allotments_new.package_id = "
    .$row['PID']."  
                                             "
    ;
                    
    $total DB::Load()->Execute($second_query)->returnArray();                        
                    
    $TOTAL=$total[0]['TOTAL'];
                }
            else
                {
                    
    $TOTAL=$row['day_rate'];
                    
    $checkout date('Y-m-d'strtotime('+ 1 day'));
                    
    $class "";        
                }
            if(
    $row['PT']=='Stay 2 Pay 1')
                {
                    
    $TOTAL$TOTAL/2;
                }
            
    $TOTAL number_format($TOTAL);        
            
    $todays_hot_deals[]=array('hotelname'=>$row['hotelname'],'hotel_id'=>$row['HID'], 'TOTAL'=>$TOTAL'mn'=>$row['mn'], 'photo'=>$row['photo'], 'destinationname'=>$row['destinationname'], 'PID'=>$row['PID']);    
        } 
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,692
    Rep Power
    6351
    You'd be better off posting your table definitions and what you need in the appropriate database forum.
    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.

IMN logo majestic logo threadwatch logo seochat tools logo