#1
  1. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221

    Is this SQL in loop avoidable?


    Hi;

    I have a feeling I'll have to post this in Rudy's neighborhood.

    But always good to try it here first:

    Code:
    SELECT launch_plcs.id,
           launch_plcs.launch_id,
           launch_plcs.user_id,
           launch_plcs.release_after_days_evergreen,
           launch_plcs.page_title_for_browser,
           launch_plcs.title,
           launch_plcs.youtube_video,
           launch_plcs.broadcast_status,
           launch_plcs.broadcast_access_date,
           launch_plcs.main_headline
    FROM   launch_plcs
           INNER JOIN launch_launches
                   ON launch_launches.id = launch_plcs.launch_id
    WHERE  launch_plcs.launch_id = ?
    GROUP  BY launch_plcs.id
    PHP Code:
    $query $this->db->query($sql, array('launch_id' =>$id));
    $this->data['plcs'] = $query->result_array(); 
    PHP Code:
    foreach($plcs as $data=>$row
      { 
        
    //Next PLC release_after_days_evergreen 
          
    $sql "SELECT release_after_days_evergreen, id FROM launch_plcs WHERE id > ".$row['id'].
          AND launch_id =  "
    .$row['launch_id']." ORDER BY ID ASC LIMIT 1"
          
    $next_plc $this->db->query($sql); 
          if(
    $next_plc->num_rows()>0
            { 
              
    $next_plc_releas_after $next_plc->result_array()[0]['release_after_days_evergreen']; 
            } 
          else 
    //This is the last plc, so get open_offer_x_days_after_last_plc from launch_launches table  
            

              
    $sql "SELECT open_offer_x_days_after_last_plc FROM launch_launches WHERE id = ".$row['launch_id']; 
              
    $next_plc $this->db->query($sql); 
              
    $next_plc_releas_after $next_plc->result_array()[0]['open_offer_x_days_after_last_plc']; 
            }   
         
        if(
    $previous_plcs==0
          { 
            
    $plcs[$data]['from']=$plcs[$data]['release_after_days_evergreen']; 
          } 
        else 
          { 
            
    $plcs[$data]['from']=$previous_plcs+$plcs[$data]['release_after_days_evergreen']; 
          }   
        
    //Next PLCs Wait: 
        
    $plcs[$data]['to']=$plcs[$data]['from']+$next_plc_releas_after
        
    $previous_plcs $previous_plcs $row['release_after_days_evergreen']; 
      } 

    Tables

    Code:
    CREATE TABLE `launch_plcs` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `launch_id` int(11) NOT NULL,
     `user_id` int(11) NOT NULL,
     `release_after_days_evergreen` int(11) NOT NULL DEFAULT '1',
     `page_title_for_browser` varchar(255) NOT NULL,
     `title` varchar(255) NOT NULL,
     `youtube_video` varchar(255) NOT NULL,
     `broadcast_status` int(11) DEFAULT NULL,
     `broadcast_access_date` date NOT NULL,
     `evergreen_status` int(11) DEFAULT '1',
     `sub_headline` varchar(255) NOT NULL,
     `main_headline` varchar(255) NOT NULL,
     `magic_content` varchar(255) NOT NULL,
     `magic_content_2` varchar(255) NOT NULL DEFAULT 'no',
     `magic_content_3` text NOT NULL,
     `magic_timer` varchar(255) NOT NULL DEFAULT 'no',
     `show_magic_content_after_seconds` int(11) NOT NULL,
     `video_frame_bg` varchar(255) NOT NULL,
     `show_launch_nav` int(11) NOT NULL DEFAULT '0',
     `show_offer_open_timer` varchar(255) NOT NULL DEFAULT 'yes',
     `show_comments` int(11) NOT NULL DEFAULT '1',
     `comment_type` varchar(255) NOT NULL DEFAULT 'manual',
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `launch_launches` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `launch_status` int(11) NOT NULL DEFAULT '1',
     `title` varchar(255) NOT NULL,
     `launch_type` varchar(255) NOT NULL,
     `user_id` int(11) NOT NULL,
     `product_id` int(11) NOT NULL,
     `open_offer_x_days_after_last_plc` int(11) NOT NULL DEFAULT '6',
     `offer_open_date` date NOT NULL,
     `sales_page` varchar(500) NOT NULL,
     `offer_closed_page` varchar(255) NOT NULL,
     `how_many_days_keep_sales_page_open` int(11) NOT NULL DEFAULT '4',
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
    Thanks
    Last edited by English Breakfast Tea; April 8th, 2018 at 07:01 PM.
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,885
    Rep Power
    9646
    The crux of the question is about writing a query and the PHP is easy to understand so yeah, the MySQL forum would be a good place for this.

    So I see three queries: the first one, and the two in the code. I think you can combine them all. The "ORDER BY ID ASC LIMIT 1" is the main issue - it implies there are multiple records and you only want the first one.
  4. #3
  5. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,271
    Rep Power
    4193
    Code:
    if($previous_plcs==0) 
          { 
            $plcs[$data]['from']=$plcs[$data]['release_after_days_evergreen']; 
          } 
        else 
          { 
            $plcs[$data]['from']=$previous_plcs+$plcs[$data]['release_after_days_evergreen']; 
          }
    This if condition is unnecessary. $plcs[$data]['release_after_days_evergreen']; and $previous_plcs+$plcs[$data]['release_after_days_evergreen']; are equivalent when $previous_plcs is zero.


    I don't see any reason for you to be doing a query in your loop, Just add the ORDER BY ID ASC clause to your initial query to fetch all your plcs in the proper order and fetch them into your array. After that you can just loop that array to calculate your from/to values.

    Code:
    //Using PDO API, convert to your interface of choice.
    $sql = '
    SELECT launch_plcs.id,
           launch_plcs.launch_id,';
           launch_plcs.user_id,
           launch_plcs.release_after_days_evergreen,
           launch_plcs.page_title_for_browser,
           launch_plcs.title,
           launch_plcs.youtube_video,
           launch_plcs.broadcast_status,
           launch_plcs.broadcast_access_date,
           launch_plcs.main_headline,
           launch_launches.open_offer_x_days_after_last_plc
    FROM   launch_plcs
           INNER JOIN launch_launches
                   ON launch_launches.id = launch_plcs.launch_id
    WHERE  launch_plcs.launch_id = ?
    ORDER BY launch_plcs.id ASC
    ';
    
    $stmt = $db->prepare($sql);
    $stmt->execute([123]);
    $plcList = $stmt->fetchAll();
    
    $totalOffset = 0;
    $lastIdx = count($plcList) - 1;
    for ($idx=0; $idx <= $lastIdx; $idx++){
        if ($idx === $lastIdx){
            $offset = $plcList[$idx]['open_offer_x_days_after_last_plc'];
        } else {
            $offset = $plcList[$idx+1]['release_after_days_evergreen'];
        }
    
        $plcList[$idx]['from'] = $totalOffset + $plcList[$idx]['release_after_days_evergreen'];
        $plcList[$idx]['to'] = $plcList[$idx]['from'] + $offset;
        $totalOffset += $offset;
    }
    Last edited by kicken; April 9th, 2018 at 10:14 AM.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by English Breakfast Tea
    I have a feeling I'll have to post this in Rudy's neighborhood.
    sorry, friend, i don't do php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,885
    Rep Power
    9646
    Originally Posted by r937
    sorry, friend, i don't do php
    I moved it from PHP so my bad. I thought the question would be primarily SQL but it seems it went in a bit of a PHP direction after all.
  10. #6
  11. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Originally Posted by kicken
    Code:
    if($previous_plcs==0) 
          { 
            $plcs[$data]['from']=$plcs[$data]['release_after_days_evergreen']; 
          } 
        else 
          { 
            $plcs[$data]['from']=$previous_plcs+$plcs[$data]['release_after_days_evergreen']; 
          }
    This if condition is unnecessary. $plcs[$data]['release_after_days_evergreen']; and $previous_plcs+$plcs[$data]['release_after_days_evergreen']; are equivalent when $previous_plcs is zero.


    I don't see any reason for you to be doing a query in your loop, Just add the ORDER BY ID ASC clause to your initial query to fetch all your plcs in the proper order and fetch them into your array. After that you can just loop that array to calculate your from/to values.

    Code:
    //Using PDO API, convert to your interface of choice.
    $sql = '
    SELECT launch_plcs.id,
           launch_plcs.launch_id,';
           launch_plcs.user_id,
           launch_plcs.release_after_days_evergreen,
           launch_plcs.page_title_for_browser,
           launch_plcs.title,
           launch_plcs.youtube_video,
           launch_plcs.broadcast_status,
           launch_plcs.broadcast_access_date,
           launch_plcs.main_headline,
           launch_launches.open_offer_x_days_after_last_plc
    FROM   launch_plcs
           INNER JOIN launch_launches
                   ON launch_launches.id = launch_plcs.launch_id
    WHERE  launch_plcs.launch_id = ?
    ORDER BY launch_plcs.id ASC
    ';
    
    $stmt = $db->prepare($sql);
    $stmt->execute([123]);
    $plcList = $stmt->fetchAll();
    
    $totalOffset = 0;
    $lastIdx = count($plcList) - 1;
    for ($idx=0; $idx <= $lastIdx; $idx++){
        if ($idx === $lastIdx){
            $offset = $plcList[$idx]['open_offer_x_days_after_last_plc'];
        } else {
            $offset = $plcList[$idx+1]['release_after_days_evergreen'];
        }
    
        $plcList[$idx]['from'] = $totalOffset + $plcList[$idx]['release_after_days_evergreen'];
        $plcList[$idx]['to'] = $plcList[$idx]['from'] + $offset;
        $totalOffset += $offset;
    }
    Hey Kicken.

    We get diffrent results --> Here.
  12. #7
  13. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,271
    Rep Power
    4193
    Well, then fix it. I don't have your data so I didn't test anything, apparently I must have made a mistake when rewriting the code.

    Looking over the code closer I'd guess I calculated the $totalOffset wrong and it should instead be
    Code:
    $totalOffset += $plcList[$idx]['release_after_days_evergreen'];
    But again, no tested due to no real data.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  14. #8
  15. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Originally Posted by kicken
    Code:
    $totalOffset += $plcList[$idx]['release_after_days_evergreen'];
    But again, no tested due to no real data.
    results look good: I like to learn how you own these arrays.

    Visual: Chek it here .

    Your code:

    PHP Code:
    $totalOffset 0;
    $lastIdx count($plcs) - 1;
    for(
    $idx=0$idx <= $lastIdx$idx++)
        {
            if(
    $idx === $lastIdx)
                {
                    
    $offset $plcs[$idx]['open_offer_x_days_after_last_plc'];
                }
            else
                {
                    
    $offset $plcs[$idx+1]['release_after_days_evergreen'];
                }

        
        echo 
    "PLC:".$plcs[$idx]['id'];
        echo 
    "<br />";
        echo 
    "Offset:".$offset;
        echo 
    "<br />";
        echo 
    "From";
        echo 
    "(".$totalOffset."+".$plcs[$idx]['release_after_days_evergreen']."):";
        echo 
    $plcs[$idx]['from'] = $totalOffset $plcs[$idx]['release_after_days_evergreen'];
        echo 
    "<br />To:";
        echo 
    $plcs[$idx]['to'] = $plcs[$idx]['from'] + $offset;
        echo 
    "<br />totalOffset:";
        echo 
    $totalOffset += $plcList[$idx]['release_after_days_evergreen'];
        echo 
    "<hr />";

    If you got a few minutes plz let me know your thoughts on this approach and how you'd improve it.

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo