thenorman138 Posted March 5, 2018 Share Posted March 5, 2018 I'm trying to modify an if statement for an already working PDO statement. Basically, My insert is working properly with my existing code/statement. I'm currently checking for records existing based on a select query, and if they exist then I'm storing them. This works, and stores all records expected, so if I try to run the insert on the same data, it catches all the matches and doesn't insert anything. This is expected. However, when I modify my IF statement to add a check for the expire_date of any of those matched records, it seems to not work because it goes ahead and inserts all of the matched records, regardless of the date. This is the new IF statement I'm trying: if ( empty($count3) || strtotime($count3['expire_date']) < strtotime('now') Below is the code that currently works, but it's only saying 'if the record doesn't exist then insert`. I'm trying to modify it to say `if the record doesn't exist OR if it exists but the record's expire_date is before today, then insert` $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 "; //perpare query to check for existing records $dealerSkuCheck = " 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 "; 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{ $checkExisting = $MysqlConn->prepare($dealerSkuCheck); $existingRslt = $checkExisting->execute($values2); $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC); }catch(PDOException $ex){ echo "QUERY FAILED!!!: " . $ex->getMessage(); } //print_r($count3); // IF records do not exist, or records exist and today is after expiration date if(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(); } } } } Quote Link to comment Share on other sites More sharing options...
requinix Posted March 5, 2018 Share Posted March 5, 2018 Seems fine, except you should be using time() for the current time instead of strtotime(now). Are you sure expire_date has the values you expect it to have? But how about modifying the SELECT query to only return unexpired rows instead of checking for that in code? Quote Link to comment Share on other sites More sharing options...
thenorman138 Posted March 5, 2018 Author Share Posted March 5, 2018 To your first point I will try that modification but yes expire_date does have the values I expect. I'm pulling a sample and modifying certain dates for testing and it should catch, but I'll try using time(). To your 2nd point, Maybe I was confused but I was doing it that way to see if there were any existing records at all first and if not then insert, but then check for expire_date and insert based on it being passed. But if I modify like you said I guess it would satisfy both conditions? Quote Link to comment Share on other sites More sharing options...
thenorman138 Posted March 5, 2018 Author Share Posted March 5, 2018 I tried this but still the same issue: if(empty($count3) || $count3['expire_date'] < time()){ Quote Link to comment Share on other sites More sharing options...
thenorman138 Posted March 5, 2018 Author Share Posted March 5, 2018 @requinix I'm taking your advice to modify the query and pull unexpired records to update, and then do an '''else''' to insert. THanks! Quote Link to comment Share on other sites More sharing options...
thenorman138 Posted March 5, 2018 Author Share Posted March 5, 2018 (edited) I'm still having an issue here though. I'm posting the new code that's using a different query and only inserting what's empty in the array, but it still inserts every one of them all over again. I changed all existing records to have an expire_date of today so it shouldn't insert anything. //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 "; //TODO make this an update $insertPlacement = " UPDATE placements_new 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 "; //perpare query to check for existing records $dealerSkuCheck = " 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() "; 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{ $checkExisting = $MysqlConn->prepare($dealerSkuCheck); $existingRslt = $checkExisting->execute($values2); $count3 = $checkExisting->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)){ for($i=0; $i<$row2["QUANTITY"]; $i++) { try{ $insert = $MysqlConn->prepare($insertPlacement); $insertRslt = $insert->execute($values); }catch(PDOException $ex){ echo "QUERY FAILED!!!: " . $ex->getMessage(); } } } } Edited March 5, 2018 by thenorman138 Quote Link to comment Share on other sites More sharing options...
requinix Posted March 5, 2018 Share Posted March 5, 2018 What's your new SELECT query? Quote Link to comment Share on other sites More sharing options...
thenorman138 Posted March 5, 2018 Author Share Posted March 5, 2018 Sorry, the code formatting messed up. I edited it for you Quote Link to comment Share on other sites More sharing options...
thenorman138 Posted March 5, 2018 Author Share Posted March 5, 2018 @requinix I keep re-working it, but I'm aiming to have an insert and update, and then use the if/else to say "If doesn't exist or it exists and today is after the expire_date, then insert. If exists and is within the expire_date then update " Quote Link to comment Share on other sites More sharing options...
requinix Posted March 5, 2018 Share Posted March 5, 2018 (edited) You don't need to check the date in your code because the query is doing the work for you. Before the query was returning anything that matched and you had to decide if it was expired (insert) or not (update). Now the query will only return anything if it is not expired so you know that if the result exists then you should update. Which matches your old code's behavior of just needing empty(). Old: - Has a row and is expired: insert - Has a row and is not expired: update - No row: insert New: - Has a row (therefore not expired): update - No row: insert Follow? Edited March 5, 2018 by requinix Quote Link to comment Share on other sites More sharing options...
thenorman138 Posted March 5, 2018 Author Share Posted March 5, 2018 I follow, I'm not sure why I was having such a difficult time with that! Thank you, I think I'm in good shape now Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.