I am trying to generate a report that accepts two text entries (start_date and end_date) via a php page and uses these values to query a postgresql database. My primary objective of this question is to find the most appropriate/accurate way to handle the date range identification and processing so providing thoughts/examples of the necessary loops and arrays is desired. Use any combination of PHP and SQL.
Database table/fields necessary are:
table: tbleventlog
field: id
field: dtdatetime
field: iusername
The input/output (note: users can input any valid start and end dates - no pulldowns, calendars, etc):
Each column of output must represent one week (sunday through saturday mandatory).The trick is to
capture the end_date the user submitted and if it isn't a saturday, find the next calendar saturday and then begin working backward "week-by-week" until we get to the start_date*. However, the start_date must be a sunday, so if the start_date the user input isn't a sunday, then we need to find the sunday immediately preceeding the start_date supplied by the user. This functionality must be capable of spanning months and years.
For example: a user submits start-date of 02/18/03 and end_date: 03/07/03.
** 02/18/03 is a tuesday and 03/07/03 is a friday **. Therefore, the end_date must be redefined as
03/08/03 because that's the next saturday. Likewise, the start_date of 02/18/03 will have to be
redefined as 02/16/02 because that's the preceeding saturday.
The report output should resmble the following:
Weekly Logins
|username | 02/16/ - 02/22 | 02/23 - 03/01 | 03/02 - 03/08 | total
|-------------------------
----------
----------
----------
----------
----------
----------
----------
----|
|johndoe@123.com | 12 | 0 | 3 | 15 |
|janewho@987.com | 3 | 4 | 13 | 20 |
|danwhat@345.com | 1 | 12 | 9 | 22 |
|jimwhen@abc.com | 0 | 7 | 15 | 22 |
|-------------------------
----------
----------
----------
----------
----------
----------
----------
----|
Thanks and good luck. (Many points for the headache). Ask for additional details if I haven't been thorough enough.
