[PHP/Mysql] Order by date

[w1ww]

Hello,

I have a few results at mysql and I want to order them by date like this:

Monday, February 12
Monday, February 14
Monday, February 17
Monday, February 11

Then when I click on one of them it should return all the data of that day.

What I want is to return the date like that. If the query is 'SELECT * FROM the_table ORDER BY DATE' it will return all the lines order by date, but I just want to list one of each one.

How can it be done?

Sorry my poor English!


Thanks in advance !

Tiago

 

[Tree]

How are the dates stored in the database?

 

[Peter]

To only return the dates once simply use group by.

'SELECT * FROM the_table GROUP BY date ORDER BY DATE'

 

[Dotmainer]

'SELECT * FROM the_table GRUOUP BY DATE ORDER BY DATE'

...should work

-

 

[Skeeter]

Tree asked the right question. The answer to your questions depends on the data type and format of the column in your database.

If your dates are stored as strings, formatted the same as your example ("Monday, February 12), then the queries that Zoki and Peter gave you should work great.

If your date is stored as a date/time data type in the database then you will need to use date/time functions to format the output as shown in your post.

SELETE CONCAT(DAYNAME(datefield),',',MONTHNAME(datefield),' ',DAYOFMONTH(datefield))
FROM the_Table
GROUP BY CONCAT(DAYNAME(datefield),',',MONTHNAME(datefield),' ',DAYOFMONTH(datefield))

Should give you this

Monday, February 12
Monday, February 14
Monday, February 17
Monday, February 11

I haven't had a chance to verify this syntax but it should get you going in the right direction. You can also use the link below for more date/time functions in MySql.

MYSQL DateTime Functions

 

[w1ww]

Hi guys,

I will try this out. The dates are generated by Mysql (timestamp). I'll let you know.

Thank you!

 

[redwizardstudios]

I'm very lazy with mysql date/time storage. I tend to store a unix timestamp in a bigint and then convert it back in php. Then if i need to sort by date, i can sort by that field and they'll be sorted down to the nearest second.

Darren

 

[user-7256]

I'm very lazy with mysql date/time storage. I tend to store a unix timestamp in a bigint and then convert it back in php. Then if i need to sort by date, i can sort by that field and they'll be sorted down to the nearest second.

Darren

Bingo! ^_^

Yes, and PHP's date() function is very handy for converting timestamps to any string format you want. I prefer very simple MySQL queries, no fancy stuff if possible. I try to let PHP do the work, which is often easier then.