1
\$\begingroup\$

I have a portion of a script here, and the whole script works just fine.

However, I feel like it's inefficient and may work better in a class/function format. This is a script scheduled to run every night and go between databases to select records, compare them, pull data based on comparisons and then insert or update from there.

Again, it works fine and the logic is correct, but the script is sequential which probably isn't great.

I'm hoping for some tips or guidance on refactoring this and how it could be done better, using this portion as a guiding point. I'm hoping I can get some help on modifying this into function format and applying the same logic to the rest of my script.

$updateShipped = "
        UPDATE status
        set date_updated = current_date(), active = 1
        where order_id in ($placeholders)
    ";


    try{
                $updateStatus = $MysqlConn->prepare($updateShipped);
                $statUpdateRslt = $updateStatus->execute($order_ids);
                $count = $updateStatus->rowcount();
                }
                catch(PDOException $ex)
                {
                    echo "QUERY FAILED!: " .$ex->getMessage();
                }
            echo "Records Updated: " . $count . "\n";

    //Create prepared INSERT statement
    $insertPlacement = "
        INSERT ignore INTO table2 (id, group, customer, start, end, quantity, order_num)
        SELECT 
            id, 
            group, 
            :customer, 
            DATE_ADD(DATE_FORMAT(CONVERT(:date, CHAR(20)), '%Y-%m-%d'),INTERVAL 7 DAY) as start,
            DATE_ADD(DATE_FORMAT(CONVERT(:date, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY) as end, 
            :quantity,
            :order  
        FROM builds s  
        WHERE  s.build=:build AND s.mtrl=:mtrl AND s.col=:col
    ";

    //create update statement for necessary constraints
    $updatePlacement = "
        UPDATE table2
        SET end = DATE_ADD(DATE_FORMAT(CONVERT(current_date(), CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY)
    ";

    //perpare query to check for existing records that are expired
    $expiredCheck = "
        SELECT 
            id,
            cust,
            end
        FROM table2 p
            INNER JOIN builds s
                ON p.id = s.id
        WHERE p.cust = :cust
            AND   s.build = :build
            AND   s.mtrl = :mtrl
            AND   s.col = :col
            AND   p.order_num = :order
            AND   p.end <= current_date()
    ";

    //perpare query to check for existing records that are expired
    $validCheck = "
        SELECT 
            id,
            cust,
            end
        FROM table2 p
            INNER JOIN builds s
                ON p.id = s.id
        WHERE p.cust = :cust
            AND   s.build = :build
            AND   s.mtrl = :mtrl
            AND   s.col = :col
            AND   p.order_num = :order
            AND   p.end > current_date()
    ";

$checkExisting = $MysqlConn->prepare($expiredCheck);

$checkExistingValid = $MysqlConn->prepare($validCheck);

$insert = $MysqlConn->prepare($insertPlacement);

$insert = $MysqlConn->prepare($insertPlacement);

$update = $MysqlConn->prepare($updatePlacement);



    while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) {
        //print_r($count2);
        $values = [
            ":cust" => $row2["cust"],
            ":date" => $row2["date"],
            ":quantity" => $row2["quantity"],
            ":order" => $row2["order"],
            ":build" => $row2["build"],
            ":mtrl" => $row2["mtrl"],
            ":col" => $row2["col"],
        ];

        $values2 = [
            ":cust" => $row2["cust"],
            ":build" => $row2["build"],
            ":mtrl" => $row2["mtrl"],
            ":col" => $row2["col"],
            ":order" => $row2["order"],

        ];

        try{
            //Array will contain records that are expired
            $existingRslt = $checkExisting->execute($values2);
            $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC);

            //Array will contain records that are valid
            $existingVldRslt = $checkExistingValid->execute($values2);
            $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC);

            //print_r($count3);

        }catch(PDOException $ex){
                echo "QUERY FAILED!!!: " . $ex->getMessage();
        }



            // IF records do not exist, or records exist and today is after expiration date
            if(empty($count3) && empty($count4)){
                print_r("Inserting");
                for($i=0; $i<$row2["quantity"]; $i++) {  
                    try{
                        $insertRslt = $insert->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }

                }
            }elseif(!empty($count3)){
                print_r("Inserting");
                for($i=0; $i<$row2['quantity']; $i++){
                    try{
                        $insertRslt = $insert->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }
                }
            }elseif(!empty($count4)){
                print_r("updatin");
                for($i=0; $i<$row2['quantity']; $i++){
                    try{
                        $updateRslt = $update->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }
                }
            }else{
                die("No action taken");
            }
         }    
}
\$\endgroup\$
  • \$\begingroup\$ I don't want to discourage other people from trying to answer your question, but it seems to me that you just need a good tutorial on functions and object oriented programming. Explaining all you need to know, in an answer here, would require a complete course in PHP. So why not look for one, read, and learn. One advice: The best way to learn is to practice. Links: thebalance.com/websites-for-learning-php-2071891 This is perhaps not the answer you were hoping for, but I think it is a honest one. \$\endgroup\$ – KIKO Software Mar 20 '18 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.