Jump to content

Archived

This topic is now archived and is closed to further replies.

NFD

By Time Period Queries

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 :(

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Can you post the script with the modifactions?

Orio.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
-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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
What's the value of $glob['dbprefix']?
And are you using mysql databses, and not another type of a database?

Orio.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
All is now working perfectly.
Thankyou very much to Orio and King Arthur
So very much appreciated :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.