filtering results from a recordset

Hi I had a quick look on Google, but thought it be quicker to ask on here and also to help others if anyone else requires this type of filtering....

Currently I have a statistics page on my ecommerce site which shows total costs of revenue..However I need a little direction in filtering other results.... for example I want to sum all the orders to show which month was the most popular month of the year, how much revenue was taken on a given month. popular product and popular category etc..

currently for total revenue for the website I am using the code:

<?php
$query="SELECT SUM(OrderAmount) AS `total` from orders";
$result=mysql_query($query);
$total=mysql_result($result,0,"total");
?>

but i want to expand on this to calculate what I have mentioned above.

Any direction appriciated, even if its a nice google link for me to experiment with :)

Chris

you need separate recordsets for each of these criteria, you cant use 1 recordset to return each of these items. for example, to return the sum by month:

SELECT SUM(OrderAmount) as orderTotal FROM orders GROUP BY DATE_FORMAT(OrderDate,'%m-%y') ORDER BY OrderAmount

that will tell both total by month and which moth had the highest revenue

to tell the most popular product, you would use a join query of the order details and products table to using the COUNT() of the orderdetails.productID column to determine which item was ordered most:

SELECT COUNT(DetailProductID) as numberOrderd,ps4_products.ProductName FROM ps4_orderdetails
INNER JOIN ps4_products ON ps4_orderdetails.DetailProductID = ps4_products.productID

popular category would be similar, you would just use a group by clause to group the count by the category.

Hey Jason, hmm ok. currently I have used only ONE recordset and with this I have been able to extract seperate calculations from this 1 recordset to include on my page statistics for:

Just to make sure we are both on the same page with my question...heres more details:

Currently...the page works with 1 recordset to display the following:

Total revenue is : £8,921.02
Total gift vouchers redeemed valued at : £90.00
Total coupons used valued at : £0.00
Total Current Orders : 128
Average Shopping basket spend :£69.70


using custom php as follows:

<?php

$query="SELECT SUM(OrderAmount) AS `total` from orders";

$result=mysql_query($query);

$total=mysql_result($result,0,"total");

?>

<?php

$query="SELECT SUM(OrderAmount) AS `atotal` from orders";

$result=mysql_query($query);

$atotal=mysql_result($result,0,"atotal");

?>

<?php

$query="SELECT SUM(OrderCoupon) AS `ctotal` from orders";

$result=mysql_query($query);

$ctotal=mysql_result($result,0,"ctotal");

?>

<?php

$query="SELECT SUM(OrderVoucher) AS `vtotal` from orders";

$result=mysql_query($query);

$vtotal=mysql_result($result,0,"vtotal");

?>

therefore I was hoping I could use the same method to create a query for what I wanted.

for example of the page it will show:

Total order revenue for current month : £1432.54
Total order revenue by month : August (this would be a link)
Total order revenue by tax year : 2013 (this would be a link)
Popular month for transactions : November
Popular day for transactions : Thursday
Popular product : Ball Bracelet
Popular category : Bracelets
Popular country : United Kingdom
Popular delivery method : Recorded Delivery

Couldnt I just use the same method with the PHP i included above to create the calculation into the php tags and then assign a variable to that query?

Chris

But this is using custom php to just execute multiple SQL Statements

that is exactly what i was saying, you need a separate SQL Statement to return each piece of info.

Hey Jason, with some direction from Ray, I have modified what I was doing...I just wanted to make sure that the calculation is correct.

so therefore for finding out the most popular product category...would this calculation work (its hard to test without having to manually go through all the orders in the database and work out the math lol)

$result = mysql_query('SELECT CategoryName FROM orderdetails INNER JOIN products ON orderdetails.DetailProductID = products.ProductID INNER JOIN productcategories ON products.ProductCategoryID = productcategories.CategoryID GROUP BY productcategories.CategoryName ORDER BY Count(*) DESC LIMIT 1') or die(mysql_error());
$PopularCategory = mysql_fetch_assoc($result);

Also for using the above and modifying to find results using dates...how would I calculate say for example - the revenue for the previous month then also for the current month....reason why i ask is the example you gave yesterday regarding to calculate the sum by month is the result gave a very unusual value since I was able to calculate the result manually it doesnt match the result from the recordset query.

Chris

I need to double check result.. but I think I worked out the date in terms of popular month for transactions:

<?php

$result = mysql_query('SELECT OrderDate FROM orders GROUP BY DATE_FORMAT(OrderDate, "%m") ORDER BY Count(*) DESC  LIMIT 1') or die(mysql_error());

$PopularMonth = mysql_fetch_assoc($result);

?>

Then to display this result:

<?php echo date('M',strtotime($PopularMonth['OrderDate'])); ?>

  (its hard to test without having to manually go through all the orders in the database and work out the math lol)  

The SQQL Looks correct, but I would have to do the same test you mention above to be sure.

Thanks Jason, It appears to work as I counted the orders in the database for that month....I just need to work out the use of date queries now, I setup this code to calculate for the revenue for current month (august in this case) however the result isnt correct... just wondered by looking at this code block it would be obvious for you to see my mistake?

$result = mysql_query('SELECT OrderAmount FROM orders GROUP BY DATE_FORMAT(OrderDate, "%Y-%m") = date_format(now() - INTERVAL 1 MONTH, "%Y-%m") ORDER BY SUM(OrderAmount)') or die(mysql_error());

$CurrentMonth = mysql_fetch_assoc($result);

Then to display as a month:

<?php echo($CurrentMonth['OrderAmount']); ?>

But as i mentioned it displays the wrong value.



Chris

You're using GROUP BY when you should be using WHERE