View Full Version : Week beginning count records

03-12-2007, 12:05 PM
Hi guys,

i have a table with a large number of records, the two fields i'm interested in date (timestamp) and status, which can either be Pending, Rejected or Approved.

when i display there records, is there a way in which i can display a count at the top of the screen.

but i only want to display the number of Pending, Rejected and Approved status's for the current week (Monday to Sunday). Obviously these numbers will change constantly during the day and start fresh every monday.

any help would be great.

thanks inadvance.

03-12-2007, 04:51 PM
Providing you are using an SQL then you should use the filtering in the select query

03-12-2007, 09:47 PM
This boils down to identifying the most recent Monday expressed in either a MySQL datetime format inside a query, or a unix timestamp format in PHP.

I would probably identify the date in PHP and then use it in the query.

Finding the Monday would be something like:

//initialize work week number to today's week day number (0 = sunday, 1 = monday, etc)
$wkWeekDayNbr = date('w');
//initialize work timestamp to the current timestamp
$wkTS = mktime();

//do this loop until you get to monday (by subtracting 24 hours from the work timestamp each iteration)
while ($wkWeekDayNbr != 1) {
$wkTS -= 60 * 60 * 24; //60 seconds, 60 minutes, 24 hours
$wkWeekDayNbr = date('w', 0, 0, 0, mktime(date('m', $wkTS), date('d', $wkTS), date('Y', $wkTS)));

$queryTS = date('Y-m-d 00:00:00', $wkTS);

And then the query would use a GROUP BY clause on status to get the counts:

$query = "SELECT count(*)
FROM my_table
WHERE date_field >= $queryTS
GROUP BY status_field";

(note: I did not test any of this code so there may be corrections needed)