Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2014

    Unanswered: list events for this week only

    I am writing a database using php/mysql.

    I have a table actions which has fields actionid, actiondate, action.

    I want to produce a list of actions which have been entered for the week we are in. For example, if the 2nd February were a Monday so the working week was 2 - 6th February, if I ran the report on any day of that week it would list out the actions during that week grouped by day. So, not the last 7 (or 5) days but based on the calendar week.

    I have looked around but can't see how to do this. Does anyone have any suggestions please?

    Thank you.

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    Have a look at the DATE_FORMAT function. Firstly you can filter the content based on the date field in your table to match the current week

    WHERE DATE_FORMAT(NOW(), '%u') = DATE_FORMAT(datefield, '%u')

    This filters on content within the same week. Next to aggregate content based on day of week

    SELECT DATE_FORMAT(datefield, '%a'), COUNT(1)
    FROM ...
    WHERE DATE_FORMAT(NOW(), '%u') = DATE_FORMAT(datefield, '%u')
    GROUP BY DATE_FORMAT(datefield, '%a');
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts