Jump to content


Photo

By Time Period Queries


  • Please log in to reply
23 replies to this topic

#21 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 09:29 PM

Start of year would be
$start_of_year = mktime(0, 0, 0, 1, 1, $year);

and all time, just leave out the where clause altogether in the query.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#22 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 09:48 PM

Awesome.
Thankyou King Arthur :)

Last part to go.... adding this:
WHERE customer_id = ".$db->mySQLsafe($ccUserData[0]['customer_id'])
correctly to your existing where clauses.

#23 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 06 August 2006 - 10:00 PM

If you already have the $start_of_month and $start_of_year variables defined then I guess:

$query = "select sum(total) as total_orders from order_sum where time>=$start_of_month and customer_id = ".$db->mySQLsafe($ccUserData[0]['customer_id']);
$result=mysql_query($query) or die(mysql_error());
$month_total = mysql_result($result, 0);

$query = "select sum(total) as total_orders from order_sum where time>=$start_of_year and customer_id = ".$db->mySQLsafe($ccUserData[0]['customer_id']);
$result=mysql_query($query) or die(mysql_error());
$year_total = mysql_result($result, 0);

$query = "select sum(total) as total_orders from order_sum where customer_id = ".$db->mySQLsafe($ccUserData[0]['customer_id']);
$result=mysql_query($query) or die(mysql_error());
$whole_total = mysql_result($result, 0);

Try that. The variables $month_total, $year_total and $whole_total should have the values you want, any typos notwithstanding as it is late on a Sunday here.

BTW the use of mktime() is a little obtuse, but you can do all sorts with it. If you want total orders today, use mktime(0, 0, 0, $mon, $mday, $year). If you want everything since 9am May 17th 1995 for whatever reason, use mktime(9, 0, 0, 5, 17, 1995). The variables I used are from the keys extracted from the array made by getdate() which are $seconds, $minutes, $hours (self explanatory), $mday (day of month), $wday (day of week), $mon (month of year), $year, $yday (day of year).
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#24 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 10:08 PM

All is now working perfectly.
Thankyou very much to Orio and King Arthur
So very much appreciated :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users