close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

filtering results from a recordset

Thread began 8/19/2013 2:43 pm by Christopher West | Last modified 8/20/2013 11:09 am by Jason Byrnes | 734 views | 7 replies |

Christopher WestCommunity Expert

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

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

Christopher WestCommunity Expert

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

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

Christopher WestCommunity Expert

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:
<?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:
<?php echo date('M',strtotime($PopularMonth['OrderDate'])); ?>
Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

Christopher WestCommunity Expert

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?

php:
$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:
<?php echo($CurrentMonth['OrderAmount']); ?>



But as i mentioned it displays the wrong value.



Chris

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...