php/mysql group by weekdays

[CodilX]

hi there,

I'm having some trouble sorting my database

I'm trying to group my database by weekdays and I can't seem to get it right

My idea is that let's I want to group by a day, for example monday, get all the entries that were made on all of the mondays in the database and do some calculations.

Hope someone can help me on this :)

[Anyango]

which format are you using to store date in the database ? Unix Timestamp (int) ? or any other format ? solution depends upon that.

[CodilX]

yyyy-mm-dd

[CodilX]

after some thinking, I wondered whether sorting this way would be a good idea, so I just added a column "day" and ran this code:

$get = mysql_query("SELECT * FROM `database`");

 while($row=mysql_fetch_array($get)) {

 $date = strtolower(date('D', strtotime('' . $row['date'] . '')));

 mysql_query('UPDATE `database`.`table` SET `day` = "' . $date . '" WHERE `table`.`id` =' . $row['id'] . '');

}

echo "done";

now I can just sort by "mon", or "tue", etc :)

just a few follow up questions. how can I group by "mon" AND "tue", not just by one value? and how can I count how many mondays, tuesdays etc there where within a given time frame?

[edited by: CodilX at 4:03 am (utc) on Oct. 12, 2008]

[Anyango]

select count(*) as dayCount,day from yourTable group
by day

then you will get values like

24, mon
**, tue
xx, wed

and you can add the where clauses to limit time duration, before the group by clause

[CodilX]

thanks :)

[coopster]

In regards to your original question, and FWIW, there is an ODBC standard called DAYOFWEEK [dev.mysql.com] that MySQL supports.