By Time Period Queries

[NFD]

What I am trying to do is query the orders, and display something like:
Orders This Month:  $10
Orders This Year:  $100
Orders To Date: $1000
where the figures above are calulated from whats in a table called order_sum

The two fields Im looking at are called:
- total (a dollar figure)
- time (stored like 1154879296)

How would I go about doing this?

[king arthur]

First get the components of the current time with
[code]
$date_array = getdate();
extract($date_array);
[/code]

This gives you lots of variables such as $mon, the number of the current month in the year, $mday the current day of the month etc.

You can then use these to construct the date you want to count results from with mktime(). E.g.
[code]
$start_of_month = mktime(0, 0, 0, $mon, 1, $year);
[/code]

That will give you a timestamp for midnight on the first day of this month.

Then query the database with this timestamp thus:
[code]
$query = "select sum(total) as total_orders from order_sum where time>=$start_of_month";
$result=mysql_query($query) or die(mysql_error());
$total = mysql_result($result, 0);
[/code]

$total is the total amount so far this month.

HTH.

[Orio]

[code]<?php
$now=time();
$sec['month']=60*60*24*30; //secs in 30 days
$sec['year']=60*60*24*364 //secs in 364 days
$month_ago=$now-$sec['month']; //timestamp of 30 days ago
$year_ago=$now-$sec['year']; //timestamp of 364 days ago

//create queries
$sql['month']="SELECT total FROM `order_sum` WHERE time>".$month_ago;
$sql['year']="SELECT total FROM `order_sum` WHERE time>".$year_ago;
$sql['total']="SELECT total FROM `order_sum`";

//run queries
$result['month']=mysql_query($sql['month']);
$result['year']=mysql_query($sql['year']);
$result['total']=mysql_query($sql['total']);

$sum['month']=0;
$sum['year']=0;
$sum['total']=0;

//loop thru results and add
while($dollars=mysql_fetch_array($result['month'])){$sum['month']=$sum['month']+$dollars;}
while($dollars=mysql_fetch_array($result['year'])){$sum['year']=$sum['year']+$dollars;}
while($dollars=mysql_fetch_array($result['total'])){$sum['total']=$sum['total']+$dollars;}

//output
echo("Oders This Month: $".$sum['month']."<br>");
echo("Oders This Year: $".$sum['year']."<br>");
echo("Oders To Date: $".$sum['total']);

?>[/code]

This will work fine if you have only numbers in the table. If every number has a dollar sign before of it, use substr() to remove the first char.

Orio.

[NFD]

Wow.
I was not expecting such a fast and in depth response.

Thankyou kindly to you both, it is very much appreciated.

The field thankfully doesnt have $, is just numbers.

I will now attempt to implement what you have suggested and let you know how I go.

Again, thankyou.

[NFD]

Using Orio's example, I get an error of :
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/includes/content/viewOrders.inc.php on line 65
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/includes/content/viewOrders.inc.php on line 66
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/includes/content/viewOrders.inc.php on line 67
And it returns a $0 value

If I comment out (lines 65,66,67)
//loop thru results and add
//while($dollars=mysql_fetch_array($result['month'])){$sum['month']=$sum['month']+$dollars;}
//while($dollars=mysql_fetch_array($result['year'])){$sum['year']=$sum['year']+$dollars;}
//while($dollars=mysql_fetch_array($result['total'])){$sum['total']=$sum['total']+$dollars;}
no error, but it keeps returning a $0 value (should be $6.99)


