Jump to content


Photo

By Time Period Queries


  • Please log in to reply
23 replies to this topic

#1 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 04:53 PM

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?

#2 king arthur

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

Posted 06 August 2006 - 05:15 PM

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

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.
$start_of_month = mktime(0, 0, 0, $mon, 1, $year);

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

Then query the database with this timestamp thus:
$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);

$total is the total amount so far this month.

HTH.

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.

#3 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 06 August 2006 - 05:17 PM

<?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']);

?>

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.
Think you're smarty?

(Gone until 20 to November)

#4 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 05:38 PM

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.

#5 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 06:37 PM

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 :(

#6 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 06 August 2006 - 06:45 PM

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.
Think you're smarty?

(Gone until 20 to November)

#7 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 06:56 PM

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.

#8 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 07:09 PM

Ok
No more errors, but still all $0 totals

#9 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 06 August 2006 - 07:53 PM

Can you post the script with the modifactions?

Orio.
Think you're smarty?

(Gone until 20 to November)

#10 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 07:58 PM

Sure.

<?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";
}
?>

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

#11 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 06 August 2006 - 08:04 PM

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.
Think you're smarty?

(Gone until 20 to November)

#12 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 08:15 PM

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?

#13 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 06 August 2006 - 08:21 PM

-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.
Think you're smarty?

(Gone until 20 to November)

#14 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 08:26 PM

You mean:

//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;

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:
WHERE customer_id = ".$db->mySQLsafe($ccUserData[0]['customer_id'])
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



#15 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 06 August 2006 - 08:30 PM

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

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.
Think you're smarty?

(Gone until 20 to November)

#16 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 08:38 PM

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

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

$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']);

Note the change in :
//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";

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

#17 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 06 August 2006 - 08:44 PM

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

Orio.
Think you're smarty?

(Gone until 20 to November)

#18 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 08:48 PM

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

$start_of_month = mktime(0, 0, 0, $mon, 1, $year);

But for start of year and all time.

#19 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 06 August 2006 - 08:56 PM

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.
Think you're smarty?

(Gone until 20 to November)

#20 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 06 August 2006 - 09:00 PM

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)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users