mysql query, how to go about it?

I am storing data in a mysql database with unix time()

I want to select data out for days, and echo it out like this:

Monday - value
Tuesday - value
Wednesday - value
Thursday - valu开发者_开发问答e
Friday - value
Saturday - value
Sunday - value

value is saved in the database as value the times are saved in the database as time

So basically I want to select time from the database in a query that groups all of them as a day, Monday.. do you get what I mean?

Any help with this would be greatly appreciated.

---

First, replace that integer with a real DATETIME column, then use MySQL's built-in functions to easily GROUP BY the DAYOFWEEK(column)

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

There is no advantage to storing an integer instead of a real date. Not only can you easily convert to a UNIX timestamp if you need that in your application, but you'd save storage space too.

---

To convert to MYSQL native date, use FROM_UNIXTIME(). DAYNAME will return the day of the week.

SELECT DAYNAME(FROM_UNIXTIME(col))

For example:

SELECT DAYNAME(FROM_UNIXTIME(1196440219))

Further, to group by you could use something like this:

SELECT DAYNAME(FROM_UNIXTIME(col)), ... FROM table GROUP BY DAYNAME(FROM_UNIXTIME(col))

---

Maybe hits post can help you: MySQL Query GROUP BY day / month / year

Regards!