How to get data between start and expiration date if date is not empty or null?

0

Issue

I am trying to select offers between two dates, one of start and one of expiration and in case the expiration date is empty or null it will always show the offers.

Table

+----------------+---------------------+---------------------+
|   deal_title   |     deal_start      |   deal_expire   |
+----------------+---------------------+---------------------+
| Example Deal   | 10-24-2021 16:10:00 | 10-25-2021 16:10:00 |
| Example Deal 2 | 10-24-2021 16:10:00 | NULL                |
+----------------+---------------------+---------------------+

Php Function to get the current date by timezone.

function getDateByTimeZone(){
   $date = new DateTime("now", new DateTimeZone("Europe/London") );
   return $date->format('m-d-Y H:i:s');
}

Mysql query:

SELECT deals.*, categories.category_title AS category_title 
FROM deals 
LEFT JOIN categories ON deal_category = categories.category_id 
WHERE deals.deal_status = 1 
 AND deals.deal_featured = 1 
 AND deals.deal_start >= '".getDateByTimeZone()."' 
 AND '".getDateByTimeZone()."' < deals.deal_expire 
 OR deals.deal_expire IS NULL 
 OR deals.deal_expire = '' 
GROUP BY deals.deal_id ORDER BY deals.deal_created DESC

Solution

You didn’t really explain what problem you’re having. Having written queries like this many times in the past, you likely need parentheses around the expiration side of your date qualifications.

WHERE deals.deal_status = 1 
 AND deals.deal_featured = 1 
 AND deals.deal_start >= '".getDateByTimeZone()."' 
 AND (
   '".getDateByTimeZone()."' < deals.deal_expire 
   OR deals.deal_expire IS NULL
 )

If you don’t put parentheses around your OR clause, then operator precedence will cause the whole WHERE clause to be true whenever the expire date is NULL and that’s not what you want. You want a compounded OR clause here.

I don’t think you need to compare against empty string either, just assuming you put that in there trying to figure things out so I left it out in my sample code.

Also I’m not familiar with PHP string interpolation enough to know if there’s an issue with the way you’re interpolating the result of the ‘getDateByTimeZone’ function into that query. It looks funky to me based on past experience with PHP, but I’m ignoring that part of it under the assumption that there’s something wrapping this code which resolves it correctly.

Answered By – Joe Holloway

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More