Jump to content

By Time Period Queries


NFD

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?
Link to comment
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.
Link to comment
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.
Link to comment
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.
Link to comment
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 :(
Link to comment
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.
Link to comment
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.
Link to comment
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.
Link to comment
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?
Link to comment
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.
Link to comment
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

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.