weekly stats

doobster

Hello I am trying to figure out how to calculate weekly stats

I can't figure out how to automatically detect when the week starts to the current day.

Meaning if the day is Tuesday it would display stats for Tuesday, Monday, Sunday. If it were later it would display stats for the day and the rest of the week.

pete_bisby

If you use the date() function, you'll be able to find out which day of the week it is. With this, you should be able to work out the date range for you query ...

$dayOfWeek = date("w");  // which day of the week this is 0=Sunday, 1=Monday, etc.)
$day = date("d");
$month = date("m");
$year = date("Y");
$startDate = date("Y-m-d", mktime(0,0,0,$month,$day-$dayOfWeek,$year);
$endDate = date("Y-m-d");

$query = "SELECT * from WeeklyStats WHERE StatDate BETWEEN '$startDate' AND '$endDate'";

... or something along those lines, anyway!

DeltaRho2K

You have to be careful with this, because if the 1st is on a Tuesday (Like July 2003 for example), then June 29, and June 30th come into play here... Also, you have to take into account, the year rollover if you are talking about something like January 1...

There is a lot of thought and planning that needs to go into this... Sorry to be the bearer bad news here 😉

Quick Example to calculate months and years:


  $curr_month = date("n", mktime());
  $curr_year = date("Y", mktime());

If ($curr_month == '1'):
  $prev_month = '12';
  $prev_year = $curr_year-1;
Else:
  $prev_month = $curr_month-1;
  $prev_year = $curr_year;
Endif;

If ($curr_month == '12'):
  $next_month = '1';
  $next_year = $curr_year+1;
Else:
  $next_month = $curr_month+1;
  $next_year = $curr_year;
Endif;

doobster

Thats a good point.

Can anyone provide some more elaborate code of how to incorporate into the first example?

DeltaRho2K

I hope this is elaborate enough... (yet commented enough). Have fun with it.

<?

// This uses variables pulled from the URL
// http://www.foo.com/test.php?month=7&day=14&year=2003
extract($_GET);


// Little check to see if we need to go to a previous year or not.
If ($month == '1'):
  $prev_year = $year-1;
Else:
  $prev_year = $year;
Endif;


// Numerical representation of the day of the week.
// In the case of July 14, 2003, this would give you a 1 (For Monday)
  $firstdate = date ("w", mktime(0,0,0,$month,$day,$year));

// Get our day and month variables established (with leading zeros) for our query
  $startday = date ("d", mktime(0,0,0,$month,($day-$firstdate),$year));
  $startmonth = date ("m", mktime(0,0,0,$month,($day-$firstdate),$year));
  $nowday = date ("d", mktime(0,0,0,$month,$day,$year));
  $nowmonth = date ("m", mktime(0,0,0,$month,$day,$year));

// This isn't needed, but if you echo it out, you will see what is going on with this to verify that you have
// Sunday through the current day of the week.
  $startdayname = date ("l", mktime(0,0,0,$month,($day-$firstdate),$year));
  $nowdayname = date ("l", mktime(0,0,0,$month,$day,$year));

// Quick check of the startmonth, in case you are choosing January as the current month.
// This will set the $startmonth to 12 (for December)
If ($startmonth == '0'):
  $startmonth = '12';
Endif;

// Compile the data into YYYY-MM-DD format for a comparison on a field that is in the Date format.
$startDate = $prev_year."-".$startmonth."-".$startday;
$endDate = $year."-".$nowmonth."-".$nowday;

// Just echoing the output, so you can see what's going on here.
echo $startDate." through ".$endDate." is a ".$startdayname." through a ".$nowdayname."!!!";


// Your query would go something like this (Obviously, you would need to set the connection and all first):
// I would avoid using a BETWEEN clause because if your date is on a Sunday, then a BETWEEN clause wouldn't produce any results.
$query = "SELECT * from WeeklyStats WHERE StatDate >= '$startDate' AND StartDate <= '$endDate'";
?>