-1

I tried to Google this, but all I could find was how to sort alphabetically which I already know. Basically I would like to sort my different rows and print them out based on their date value.

This is printing out a competition schedule. I want to organize it by weeks and print it out so all of the first weeks games are printed together and in a different div the 2nd weeks games etc etc...

I don't want to use a WHERE() clause with a certain date because I have multiple leagues with multiple start dates and the dates will definitely not be the same. I feel like it would be very sluggish to search through every possible date.

Any help would be awesome.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Nash
  • 542
  • 1
  • 7
  • 16

1 Answers1

0

This is conceptually straightforward, if a little messy in syntax.

You need an expression that will turn any DATE into the first day of the week it contains, and then you need to ORDER BY that expression.

Here's the expression, given that your weeks begin on Sundays.

FROM_DAYS(TO_DAYS(event_date) -MOD(TO_DAYS(event_date) -1, 7))

So a query roughly like this will do the trick for you.

SELECT FROM_DAYS(TO_DAYS(event_date) -MOD(TO_DAYS(event_date) -1, 7)) AS week_begin,
       *
  FROM competition
 ORDER BY FROM_DAYS(TO_DAYS(event_date) -MOD(TO_DAYS(event_date) -1, 7)),
       team, whatever

This isn't nearly debugged; I don't know your column or table names.

This is written up in some detail here. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

O. Jones
  • 103,626
  • 17
  • 118
  • 172