Personally I try to farm out as much work to PHP as possible - so if I
understand your question correctly - all of that date stuff I would be
caclulating in PHP and sending hard date values into the query.
instead of...
DATE_SUB(DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY),INTERVAL
7 DAY)AS startOfPeriod
you can roll with...
$last_friday = strtotime('last friday 23:59:59');
$friday_before_that = strtotime('-1 week', $last_friday);
and then...
$formatted_last_friday = date('Y-m-d H:i:s', $last_friday);
to format it for the query if you are using mysql datetime types to
store dates.
Using strtotime() in PHP it's pretty easy to calculate the timeframes
you are looking for - in my opinion way easier and cleaner-looking than
trying to do it in mysql.
hope that helps,
Harvey.
On 17/11/2010 11:24 a.m., William Hamilton wrote:
I do remember the warning about complex queries slowing server but at
this stage I have no choice :( Looking for some pointers or even
happy pay someone to generate query for me. Using PHPUG as folk are
in NZ :) Sorry for the bit off topic.
I have three tables show below which I am querying in a number of
ways. e.g. I have a report which lists number of reports provided
compared to number which were due over the duration of the project.
I am puzzling over how to select the reports which were due and were
delivered during a period based on frequency** and current date.
eg: I would like to produce an output based on the past week ending on
a Friday (eg: if run on Tuesday 2nd it would still report on the
previous week ending Friday 29th). I can mostly work out the "due"
part based on the frequency ie: weekly due every week, fortnightly - I
am using MOD to see if week is odd or even, monthly not sure yet but
work out if this is the last week of the month I suppose.
**project_cstm -- reportingfrequency will be something like weekly,
fortnightly or monthly
Any constructive pointers welcome and if more details required happy
to fill in the gaps. If you think you can generate me a wizbang great
query and want paid drop me a message here of offlist.
TIA
W
===mixed workings from ===
SELECT
DATE_SUB(DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY),INTERVAL
7 DAY)AS startOfPeriod,
DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY)AS
endOfPeriod,
CASE project_cstm.`reportingfrequency_c` WHEN "Weekly"THEN
"1"WHEN "Fortnightly"THEN
IF(MOD(week(project.`estimated_start_date`),2)=MOD(WEEK(DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY)),2),1,0)WHEN
"Monthly"THEN 'month'ELSE 'bugger!'END AS reportDue,
COUNT(notes.`parent_id`)AS deliveredReports,
project_cstm.`reportingfrequency_c` AS reportFreaquency,
project.`name` AS project_name
FROM
`project` project LEFT OUTER JOIN `notes` notes ON project.`id` =
notes.`parent_id`
LEFT OUTER JOIN `project_cstm` project_cstm ON project.`id` =
project_cstm.`id_c`
WHERE
project.`deleted` = 0
AND project.`estimated_end_date` >
DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY)
GROUP BY
project.`id`
====table descriptions====
mysql> describe notes;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id | char(36) | NO | PRI | | |
| date_entered | datetime | NO | | | |
| date_modified | datetime | NO | | | |
| modified_user_id | char(36) | YES | | NULL | |
| created_by | char(36) | YES | | NULL | |
| name | varchar(255) | YES | MUL | NULL | |
| filename | varchar(255) | YES | | NULL | |
| file_mime_type | varchar(100) | YES | | NULL | |
| parent_type | varchar(25) | YES | | NULL | |
| parent_id | char(36) | YES | MUL | NULL | |
| contact_id | char(36) | YES | MUL | NULL | |
| portal_flag | tinyint(1) | NO | | 0 | |
| embed_flag | tinyint(1) | NO | | 0 | |
| description | text | YES | | NULL | |
| deleted | tinyint(1) | NO | | 0 | |
+------------------+--------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
mysql> describe project;
+----------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| id | char(36) | NO | PRI | | |
| date_entered | datetime | NO | | | |
| date_modified | datetime | NO | | | |
| assigned_user_id | char(36) | YES | | NULL | |
| modified_user_id | char(36) | YES | | NULL | |
| created_by | char(36) | YES | | NULL | |
| name | varchar(50) | NO | | | |
| description | text | YES | | NULL | |
| deleted | tinyint(1) | NO | | 0 | |
| estimated_start_date | date | NO | | | |
| estimated_end_date | date | NO | | | |
| status | varchar(255) | YES | | NULL | |
| priority | varchar(255) | YES | | NULL | |
+----------------------+--------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
mysql> describe project_cstm;
+-----------------------+--------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default |
Extra |
+-----------------------+--------------+------+-----+-------------+-------+
| id_c | char(36) | NO | PRI | |
|
| project_0bjective_1_c | varchar(255) | NO | | Insert text |
|
| reportingfrequency_c | varchar(100) | YES | | Weekly |
|
| account_id_c | char(36) | YES | | NULL |
|
+-----------------------+--------------+------+-----+-------------+-------+
4 rows in set (0.00 sec)
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
--
Harvey Kane
Phone:
- Auckland: +64 9 950 4133
- Wanaka: +64 3 746 8133
- Mobile: +64 21 811 951
Email: [email protected]
If you need to contact me urgently, please read my email policy
www.ragepank.com/email/
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
