Jump to content

Modifying IF statement to incorporate a date before current_date

thenorman138

By thenorman138
in PHP Coding Help

Recommended Posts

thenorman138 Advanced Member

thenorman138

Posted
Posted

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


        }
    }
    }

 

Link to comment
Share on other sites
requinix Prolific Member

requinix

Posted
Posted

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?

Link to comment
Share on other sites
thenorman138 Advanced Member

thenorman138

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
thenorman138 Advanced Member

thenorman138

Posted
  • Author
Posted

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


        }
    }
    }

 

Link to comment
Share on other sites
requinix Prolific Member

requinix

Posted
Posted

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?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.