Jump to content

Modifying IF statement to incorporate a date before current_date


thenorman138

Recommended Posts

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

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

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

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 by thenorman138
Link to comment
Share on other sites

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 by requinix
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.