How to sort data depending on what day it was posted ?

Question

Hello,

How do you sort data which was stored in a mysql database depending on the days of the week in which the data was submited ??

I basically want to create a diary which outputs information in each day of the week depending on what day it was posted by dates so,

Mon - Data in order of date Tue - Wed - e.t.c

Any code examples and information will be great, thanks.

Answer 1

You can use the DAYOFWEEK function to extract the day, and then sort on it just like any other data.

Answer 2

Are you making a database or want to sort existing information?

Answer 3

What kinf of data type is the column where you store the date submission?

Answer 4

It seems like you're asking for a basic SELECT statement?

SELECT some_column, another_colum FROM your_table ORDER BY your_date_column DESC

This assumes you actually have a column that logs the insertion timestamp.

If this answer is obnoxiously simplistic, please forgive me...and give us more details :)

Regards.

Answer 5

You can do a

SELECT DAYOFWEEK(datehere) as dayofweek, datehere FROM something ORDER BY dayofweek, datehere;

Answer 6

If your data is stored as a DATE or DATETIME field, use the DAYOFWEEK or DATE_FORMAT functions to turn it into day name for output, but continue to order by the DATE field

SELECT DATE_FORMAT(my_date_column, '%W') AS dayofweek FROM my_table ORDER BY my_date_column

Answer 7

Well, the sorting bit is easy, just sort on the column that represents the post's date. The grouping in days is something you can do in your code, since you need to check the date there anyway (for post-processing the actual output).

To put it this way, you can do a subselect to get the specific day of the week, but in your code you would have to check the day again to group posts per day. In that case it's better (and cleaner, since you're separating business logic from data) to do this in your code, something like this:

1. select all posts (within date range)
2. make an associative array, with the days as keys, and posts (in new arrays) as values
3. loop through the days and then posts to output the posts per day

Answer 8

SELECT *
FROM diary_entries
ORDER BY FIELD(DAYOFWEEK(created), '2, 3, 4, 5, 6, 7, 1'), created

DAYOFWEEK grabs day of the week number (1 = Sunday).

FIELD makes Monday first day of the week.

After sorting by day of week, then sorted by date created.