How do I display events by day, starting from today?

Question

How do I display events by day, starting from today?

I have a database with these fields:

id - id,
title - title,
startdate - start of the event,
enddate - end of the event.

I need to form a request to output events by dates.

Yes, and most importantly. If, for example, today's date falls between the date startdate (the start of the event) and enddate (the end of the event), this is the event should also be output.

A search on this site and on others yielded nothing. I would be very grateful.

2

php mysql события

Author: Nicolas Chabanovsky, 2012-11-13

Source

4 answers

score 3 · Answer 1

If you need to display events in a certain date range, then so:

select
  *
from
  `tablename`
where
  `startdate` between __начало_интервала__ and __конец_интервала__
  or `enddate` between __начало_интервала__ and __конец_интервала__
order by `startdate`

If events starting from today, then so:

select
  *
from
  `tablename`
where
  NOW() between `startdate` and `enddate`
order by `startdate`

score 2 · Answer 2

select id , title from tablename where startdate ='2012-01-12' and enddate = '2012-01-31'

Substitute the values of the beginning and end of events in the request and that's it.

2

Author: Artem, 2012-11-13 10:07:46

score 1 · Answer 3

As @Shrek wrote, you substitute the necessary dates. That is, let's say the request came: choose between first_date and second_date, and plus you need to select an event that occurs at this moment, then we get:

SELECT
  id,
  title
FROM
  tablename
WHERE (startdate = 'first_date' AND enddate = 'second_date')
  OR (startdate < NOW() AND enddate > NOW())

score 0 · Answer 4

If necessary, with grouping by day, then there is a function TO_DAYS. You can group it, or even make a separate field and store the result of the function in it, to speed up the query.

Add a field and do UPDATE set days=TO_DAYS(startdate); then do for example

SELECT ... FROM ... WHERE days>=TO_DAYS(NOW()) ORDER BY days

With this query, I select all events from the current day and more. Only for the current date-output days=TO_DAYS(NOW()) This option is allows you to make an index for the days field (if the TO_DAYS(startdate) index is not applied directly in the request. getting the array

Id title startdate days

1 news item 2012-11-20 10:50:33 735192

2 news2 2012-11-20 12:10:22 735192

3 news3 2012-11-21 17:10:22 735193

Accordingly, each time the value changes in the days field, we display a new title, etc.