MySQL/PHP Algorithm for Weekday/Weekend count (per month)

Question

Hey everyone,

Before I start coding, I want to come up with a good design first. Basically, I have a database table filled with dates, and users who are associated with those dates (the dates are in sql format).

Using PHP I want to, for each user, calculate (count in total) how many weekdays they are associated with, as well as how many weekends they are associated with. I need to have a total count for each month, as well as a grand total. This needs to to "run" from August until May.

I know that I can determine whether a day is a weekend or a weekday with:

 $date = '2007/08/30';
 $weekday = date('l', strtotime($date));

For determining month, I can use SQL, and use a case statement to, for example, get "October" from "10":

 SELECT MONTH(DATE_SPECIFIED);

What I am most unsure of, though, is what process to go through. I could easily have ALOT of queries, but that's inefficient. Ideally, I was thinking about printing the results in an html table.

Can anyone offer any suggestions/advice on how you might go about it?

Thanks.

---

EDIT:

I have a users table, and an eventcal table.

Here is the users table:

 CREATE TABLE `users` (
  `fname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `role` varchar(75) NOT NULL,
  `region` tinyint(4) unsigned default NULL,
  `username` varchar(25) NOT NULL,
  `password` varchar(75) NOT NULL,
  `new_pass` varchar(5) default NULL,
  PRIMARY KEY  (`username`),
  KEY `role` (`role`),
  KEY `region` (`region`),
  CONSTRAINT `users_ibfk_2` FOREIGN KEY (`region`) REFERENCES `region` (`region`) ON UPDATE CASCADE,
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role`) REFERENCES `role` (`role`) ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

And here is the eventcal table:

 CREATE TABLE `eventcal` (
  `id` int(11) NOT NULL auto_increment,
  `region` tinyint(3) unsigned NOT NULL,
  `primary` varchar(25) NOT NULL,
  `secondary` tinyint(1) NOT NULL,
  `eventDate` date NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `primary_2` (`primary`),
  CONSTRAINT `eventcal_ibfk_1` FOREIGN KEY (`primary`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8

Note that evencal.primary has a foreign key reference to users.username...

Answer 1

MySQL has a WEEKDAY() function:

WEEKDAY(date)

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

You should be able to use this in a MySQL query with GROUP BY month.

My SQL knowledge is very rusty and I'm sure this isn't valid SQL, but you should get the gist from it.

SELECT user, weekdaysPerMonth, month
FROM
(SELECT COUNT(*) AS weekdaysPerMonth, user, date, month
 FROM table
 WHERE WEEKDAY(date) > 0 AND WEEKDAY(date) < 5)
GROUP BY month

Answer 2

You should be able to do.... (Assuming you have a date table, a user table, and a link table)

SELECT MONTH(eventDate), DAYOFWEEK(eventDate), 
COUNT(*) 
FROM eventcal as e 
LEFT JOIN users as u ON e.primary = u.username 
GROUP BY MONTH(eventDate), DAYOFWEEK(eventDate);

That should return a load of rows, 3 colums each up to 7 rows per month. One is the day index (1 = sunday, 7 = saturday), one is the numeric month and one is the number of users attached to that day.

If you need to restrict to certain months, you could add a where clause as well like ...

WHERE eventDate BETWEEN '20090501' AND '20091001'

You can also use the WITH ROLLUPS keyword after the group by to have mysql return the totals per day of week or per month as well. But this is often more hassle to use than it saves you, as you have to write logic to determine if the current row is a total or not.

EDIT:

If you want to get the weekend/weekday values directly:

SELECT MONTH(eventDate), IF(WEEKDAY(eventDate)<5, 'weekday', 'weekend') AS DAY,
COUNT(*) 
FROM eventcal as e 
LEFT JOIN users as u ON e.primary = u.username 
GROUP BY MONTH(eventDate), IF(WEEKDAY(eventDate)<5, 'weekday', 'weekend');

This will return as above but only 2 rows per month, one for weekdays, one for weekends.

(Although i'm not totaly sure that you can group by a calculated value like this, I think you can - let me know if you try!)