CODEWITHSUNDEEP
mysql
tmartin314
tmartin314

Reputation: 4171

SQL query that changes if date is today

I'm having trouble deciding how to grab the latest episode ID from a mysql database.

What I need is to grab the most recent air_date that is in the past, but if the episode is airing today, the return this one. The code I have isn't working and I think I'm missing something. IF anyone has any suggestions how to achieve this, I would appreciate it:

$today = mktime(0, 0, 0);

"SELECT `id` FROM `hm_episodes_main`
WHERE `show_id` = '{$iShowId}'
AND `episode_voting` = 'Yes'
AND `air_date` < '{$today}'
ORDER BY `air_date` DESC"

Upvotes: 0

Views: 134

Answers (2)

pilcrow
pilcrow

Reputation: 58741

If I understand correctly, air_date is an epoch timestamp. How about:

   SELECT `id`
     FROM `hm_episodes_main`
    WHERE `show_id` = ?                  -- plug $iShowId in here
          AND
          `episode_voting` = 'YES'
          AND
          `air_date` <= UNIX_TIMESTAMP() -- no need for mktime()
 ORDER BY `air_date` DESC
    LIMIT 1;

Upvotes: 0

jim31415
jim31415

Reputation: 8818

I think you may just need to modify your existing query to include the current date:

AND air_date <= '{$today}'

Upvotes: 1

Related Questions