As for king arthur, I just cant get that one to work at all :(

[Orio]

Are you sure the table name and field names are right?

If this is not the problem, change the mysql_query part this way to get more information:
$result['month']=mysql_query($sql['month']) or die(mysql_error());
$result['year']=mysql_query($sql['year']) or die(mysql_error());
$result['total']=mysql_query($sql['total']) or die(mysql_error());

Orio.

[NFD]

I think so
db = test
table = order_sum
fields =
prod_total (has a value of 6.99)
time (has a value of 1154879296)

I also need to look at incorporating :
WHERE customer_id = ".$db->mySQLsafe($ccUserData[0]['customer_id'])."
so it returns the results only for the user logged in

I will now try the change you have suggested.

[NFD]

Ok
No more errors, but still all $0 totals

[Orio]

Can you post the script with the modifactions?

Orio.

[NFD]

Sure.

[code]<?php

if (ereg(".inc.php",$HTTP_SERVER_VARS['PHP_SELF']) || ereg(".inc.php",$_SERVER['PHP_SELF'])) {
echo "<html>\r\n<head>\r\n<title>Forbidden 403</title>\r\n</head>\r\n<body><h3>Forbidden 403</h3>\r\nThe document you are requesting is forbidden.\r\n</body>\r\n</html>";
exit;
}

// query database

$view_orders=new XTemplate ("skins/".$config['skinDir']."/styleTemplates/content/viewOrders.tpl");

$view_orders->assign("LANG_YOUR_VIEW_ORDERS",$lang['front']['viewOrders']['your_orders']);

//Start SemperFi Addition


$now=time();
$sec['month']=60*60*24*30; //secs in 30 days
$sec['year']=60*60*24*364; //secs in 364 days
$month_ago=$now-$sec['month']; //timestamp of 30 days ago
$year_ago=$now-$sec['year']; //timestamp of 364 days ago

//create queries
$sql['month']="SELECT prod_total FROM `CubeCart_order_sum` WHERE time>".$month_ago;
$sql['year']="SELECT prod_total FROM `CubeCart_order_sum` WHERE time>".$year_ago;
$sql['total']="SELECT prod_total FROM `CubeCart_order_sum`";

//run queries
$result['month']=mysql_query($sql['month']) or die(mysql_error());
$result['year']=mysql_query($sql['year']) or die(mysql_error());
$result['total']=mysql_query($sql['total']) or die(mysql_error());

$sum['month']=0;
$sum['year']=0;
$sum['total']=0;

//loop thru results and add
while($dollars=mysql_fetch_array($result['month'])){$sum['month']=$sum['month']+$dollars;}
while($dollars=mysql_fetch_array($result['year'])){$sum['year']=$sum['year']+$dollars;}
while($dollars=mysql_fetch_array($result['total'])){$sum['total']=$sum['total']+$dollars;}

//output
echo("Oders This Month: $".$sum['month']."<br>");
echo("Oders This Year: $".$sum['year']."<br>");
echo("Oders To Date: $".$sum['total']);


$view_orders->assign("LANG_ORDERS_THIS_MONTH",$lang['front']['viewOrders']['orders_this_month']);
$view_orders->assign("LANG_ORDERS_THIS_YEAR",$lang['front']['viewOrders']['orders_this_year']);
$view_orders->assign("LANG_ORDERS_TO_DATE",$lang['front']['viewOrders']['orders_to_date']);
// $view_orders->assign("VAL_ORDERS_THIS_MONTH",$prod_total);

//End SemperFi Addition

$orders = $db->select("SELECT status, cart_order_id, time FROM ".$glob['dbprefix']."CubeCart_order_sum WHERE customer_id = ".$db->mySQLsafe($ccUserData[0]['customer_id'])." ORDER BY `time` DESC");

if($orders == TRUE){

$view_orders->assign("LANG_ORDER_LIST",$lang['front']['viewOrders']['orders_listed_below']);

$view_orders->assign("LANG_ORDER_NO",$lang['front']['viewOrders']['order_no']);
$view_orders->assign("LANG_STATUS",$lang['front']['viewOrders']['status']);
$view_orders->assign("LANG_DATE_TIME",$lang['front']['viewOrders']['date_time']);
$view_orders->assign("LANG_ACTION",$lang['front']['viewOrders']['action']);
$view_orders->assign("LANG_VIEW_ORDER",$lang['front']['viewOrders']['view']);

for($i=0; $i<count($orders);$i++){

$state = $orders[$i]['status'];
$orders[$i]['state'] =  $lang['orderState'][$state];

$view_orders->assign("TD_CART_CLASS",cellColor($i, $tdEven="tdcartEven", $tdOdd="tdcartOdd"));
$view_orders->assign("DATA",$orders[$i]);
$view_orders->assign("DATA",$orders[$i]);
$view_orders->assign("VAL_DATE_TIME",formatTime($orders[$i]['time']));
$view_orders->parse("view_orders.session_true.orders_true.repeat_orders");
}


$view_orders->parse("view_orders.session_true.orders_true");

} else {
$view_orders->assign("LANG_NO_ORDERS",$lang['front']['viewOrders']['no_orders']);
$view_orders->parse("view_orders.session_true.orders_false");

}


$view_orders->assign("LANG_LOGIN_REQUIRED",$lang['front']['viewOrders']['login_required']);

if($ccUserData[0]['customer_id']>0) $view_orders->parse("view_orders.session_true");

else $view_orders->parse("view_orders.session_false");

$view_orders->parse("view_orders");

$page_content = $view_orders->text("view_orders");

if($_GET["ccdl"]){
$page_content = "ccdl";
}
?>[/code]

Thats the entire script.
Not just the stand alone part I was using for testing.

[Orio]

Weird...
Once again- all the column/table names are correct? (Remember that everything is case sensative)
And are you sure there are records in the tables?

Orio.

[NFD]

Went thru and checked them again.
Both in the test one which only contains the table and fields I mentioned and in the one that script looks at.

Im going to manually add a heap of entries.

Looking at what I posted above, can you see:
- why a 0 result?
- the correct way of checking the user?

You got email (seeing PM is busted) Orio?

[Orio]

-I think it's a 0 because there's a problem with the queries. Like I said in my previous post.

-Do you have in this table which user submited that order? If you do, add to the quries "AND user=$username", when the column "user" is the column that holds the usernames, and $username is the currently logged user.

Orio.

[NFD]

You mean:

[code]//run queries
$result['month']=mysql_query($sql['month']) or die(mysql_error());
$result['year']=mysql_query($sql['year']) or die(mysql_error());
$result['total']=mysql_query($sql['total']) or die(mysql_error());

$sum['month']=0;
$sum['year']=0;
$sum['total']=0;[/code]

What does the sum=0 in those do by thee way?

As for checking the user how do I add an additional WHERE that would include:
[code]WHERE customer_id = ".$db->mySQLsafe($ccUserData[0]['customer_id'])[/code]
to your queries?

Ive just added a few additional orders manually
- 2 more for today
- 1 for this day last month
- 1 for this day last year

[Orio]

The:
[i]$sum['month']=0;
$sum['year']=0;
$sum['total']=0;[/i]
Is just to declare these three vars, and give them a value of 0 so the while loop will add everything properly.


I dont know what this: $db->mySQLsafe($ccUserData[0]['customer_id']) means, but I think it's ok :D nvm that for now.

Still giving you a zero to everything after you added these records?

Orio.

[NFD]

Ah, ok, thanks for the explaination on that one.

Ignoring the user part for now, here is what your code input currently looks like:

[code]$now=time();
$sec['month']=60*60*24*30; //secs in 30 days
$sec['year']=60*60*24*364; //secs in 364 days
$month_ago=$now-$sec['month']; //timestamp of 30 days ago
$year_ago=$now-$sec['year']; //timestamp of 364 days ago


//create queries
$sql['month']="SELECT prod_total FROM ".$glob['dbprefix']."CubeCart_order_sum WHERE time>".$month_ago;
$sql['year']="SELECT prod_total FROM ".$glob['dbprefix']."CubeCart_order_sum WHERE time>".$year_ago;
$sql['total']="SELECT prod_total FROM ".$glob['dbprefix']."CubeCart_order_sum";

//run queries
$result['month']=mysql_query($sql['month']) or die(mysql_error());
$result['year']=mysql_query($sql['year']) or die(mysql_error());
$result['total']=mysql_query($sql['total']) or die(mysql_error());

$sum['month']=0;
$sum['year']=0;
$sum['total']=0;

//loop thru results and add
while($dollars=mysql_fetch_array($result['month'])){$sum['month']=$sum['month']+$dollars;}
while($dollars=mysql_fetch_array($result['year'])){$sum['year']=$sum['year']+$dollars;}
while($dollars=mysql_fetch_array($result['total'])){$sum['total']=$sum['total']+$dollars;}

//output
echo("Orders This Month: $".$sum['month']."<br>");
echo("Orders This Year: $".$sum['year']."<br>");
echo("Orders To Date: $".$sum['total']);[/code]

Note the change in :
[code]//create queries
$sql['month']="SELECT prod_total FROM ".$glob['dbprefix']."CubeCart_order_sum WHERE time>".$month_ago;
$sql['year']="SELECT prod_total FROM ".$glob['dbprefix']."CubeCart_order_sum WHERE time>".$year_ago;
$sql['total']="SELECT prod_total FROM ".$glob['dbprefix']."CubeCart_order_sum";[/code]

And I get:
[code]Fatal error: Unsupported operand types in /home/includes/content/viewOrders.inc.php on line 67[/code]
which is the line below //loop thru results and add

[Orio]

What's the value of $glob['dbprefix']?
And are you using mysql databses, and not another type of a database?

Orio.

[NFD]

That part worked all fine, but never mind, as I got King Arthurs to work! :D

All I need is the equivelent of what this would be

[code]$start_of_month = mktime(0, 0, 0, $mon, 1, $year);[/code]

But for start of year and all time.

[Orio]

I found a little error of my own, maybe that will fix the problem-
Change this:
$sec['year']=60*60*24*364 //secs in 364 days

To:
$sec['year']=60*60*24*364; //secs in 364 days

Orio.

[NFD]

Fixed that one up earlier by myself when it gave an error.
Still $0 on that.

Trying to get King Arthur's solution working now.

Need the mktime values as above.

And also, how to run 3 queries (1 for each)

[king arthur]

Start of year would be
[code]
$start_of_year = mktime(0, 0, 0, 1, 1, $year);
[/code]

and all time, just leave out the where clause altogether in the query.

[NFD]

Awesome.
Thankyou King Arthur :)

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

[king arthur]

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

[code]
$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);
[/code]

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).

[NFD]

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