CODEWITHSUNDEEP
phpmysql
Dan
Dan

Reputation: 29

Structure a query to only display today's event after a certain time of day

I have a query that pulls homework assignments for students. I want to always display everything less than or equal to today's date, which is fine and done, but how can I add a stipulation in either PHP or MySQL that displays today's event after 8:30am, while still displaying anything prior to today all the time.

Trying to prevent students from seeing their homework before they get to school.

        //Get & Display the Homework
    $getTopics = mysql_query("SELECT topicId, categoryId, topicName, assignmentDate FROM forumTopics WHERE categoryId = '".$forumId."' AND hidden = '0' and assignmentDate <= '".$today."' ORDER BY createdDate DESC") or die("Cannot get forum topics: " . mysql_error());

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Your SQL is:

SELECT topicId, categoryId, topicName, assignmentDate
FROM forumTopics
WHERE categoryId = '".$forumId."' AND hidden = '0' and assignmentDate <= '".$today."'
ORDER BY createdDate DESC;

To make this show today only after 8:30 a.m.:

SELECT topicId, categoryId, topicName, assignmentDate
FROM forumTopics
WHERE categoryId = '".$forumId."' AND hidden = '0' and
      (assignmentDate < '".$today."' or assigmentDate = '".$today."' and time('".today."') >= '08:30:00'
ORDER BY createdDate DESC;

This is assuming that your variable $today has both the date and time. Otherwise, just use now() and/or CURDATE() and/or CURTIME().

Upvotes: 2

Related Questions