This script worked fine yesterday, but today, since now in the table from which I initially select, there are now about 150,000 records, this did not help to say what I was extracting from null (). As far as I could say this, because I had too many records.
So, I fixed it by adding a limit on the initial request (1000) and this line:
$MysqlConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
This worked the first 15 or so times when I ran the script, but now it does not work and no insertions are made. I get an error from buffered_query as false, saying that it can start a buffered query while another is executing.
I haven't even used this before, not to mention getting this error. I'm sure my code can be optimized for this (I'm also very green when it comes to using PDO).
Perhaps someone can offer some insight:
$MysqlConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $MysqlConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); /*Delete records that were made placements more than 5 days ago*/ $deleteOld = ' DELETE FROM Order_status WHERE date_updated < current_date() - INTERVAL 5 DAY'; try{ $delete = $MysqlConn->prepare($deleteOld); $result = $delete->execute(); $count = $delete->rowcount(); echo "Records Deleted: " . $count . "\n"; } catch(PDOException $ex) { echo "QUERY FAILED!: " .$ex->getMessage(); } /*Placement process for orders already marked as Shipped*/ //PDO statement to select from order_status $ordStatSql = 'SELECT order_id, order_status, is_placement, date_updated FROM order_status WHERE order_status = "S" AND date_updated IS NULL order by order_id desc LIMIT 1000'; try{ $ordStat = $MysqlConn->prepare($ordStatSql); $result = $ordStat->execute(); } catch(PDOException $ex) { echo "QUERY FAILED!: " .$ex->getMessage(); } $order_ids = []; while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) { $order_ids[] = $row['order_id']; } if (count($order_ids) > 0) { $placeholders = implode(',', array_fill(0, count($order_ids), '?')); $detailStatCheck = " SELECT invnoc as INVOICE, fstatc as STATUS, cstnoc AS DEALER, framec AS FRAME, covr1c AS COVER, colr1c AS COLOR , extd2d AS SHIPDATE, orqtyc AS QUANTITY FROM GPORPCFL WHERE invnoc IN ($placeholders) "; try { $detailCheck = $DB2conn->prepare($detailStatCheck); $detailRslt = $detailCheck->execute($order_ids); $count2 = $detailCheck->fetch(); print_r($order_ids); print_r($count2); } catch(PDOException $ex) { echo "QUERY FAILED!: " .$ex->getMessage(); } //Create prepared INSERT statement $insertPlacement = " INSERT ignore INTO placements_new (sku_id, group_id, dealer_id, start_date, expire_date, locations, order_num) SELECT id, sku_group_id, :DEALER, DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 7 DAY) as start_date, DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY) as expire_date, :QUANTITY, :INVOICE FROM skus s WHERE s.frame=:FRAME AND s.cover1=:COVER AND s.color1=:COLOR "; //create update statement for necessary constraints $updatePlacement = " UPDATE placements_new SET expire_date = 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 sku_id, dealer_id, expire_date FROM placements_new p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.frame = :FRAME AND s.cover1 = :COVER AND s.color1 = :COLOR AND p.order_num = :INVOICE AND p.expire_date <= current_date() "; //perpare query to check for existing records that are expired $validCheck = " SELECT sku_id, dealer_id, expire_date FROM placements_new p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.frame = :FRAME AND s.cover1 = :COVER AND s.color1 = :COLOR AND p.order_num = :INVOICE AND p.expire_date > current_date() "; $updateShipped = ' UPDATE order_status S INNER JOIN placements_new N ON S.order_id = N.order_num set S.date_updated = current_date(); '; while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) { $values = [ ":DEALER" => $row2["DEALER"], ":SHIPDATE" => $row2["SHIPDATE"], ":QUANTITY" => $row2["QUANTITY"], ":INVOICE" => $row2["INVOICE"], ":FRAME" => $row2["FRAME"], ":COVER" => $row2["COVER"], ":COLOR" => $row2["COLOR"], ]; $values2 = [ ":DEALER" => $row2["DEALER"], ":FRAME" => $row2["FRAME"], ":COVER" => $row2["COVER"], ":COLOR" => $row2["COLOR"], ":INVOICE" => $row2["INVOICE"], ]; try{ //Array will contain records that are expired $checkExisting = $MysqlConn->prepare($expiredCheck); $existingRslt = $checkExisting->execute($values2); $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC); //Array will contain records that are valid $checkExistingValid = $MysqlConn->prepare($validCheck); $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)){ for($i=0; $i<$row2["QUANTITY"]; $i++) { try{ $insert = $MysqlConn->prepare($insertPlacement); $insertRslt = $insert->execute($values); }catch(PDOException $ex){ echo "QUERY FAILED!!!: " . $ex->getMessage(); } } }elseif(!empty($count3)){ for($i=0; $i<$row2['QUANTITY']; $i++){ try{ $insert = $MysqlConn->prepare($insertPlacement); $insertRslt = $insert->execute($values); }catch(PDOException $ex){ echo "QUERY FAILED!!!: " . $ex->getMessage(); } } }elseif(!empty($count4)){ for($i=0; $i<$row2['QUANTITY']; $i++){ try{ $update = $MysqlConn->prepare($updatePlacement); $updateRslt = $update->execute($values); }catch(PDOException $ex){ echo "QUERY FAILED!!!: " . $ex->getMessage(); } } }else{ die("No action taken"); } } try{ $updateStatus = $MysqlConn->prepare($updateShipped); $statUpdateRslt = $updateStatus->execute(); $count = $updateStatus->rowcount(); } catch(PDOException $ex) { echo "QUERY FAILED!: " .$ex->getMessage(); } echo "Records Updated: " . $count . "\n"; }