MySQL的/ PHP的算法平日/周末计数（每月） [英] MySQL/PHP Algorithm for Weekday/Weekend count (per month)

问题描述

在我开始编码，我要拿出一个好的设计第一。基本上，我充满了日期的数据库表，和谁与这些日期相关联的用户（日期是SQL格式）。

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

使用PHP欲，对于每个用户，计算（计算），他们有多少平日相关联，以及它们多少周末与相关联。我需要有一个总数为每月，以及总计。这需要从八月到跑到五月。

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

有关确定一个月里，我可以使用SQL，并使用一个case语句，比如，获得十一，从10：

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

 SELECT MONTH(DATE_SPECIFIED);

我什么最不能确定的，不过，是什么过程要经过。我可以轻松拥有的查询了很多，但这是低效率的。理想情况下，我在想打印结果的HTML表。

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?

感谢。

---

编辑：

我有一个用户表，和一个eventcal表。

I have a users table, and an eventcal 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

这里是eventcal表：

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

需要注意的是evencal.primary有一个外键引用users.username ...

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

推荐答案

您应该能够做到......（假设你有一个日期表，用户表，和一个链接表）

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

这应该返回行的负载，3 colums各月产量可达7行。一个是天指数（1 =星期日，7 =星期六），一个是数字月和1是附连到当天的用户数。

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.

如果您需要限制某些个月，你可以添加一个WHERE子句以及像...

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

WHERE eventDate BETWEEN '20090501' AND '20091001'

您还可以使用WITH汇总关键字组后有MySQL的返回月份的周或总计每天为好。但是，这往往是更多的麻烦比它节省了你使用，你必须编写逻辑来判断当前行是一个总的或没有。

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.

编辑：

如果您想直接获取周末/工作日值：

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

这篇关于MySQL的/ PHP的算法平日/周末计数（每月）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！