Thanks Harvey, much better way of doing it I agree and a very nice way of doing it too.. I have never used strtotime() so will have a wee play to learn some more. Goot to be able to contact smart folk like yourself.. keeps reminding me how little I know lol
The main issue I have doing it that way is the result is collected by an external dashboard which only understands how to get queries... the thought I did have was extract and process with PHP then slap result into a different table from which the dashboard gets its data from... The database is not large and not expected to increase much over the next few years (yeah I know terrible last words) the impact of a complex query is not a problem for the few seconds it could take. THANKS! W On 17 November 2010 11:36, Harvey Kane <[email protected]> wrote: > 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]<nzphpug%[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]<nzphpug%[email protected]> -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
