trjhtr

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");
 
 
}