convert sql check to if statement

[thenorman138]

I'm trying to convert these 2 existing select queries:

 

//existing records that are expired

    $expiredCheck = "

SELECT 

sku_id,

dealer_id,

expire_date

FROM dealerplacetest p

INNER JOIN skus s

ON p.sku_id = s.id

WHERE p.dealer_id = :DEALER

AND   s.build = :build

AND   s.cov = :cov

AND   s.col = :col

AND   p.expire_date <= date_add(convert(:ship,date), interval 7 day)

";



//existing records that are not expired

    $validCheck = "

SELECT 

sku_id,

dealer_id,

expire_date

FROM dealerplacetest p

INNER JOIN skus s

ON p.sku_id = s.id

WHERE p.dealer_id = :DEALER

AND   s.build = :build

AND   s.cov = :cov

AND   s.col = :col

AND   p.expire_date > date_add(convert(:ship,date), interval 7 day)

";



$checkExisting = $MysqlConn->prepare($expiredCheck);

$checkExistingValid = $MysqlConn->prepare($validCheck);



$existingRslt = $checkExisting->execute($values2);

$count3 = $checkExisting->fetch(PDO::FETCH_ASSOC);



$existingVldRslt = $checkExistingValid->execute($values2);

$count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC);

 

Into one general select like so:

 

//select records alltogether, check for expiration later in loop

$expiredCheck = "

SELECT 

sku_id,

dealer_id,

expire_date

FROM dealerplacetest p

INNER JOIN skus s

ON p.sku_id = s.id

WHERE p.dealer_id = :DEALER

AND   s.build = :build

AND   s.cov = :cov

AND   s.col = :col

";



$checkExisting = $MysqlConn->prepare($expiredCheck);

 

And then use my existing bound parameters ($values2) to formulate an if statement that will replace the previously existing expiration date check. Basically I've taken the 2 previous expiration date checks from the query and plugged them into the if statement but it fails the '7' in my day interval. I feel like there may be a different syntax standard to use here but I can't seem to find the proper solution.

 

 

I'm trying to create a $count3 variable for the first check and a $count4 variable for the 2nd check.

Here are params and if statement:

 

$values2 = [

         ":DEALER" => $row2["DEALER"],

         ":build" => $row2["build"],

         ":cov" => $row2["cov"],

         ":col" => $row2["col"],

         ":ship" => $row2["ship"],

        ];





        $existingRslt = $checkExisting->execute($values2);

while($existingRow = $checkExisting->fetch(PDO::FETCH_ASSOC)){

if($existingRow["expire_date"] <= date_add(convert(":ship",date), interval 7 day){

$count3 = $checkExisting->fetch(PDO::FETCH_ASSOC);

}elseif($existingRow["expire_date"] > date_add(convert(":ship",date), interval 7 day)){

$count4 = $checkExisting->fetch(PDO::FETCH_ASSOC);

}

}

I'm just trying to consolidate SELECT queries if possible. What am I doing wrong?

[Barand]

 

if($existingRow["expire_date"] <= date_add(convert(":ship",date), interval 7 day){

 

There is so much wrong with that statement.

 

• There is no way that the string value ":ship" can be converted to a valid date value.
• "interval 7 day" is SQL syntax, not php. You can't just copy the code from from one to the other - they are two different languages.

What format are your dates in currently (expire_date and ship)?

[thenorman138]

:SHIP is coming from DB2 and it is a packed field value (decimal). Expire_date is in mysql and it is DATE

[Barand]

What does this give as a result value?

$stmt = $MysqlConn->prepare("SELECT convert(:ship, DATE)");
$stmt->execute( [ ':ship' => $row2['ship'] ]);
$date = $stmt->fetchColumn();
echo $date;

[Psycho]

Make it easy on yourself and create a derived value in the SQL query to indicate expired or not. You can use your condition as the value for that derived value and the value will be a 1 or 0 (True/False)

 

 

SELECT sku_id, dealer_id, expire_date,
       (p.expire_date <= date_add(convert(:ship, date), interval 7 day) as expired
FROM dealerplacetest p
INNER JOIN skus s
    ON p.sku_id = s.id
WHERE p.dealer_id = :DEALER
  AND   s.build = :build
  AND   s.cov = :cov
  AND   s.col = :col

 

The query results will include a dynamic field, 'expired', that will be a 1 or 0. Although, your date comparison is very odd as Barand pointed out. Not sure why you are storing two 'date' values as different field types.

[thenorman138]

Thank you, I'm trying to test this now. And just to be clear: I'm only storing the DATE version in mysql. I have to use the :SHIP from db2 to convert and add my interval so that I store the right value in mysql, but I'm only pulling the packed value because that's what DB2 is using for that field

[thenorman138]

@psycho where does the value come from though? IN other words, if I use that query and then keep my while loop, would I say if(expired = 1) count3, and if expired = 0 then count4?

[thenorman138]

@barand that gives me this format:  2018-01-19

[Barand]

The conversion works OK then.

 

In php you would do something like this

 

$expire = '2018-03-20';
$ship = '2018-01-19';

$date1 = new DateTime($expire);
$date2 = (new DateTime($ship))->add(new DateInterval('P7D'));

if ($date1 <= $date2) {
    // do whatever if expired
} else {
    // do whatever if not expired
}

 

However, Psycho's method of doing the test in the query looks a lot better. It then becomes

 

if ($row['expired']) {
    // do this
} else {
    // do that
}

[Psycho]

@psycho where does the value come from though? IN other words, if I use that query and then keep my while loop, would I say if(expired = 1) count3, and if expired = 0 then count4?

 

Barand mostly answered the question, but I will expand on what he provided.

 

Where does it come from: In the select statement we create a derived value and assigned it to an alias. A simple example would be this

 

SELECT var1, var2, (var1 + var2) as the_sum

 

In this case we are using your comparison as the derived value: (p.expire_date <= date_add(convert(:ship, date), interval 7 day)

Instead of adding two values, a comparison will result in a TRUE/FALSE value that will be interpreted as a 1 or 0. It is then assigned to the alias of 'expired' which you can reference in the resulting data as Barand showed above.

[thenorman138]

Thanks to both of you @psycho and @barand!