Geoff_S
Reputation: 5107
Insert/update script not updating at all
I have a script that runs and inserts as it should but it's not updating, at least not on the initial batch. If I run the script, the first and main SELECT query gets about 800 records. It inserts and performs the logic I have below (multiple if, elseif and for loops at bottom of script) but I need it to process for each individual record from the initial select.
So: Select all records, for first record perform select on destination table to see if the sku exists for this dealer. IF not, insert, if it does then update.
The problem is, when I run this for the initial data porting, the destination table is empty so it's not checking against anything. I added a foreach in my code to say "For every single record, perform the select/check on destination table, then perform logic to determine if insert or update"
I included a fiddle below as well but using these values to select from:
(12345, 133, 1234, '2018-02-02', 10, 10),
(12345, 133, 1235, '2018-02-02', 10, 10),
(12345, 133, 1236, '2018-02-02', 10, 10),
(12346, 133, 1234, '2018-03-02', 30, 10);
The logic is that for every dealer, they should records for each sku equal to their store number at most.
With the data above, It should insert 10 records for the first sku, 10 for the 2nd sku, 10 for the 3rd sku and then since the 4th sku is the same as the 1st and the date is before the expire_date, we would update that row 30 times, based on the quantity being more than the storefronts.
The logic is weird, I know. But that part works, I just need help with the foreach logic to make sure I'm performing the check for each record from the initial select so that it can actually determine if it needs to update.
I can clarify as needed.
$detailStatCheck = "
SELECT
orderNum,
custNum,
sku,
shipDate,
quantity,
stores
FROM orders
WHERE custNum= 133";
try {
$detailCheck = $DB2conn->prepare($detailStatCheck);
$detailRslt = $detailCheck->execute();
$count2 = $detailCheck->fetch();
print_r($count2);
} catch(PDOException $ex) {
echo "QUERY ONE FAILED!: " .$ex->getMessage();
}
$existingCheck = "
SELECT
sku,
custNum,
expire_date,
quantity
FROM individualProducts
WHERE customer = :customer
AND sku = :sku
";
$updatePlacement = "
UPDATE individualProducts
SET expire_date = DATE_ADD(DATE_FORMAT(CONVERT(current_date(), CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY),
quantity = :quantity
";
$insertPlacement = "
INSERT ignore INTO individualProducts (sku, customer, start_date, expire_date, locations, quantity)
VALUES(
:sku,
:customer,
date_add(convert(:SHIPDATE,date), interval 7 day) as start_date,
date_add(convert(:SHIPDATE,date), interval 127 day) as expire_date,
:stores,
:quantity)
";
$checkExisting = $MysqlConn->prepare($existingCheck);
$insert = $MysqlConn->prepare($insertPlacement);
$update = $MysqlConn->prepare($updatePlacement);
$orders = [];
while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) {
$orders[] = $row2;
foreach($orders as $order){
$values = [
":customer" => $row2["customer"],
":shipDate" => $row2["shipDate"],
":stores" => $row2["stores"],
":quantity" => $row2["quantity"],
":sku" => $row2["sku"],
];
$existingRslt = $checkExisting->execute($values);
$count3 = $checkExisting->fetch(PDO::FETCH_ASSOC);
//if no existing records, insert. If quantity is greater than stores, insert records to equal store number. If quantity is less than stores, insert records to equal quantity
if(empty($count3)){
print_r("Inserting");
if($row2["quantity"] > $row2["stores"]){
for($i=0; $i<$row2["stores"]; $i++){
try{
$insertRslt = $insert->execute($values);
}catch(PDOException $ex){
echo "QUERY THREE FAILED!!!: " . $ex->getMessage();
}
}
}elseif($row2["quantity"] < $row2["stores"]){
for($i=0; $i<$row2["quantity"]; $i++){
try{
$insertRslt = $insert->execute($values);
}catch(PDOException $ex){
echo "QUERY THREE FAILED!!!: " . $ex->getMessage();
}
}
}
}elseif(!empty($count3) && curdate() < $count3['expire_date']){
print_r("Updating");
if($row2["QUANTITY"] > $row2["STORES"]){
for($i=0; $i<$row2['STORES']; $i++){
try{
$updateRslt = $update->execute($values3);
}catch(PDOException $ex){
echo "QUERY FIVE FAILED!!!: " . $ex->getMessage();
}
}
}elseif($row2["QUANTITY"] < $row2["STORES"]){
for($i=0; $i<$row2['QUANTITY']; $i++){
try{
$updateRslt = $update->execute($values3);
}catch(PDOException $ex){
echo "QUERY FIVE FAILED!!!: " . $ex->getMessage();
}
}
}
}else{
print_r("Inserting");
if($row2["quantity"] > $row2["stores"]){
for($i=0; $i<$row2["stores"]; $i++){
try{
$insertRslt = $insert->execute($values);
}catch(PDOException $ex){
echo "QUERY THREE FAILED!!!: " . $ex->getMessage();
}
}
}elseif($row2["quantity"] < $row2["stores"]){
for($i=0; $i<$row2["quantity"]; $i++){
try{
$insertRslt = $insert->execute($values);
}catch(PDOException $ex){
echo "QUERY THREE FAILED!!!: " . $ex->getMessage();
}
}
}
}
$MysqlConn = null;
Here's the fiddle: http://sqlfiddle.com/#!9/0efffb
EDIT:
Create table syntax
create table orders(
orderNum int(11),
custNum int(11),
sku int(11),
shipDate DATE,
quantity int(11),
stores int(11)
);
insert into orders
values (12345, 133, 1234, '2018-02-02', 10, 10),
(12345, 134, 1234, '2018-02-02', 10, 10),
(12345, 135, 1234, '2018-02-02', 10, 10),
(12346, 133, 1234, '2018-03-02', 30, 10);
create table individualProducts(
sku int(11),
customer int(11),
start_date DATE,
expire_date DATE,
locations int(11),
quantity int(11)
);
Upvotes: 0
Views: 156
Answers (1)
PajuranCodes
Reputation: 471
Probably you know that, but, if you are more interested in regard of error handling, you could read this (general) and this (for PDO) article.
It was still hard to understand the update phase. I just hope, you can at least take some ideas from my code. Don't accept my answer. I posted it only in order to help you a bit. Ask me anything if you want.
index.php
<?php
require 'connections.php';
/*
* ====================
* Get the orders list.
* ====================
*/
$sql = 'SELECT
orderNum,
custNum,
sku,
shipDate,
quantity,
stores
FROM orders
WHERE custNum = :custNum';
$ordersStatement = $DB2conn->prepare($sql);
$ordersStatement->execute([
':custNum' => 133,
]);
$orders = $ordersStatement->fetchAll(PDO::FETCH_ASSOC);
/*
* ======================================================
* Prepare the sql for selecting the individual products.
* ======================================================
*/
$sql = 'SELECT
id,
expire_date,
quantity
FROM individualProducts
WHERE
sku = :sku
AND customer = :customer
ORDER BY id DESC';
$productsStatement = $MysqlConn->prepare($sql);
/*
* ====================================================
* Prepare the sql for inserting an individual product.
* ====================================================
*/
$sql = 'INSERT INTO individualProducts (
sku,
customer,
start_date,
expire_date,
locations,
quantity
) VALUES (
:sku,
:customer,
DATE_ADD(CONVERT(:shipDate, date), INTERVAL 7 DAY),
DATE_ADD(CONVERT(:shipDate, date), INTERVAL 127 DAY),
:locations,
:quantity
)';
$insertProductStatement = $MysqlConn->prepare($sql);
/*
* ===================================================
* Prepare the sql for updating an individual product.
* ===================================================
*/
$sql = 'UPDATE individualProducts
SET
expire_date = DATE_ADD(DATE_FORMAT(CONVERT(current_date(), CHAR(20)), "%Y-%m-%d"),INTERVAL 129 DAY),
quantity = :quantity
WHERE id = :id';
$updateProductStatement = $MysqlConn->prepare($sql);
/*
* ==================================
* Update/insert individual products.
* ==================================
*/
foreach ($orders as $orderItem) {
$orderItemCustomer = $orderItem['custNum'];
$orderItemSku = $orderItem['sku'];
$orderItemShipDate = $orderItem['shipDate'];
$orderItemQuantity = $orderItem['quantity'];
$orderItemStores = $orderItem['stores'];
/*
* =================================
* Get products by sku and customer.
* =================================
*/
$productsStatement->execute([
':sku' => $orderItemSku,
':customer' => $orderItemCustomer,
]);
$products = $productsStatement->fetchAll(PDO::FETCH_ASSOC);
if (!$products) { // No individual products found.
// How many products should be inserted?
$numberOfProductsToInsert = ($orderItemQuantity >= $orderItemStores) ? $orderItemStores : $orderItemQuantity;
// Insert the product $numberOfProductsToInsert times.
for ($i = 0; $i < $numberOfProductsToInsert; $i++) {
$insertProductStatement->execute([
':sku' => $orderItemSku,
':customer' => $orderItemCustomer,
':shipDate' => $orderItemShipDate,
':locations' => $orderItemStores,
':quantity' => $orderItemQuantity,
]);
}
} else { // Individual products found.
$numberOfProducts = count($products);
$numberOfUpdatedProducts = 0;
$currentDate = date('Y-m-d', time());
// How many products should be updated?
$numberOfProductsToUpdate = ($orderItemQuantity >= $orderItemStores) ? $orderItemStores : $orderItemQuantity;
$numberOfProductsToUpdate = ($numberOfProductsToUpdate >= $numberOfProducts) ? $numberOfProducts : $numberOfProductsToUpdate;
// Update each product (maximal $numberOfProducts).
foreach ($products as $product) {
if ($numberOfUpdatedProducts == $numberOfProductsToUpdate) {
break;
}
$productId = $product['id'];
$productExpireDate = $product['expire_date'];
if ($currentDate < $productExpireDate) {
$updateProductStatement->execute([
':quantity' => $orderItemQuantity,
':id' => $productId,
]);
$numberOfUpdatedProducts++;
}
}
}
}
connections.php
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');
define('CHARSET', 'utf8');
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER! */
/*
* Create a DB2 connection.
*/
$DB2conn = new PDO(
sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s', HOST, PORT, DATABASE, CHARSET)
, USERNAME
, PASSWORD
, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => TRUE,
PDO::ATTR_PERSISTENT => FALSE,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
/*
* Create a MySQL connection.
*/
$MysqlConn = new PDO(
sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s', HOST, PORT, DATABASE, CHARSET)
, USERNAME
, PASSWORD
, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => TRUE,
PDO::ATTR_PERSISTENT => FALSE,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
Create table syntaxes
CREATE TABLE `orders` (
`orderNum` int(11) DEFAULT NULL,
`custNum` int(11) DEFAULT NULL,
`sku` int(11) DEFAULT NULL,
`shipDate` date DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`stores` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `individualProducts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`sku` int(11) DEFAULT NULL,
`customer` int(11) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`expire_date` date DEFAULT NULL,
`locations` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Upvotes: 2
Related Questions
- PHP code can UPDATE MySQL database, but can't INSERT
- update and insert query not working from php
- PHP not updating MySQL Table
- php script not updating mysql row
- Why is my PHP / MySQL inserting instead of updating?
- PHP is updating instead of inserting
- mysql insert/update always inserting never updating
- PHP script not updating mysql table
- why is this not updating the MySQL database?
- PHP/SQL never updates only inserts