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

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

CodePudding user response：

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.

CodePudding user response：

The best would be to have MySQL datetimes from the start in your database

But you can do all in MySQL.

STR_TO_DATE will cost time every time it runs

When you put around all expire dates a () it will give back a true if youe of them is true

CREATE TABLE deals (
deal_id int,
deal_status int,
deal_featured int,
deal_category int,
  `deal_title` VARCHAR(14),
  `deal_start` VARCHAR(19),
  `deal_expire` VARCHAR(19)
  ,deal_created DATEtime
);

INSERT INTO deals
  (deal_id,deal_status,deal_featured,deal_category,`deal_title`, `deal_start`, `deal_expire`,deal_created)
VALUES
  (1,1,1,1,'Example Deal', '10-24-2021 16:10:00', '10-25-2021 16:10:00',NOW()),
  (2,1,1,1,'Example Deal 2', '10-24-2021 16:10:00', NULL,NOW());

CREATE TABLE categories (category_id int,category_title varchar(20) )

INSERT INTO categories VALUES(1,'test')

SELECT 
    deals.deal_id, MIN(`deal_title`), MIN(`deal_start`), MIN(`deal_expire`),MIN(deals.deal_created) as deal_created , MIN(categories.category_title) 
FROM
    deals
        LEFT JOIN
    categories ON deal_category = categories.category_id
WHERE
    deals.deal_status = 1
        AND deals.deal_featured = 1
        AND STR_TO_DATE(deals.deal_start, "%m-%d-%Y %H:%i:%s")  >= NOW() - INTERVAL 1 DAY
        AND (NOW() < STR_TO_DATE(deals.deal_expire, "%m-%d-%Y %H:%i:%s")
        OR deals.deal_expire IS NULL
        OR deals.deal_expire = '')
GROUP BY deals.deal_id
ORDER BY deal_created DESC

deal_id | MIN(`deal_title`) | MIN(`deal_start`)   | MIN(`deal_expire`)  | deal_created        | MIN(categories.category_title)
------: | :---------------- | :------------------ | :------------------ | :------------------ | :-----------------------------
      1 | Example Deal      | 10-24-2021 16:10:00 | 10-25-2021 16:10:00 | 2021-10-24 22:42:34 | test                          
      2 | Example Deal 2    | 10-24-2021 16:10:00 | null                | 2021-10-24 22:42:34 | test

db<>fiddle here