# Nightly script to update database

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

• 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. – KIKO Software Mar 20 '18 at 23:45