PHP - Report Summary Page Help Needed

[revdave]

Hi all,

PHP - Report Summary Page Help Needed... As an example, I have a page where the user can select a few products, then I have a database PHP MySQL query that selects recent purchases of those products and also where they were purchased from like:

- query... select widget1 - widget2 and widget3

RESULT...

widget1, invoice 51, sold from store 1

widget1, invoice 72, sold from store 2

widget1, invoice 59, sold from store 1

widget2, invoice 2, sold from store 2

widget2, invoice 81, sold from store 1

widget3, invoice 201, sold from store 1

widget3, invoice 151, sold from store 2

widget3, invoice 17, sold from store 2

widget3, invoice 3, sold from store 1

-  I would like to generate report that can do some summary functions, so that the output report looks something like:

'SHOW PRODUCTS with STORE SUMMARY'

STORE 1

widget1, invoice 51, more fields here

widget1, invoice 59, more fields here

widget2, invoice 81, more fields here

widget3, invoice 201, more fields here

widget3, invoice 3, more fields here

STORE 2

widget1, invoice 72, more fields here

widget2, invoice 2, more fields here

widget3, invoice 151, more fields here

widget3, invoice 17, more fields here

--> I am curious how to construct the php to be able to simulate this 'STORE SUMMARY' on the report page. Any ideas are welcome.

THANKS IN ADVANCE - Dave

[Rob Hecker2 · Accepted solution]

Dave, your question is pretty broad, but perhaps the following will help:

MySQL has aggregate functions such as COUNT and SUM. These would probably be useful to you in your solution. I always try to do as much in the database as possible rather than dumping a lot of data into the script for PHP to process. Also look at the MySQL GROUP BY WITH ROLLUP as a way to return a grand total.

[revdave]

Ho Rob,

Yes - MySQL GROUP BY - I had forgotton about that - I'm sure that will help a lot! and never heard of MySQL GROUP BY *WITH ROLLUP* so I will check that out also. Thanks a lot!

[sinious]

Agreed with Rob that the database already has to grab this information and if it's indexed properly then the grouping functions of any SQL server will typically run faster than creating data structures in memory via PHP. It really depends on the data. Not in regards to speed, but in regards to how difficult it will be to construct your end data.

Plenty of times when data is being shown I like to access it via any server side means (PHP, service, API, etc) that provides JSON to a JavaScript framework so it can be sorted/parsed/etc on the client rather than server-side. If JavaScript is used to parse the data the work is offloaded to the client and you have a great deal of control on how display the data.

It's all on your end data needs and your comfort level with any specific language. Can you elaborate a bit more on the real data and what you really want it to do?

[revdave]

Hi sinious,

I and going to check out MySQL GROUP BY that Rob mentioned. But ultimately I will need to set it up as json to send back to  a JavaScript parse page... not sure how GROUP BY will work with JavaScript parse - but I'll give it a try...

so far I have only worked with the jQuery .each loop to display multiple returned records

If you have any early suggestions to work with 'GROUP BY' and JS parsing - I would love to know...

Thanks in advance - dave