56154

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

Question:

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.

<hr />

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...

Answer1:

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!)

Answer2:

MySQL has a WEEKDAY() function:

<blockquote>

WEEKDAY(date)

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

</blockquote>

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

My SQL knowledge is <em>very</em> 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

Recommend

  • Source file (.py) not compiling (.pyc not updated)
  • MySpace DOM?
  • After message type e, program doesn't return to selection-screen ABAP
  • Adding new column to DataFrame with values dependent on index ref
  • Aggregate all dataframe row pair combinations using pandas
  • Is C++ compilable with OpenMP and boost on MacOS?
  • Is it possible to run an application built on sql server 2008 to run with 2005
  • firebase, how to update data at a key
  • Run EF6 Query in separate Thread on WinForm Button Click Event
  • AVCaptureSession VS UIImagePickerController camera preview
  • Jquery UI Sortable, move item automatically
  • Accessing the variables from a PHP Anonymous Function
  • apply a javascript function to draggable copy
  • Aptana 3 remove bundle (jquery)
  • netsh acl setting (need alternative method - registry settings?)
  • blocking spam referer domain with web.config
  • Sending keystrokes/mouse clicks to a Java program with Autohotkey
  • MySQL Order by column = x, column asc?
  • Is playing sound in Javascript performance heavy?
  • How to match http request and response using Jersey ContainerRequestFilter and ContainerResponseFilt
  • Insert into database using onclick function
  • Different response to non-authenticated users and AJAX calls
  • Fetching methods from BroadcastReceiver to update UI
  • Submit form in a displaytag pagination
  • Knitr HTML Loop - Some HTML output, some R output
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • Symfony2: How to get request parameter
  • Delete MySQLi record without showing the id in the URL
  • GridView Sorting works once only
  • Unanticipated behavior
  • Comma separated Values
  • How to get icons for entities from eclipse?
  • WPF Applying a trigger on binding failure
  • Proper way to use connect-multiparty with express.js?
  • Load html files in TinyMce
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • Trying to get generic when generic is not available
  • how does django model after text[] in postgresql [duplicate]
  • JaxB to read class hierarchy