[Resolved] Displaying Week Totals

[uwcoder]

Hello:

I have created an application system for a human resources department. They are interested in finding out how many people per week have submitted an application. I figured this would be fairly straight forward to code, but it seems to have turned into a monster and I figured that there must be an easier way to do this.

This is my setup:
as part of the application I collect a time stamp for submit date. Using the strftime('%U',$date) function I determine the week that each application in the system was submitted.

This is where it gets out of hand:

Then I use a bunch of if statements to determine what number week that application is in and place it into an array for that week. for example:

$test = strftime('%U',$date);
if($test == "1") { $a[] = $id; }
if($test == "2") { $b[] = $id; }
if($test == "3") { $c[] = $id; }
if($test == "4") { $d[] = $id; }
if($test == "5") { $e[] = $id; }
if($test == "6") { $f[] = $id; }
etc. for 52 weeks

then to determine if an array exists and if so, how many applications for each week using the count() function.

This works okay, but It is limited on whether or not an array exists and it seems a bit cumbersome.

would anybody know a cleaner faster way to do this?

Thanks for any feedback

[AstroTeg]

I'd recommend letting your SQL server do the work for you. You'll need to have SQL figure out which week it is. In MySQL, I think its just WEEK([date]). You then want to group all applications by this week value so you'd have GROUP BY WEEK([date]).

Then you need to keep a count of how many applications per week. To do this, you'd want to use the COUNT function. Typically, you'll want to count on something unique to the application - like an application ID. You're resulting query might look like this:

SELECT COUNT(appID) AS appCount, WEEK(mydate) AS weekNum FROM applicationtable GROUP BY WEEK(mydate), appID ORDER BY WEEK(mydate)

(The ORDER BY is optional - probably by default, SQL will have ordered the week in proper order, but if it doesn't or you don't trust it, then toss in the ORDER BY).

Once you do this, its a simple coding excercise of looping through the rows and pulling out or displaying what you need.

[uwcoder]

Thanks!

I am using MS SQL for this project, but I think I got it to work that way with a similar function. I appreciate your help.