Jump to content

convert sql check to if statement


Go to solution Solved by Barand,

Recommended Posts

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?

Link to post
Share on other sites

 

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)?

Link to post
Share on other sites

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.

  • Like 1
Link to post
Share on other sites

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

Link to post
Share on other sites
  • Solution

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
}
Link to post
Share on other sites

@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.

Link to post
Share on other sites
This thread is more than a year old.

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.