Best way to compile data for display

PHP
1

In my database is a table consisting of job results that I would like to compile and display. The user will have 2 options for the displayed report, with and without details. So the query will pull all the rows from the table, combine all the values for a particular company name and display the company name as well as the various totals for other columns all combined for the non-detailed report.

If they choose the detailed report I would like to insert more rows between each company name with the per job results.

Here are some examples (the numbers may not be accurate, this is just to give an idea of the results I am wanting):

Non-Detailed report:
[table=“width: 500, class: grid, align: right”]
[tr]
[td]Company Name[/td]
[td]Job Amount[/td]
[td]Labor[/td]
[td]Materials[/td]
[td]Balance[/td]
[/tr]
[tr]
[td]ACME[/td]
[td]$20,000[/td]
[td]$5,000[/td]
[td]$4,000[/td]
[td]$11,000[/td]
[/tr]
[tr]
[td]Farming LLC[/td]
[td]$15,000[/td]
[td]$6,000[/td]
[td]$7,000[/td]
[td]$2,000[/td]
[/tr]
[tr]
[td]WidgetCo[/td]
[td]$10,000[/td]
[td]$2,000[/td]
[td]$4,000[/td]
[td]$4,000[/td]
[/tr]
[/table]

Detailed report:
[table=“width: 500, class: grid, align: right”]
[tr]
[td]Company Name[/td]
[td]Job Amount[/td]
[td]Labor[/td]
[td]Materials[/td]
[td]Balance[/td]
[/tr]
[tr]
[td]ACME[/td]
[td]$20,000[/td]
[td]$5,000[/td]
[td]$4,000[/td]
[td]$11,000[/td]
[/tr]
[tr]
[td]31001 - JobDescription[/td]
[td]$4,000[/td]
[td]$1,000[/td]
[td]$500[/td]
[td]$2,500[/td]
[/tr]
[tr]
[td]31293 - JobDescription[/td]
[td]$6,000[/td]
[td]$2,000[/td]
[td]$2,00[/td]
[td]$2,000[/td]
[/tr]
[tr]
[td]31547 - JobDescription[/td]
[td]$10,000[/td]
[td]$2,000[/td]
[td]$1,500[/td]
[td]$6,500[/td]
[/tr]
[tr]
[td]Farming LLC[/td]
[td]$15,000[/td]
[td]$6,000[/td]
[td]$7,000[/td]
[td]$2,000[/td]
[/tr]
[tr]
[td]31228 - JobDescription[/td]
[td]$15,000[/td]
[td]$6,000[/td]
[td]$7,000[/td]
[td]$2,000[/td]
[/tr]
[tr]
[td]WidgetCo[/td]
[td]$10,000[/td]
[td]$2,000[/td]
[td]$4,000[/td]
[td]$4,000[/td]
[/tr]
[tr]
[td]31179 - JobDescription[/td]
[td]$3,500[/td]
[td]$1,250[/td]
[td]$1,500[/td]
[td]$750[/td]
[/tr]
[tr]
[td]31473 - JobDescription[/td]
[td]$6,500[/td]
[td]$750[/td]
[td]$2,500[/td]
[td]$3,250[/td]
[/tr]
[/table]

Can someone suggest how best to build the MySQL queries to pull this data, and I’m also not exactly sure of the best method to store it (array(s) or whatever) to ease iteration through the results when constructing the HTML page for the end user.

Thanks in advance for any guidance anyone can offer.

Greg

2

I am not an SQL expert, but it would seem logical to use aggregates and groupings in your statement. For example:


select

company_name,
job_description,
sum(job_amount) as job_amount_sum,
sum(labor_amount) as labor_amount_sum,
sum(material_amount) as material_amount_sum,
sum(balance_amount) as balance_amount_sum

from

jobs

group by

company_name,
job_description

order by

company_name,
job_description

NOTE: these may not be entirely accurate, it’s just a rough estimation.

Then simply adjust your loop, based on whether or not the detail is requested, displaying either company_name or job_description in the first column.

This should get everything at once though. There are more efficient ways. For example, using ajax/jquery, you can start with a smaller set, and expand only the company you want by requesting the detail for that company only, and updating the html client-side. But that is beyond the scope of your question.

I’d also wait for a proper DBA to say whether my query sample is even viable.

3

Can you provide sample data from your table? I’m hoping that there is a separate table for your job descriptions…

4

Below is a snippet of a few lines of the database table used. This is a temp table where these details are compiled to from multiple other tables. Such as there is a Labor table, Materials table, etc. The code that populates this table upon the request of this report retrieves pertinent data from each of those tables and adds it where appropriate to this one. Altogether there are 4 temporary tables (a couple for labor (with different factors adjusting the values), one for materials, then this one. The final report is fed off this table however and all the info needed to display on the report is available here.

The only step that gets added, is in the basic (non-details) report, all the rows for the same company are added together to give JUST that companies overall status, if the end user would like details they can request the full report. I do like Serenarules suggestion of making it more dynamic with the user expanding that data per company if desired. I would need to add in an “Expand/Collapse All” feature, and need to go dig through some javascript/jquery books on how to put that together.

This app was originally written in the 90’s using MSAccess with an Access front-end. It was then re-written about 10 or so years ago using Java (since PHP/Javascript were considered inferior at the time). They now would like it redone again using PHP/Javascript as it doesn’t require any of the heavy lifting that Java includes (the original coder even agrees this would be much better written in a PHP/Javascript version, though he doesn’t have time to attack it so I have been tasked with it).

jobnum,company,description,startdate,amount,indcost,truelabor,materials,balance
10886,Acurate Systems, Inc.,Tool Number 151534,2011-11-04,3280,831.6096064608131,1152.62206525,1140,0
10054,Barque Corp,Container Tool,2011-01-21,5200,1318.40547365739,1455.7046285,829.34,0
10031,Cartec,43MM Right 57-40,2011-01-12,6500,1648.00684207173,4096.1760365,885.53,0
10032,Duo Tech,52MM Left 15-44,2011-01-12,6500,1648.00684207173,3436.05348075,75.9,0
10291,Duo Tech,15-23, 28MM Incremental Tool,2011-05-05,30000,7606.18542494646,7389.738895,3985.19,0

5

Is ‘description’ a tool or action performed at the job site? It looks like there should be multiple entries per job number? Is this correct?

EDIT: It almost seems like you are pulling this data from a query rather than a table. It seems like it starts to pull each individual task performed, but then calculated fields from all of those tasks, which should not be in the table…

6

There should be only a single line per job number as that is the “parent” group of a particular task being performed (only grouping that goes higher than that is multiple jobs in process for the same customer.

Basically each row of my sample tells you this:

jobnum = The Job Number assigned to this project
company = Who the customer is for this job
description = A “user friendly” description of this job (may include part numbers)
startdate = The date a purchase order was issued from the customer for this job
amount = The amount the job was agreed to be completed for
indcost = Indirect costs associated with this job (utilities, clerical staff, etc) based on formula’s created by the owner of the business
truelabor = Actual costs of labor (this is a machine shop, so there are true labor fees and “shop” labor fees that include the machinery costs, etc)
materials = Items pruchased specifically to complete this job such as raw metal, special cutting tools, etc
balance = Sum up indcost, truelabor, and materials then subtract it from amount to get an idea of whether this job was bid properly

indcost, truelabor, and materials are all compiled into this table from other sources, queries are run against those tables that pertain to each specific job number and then summed up into this table. The detailed descriptions for each of those is handled in other reports, this report is strictly a company wide, all active jobs, synopsis for upper management to view and keep an eye on how things are running throughout the company.

I have a feeling that the balance column can be removed from this table as the balances are calculated as the report is generated (a balance per/job as well as an overall customer balance). I suppose this could be handled in the mysql level of the app, but currently it is done as the report is generated.

Let me know if that didn’t give better clarification. Thanks for the input, it is all allowing me to view this from different angles I wouldn’t have thought of previously and that is always a valuable thing to have on your side. You start looking at code like this, determined in your head that you know how it should be functioning, getting that “head-slap” to get you to rethink it is something I highly value (and actually look forward to when posting to Sitepoints forums!)

Greg

7

Ok, so I was thinking this was a construction type of app, not a machinery shop. So basically your logging in any items that have been ordered to be “machined”. If so this looks like a text book… text book DB design example… I do believe there should be a significant redesign to the table structure here, but let me know if my assumption is correct.

8

Is this the original table that the order is placed into or is that pulled off another source as well?

9

This isn’t necessarily an order tracking type of system, it’s an internal system to monitor the overall project. As stated earlier this table is simply a temp table where the data is aggregated from various other tables to prepare it for the final report output.

10

In that case you should be looking to dump this ‘temp’ table and build a single query right from the original source(s). Can you supply a quick relational diagram?

11

So are you saying that the sql statements are fine, you get all the data you want, its just a matter of replicating the extra massaging / formatting that Java was previously doing and display it as an HTML table?

12

That will take a little time, this table is built from 3 other tables (all temp) that are created using 4 different queries to aggregate the appropriate data from acros the rest of the database structure. I’ll have to put all of that into something that is more precise than my current scattered notes and snippets of old code. Let me see if I can write the entire report in pseudo-code (actually probably should have done this awhile ago) and then I can post it for feedback.

13

Yes Cups, that is correct, I have already re-written the java part of the script that built the data tables and the data they contain is exactly what we want, what my original question was regarding was what others thoughts were on how to present that data to the end user. There will be 2 versions, 1 with JUST a by company list, if a company has 3 jobs in the shop those 3 jobs data are presented in a single line under the companies name, but if the user wants to see more specifically how work is flowing job by job through the shop, they can request the “detailed” report that still shows the customer totals row, but then right below it has a per job breakdown (each job in it’s own row).

I like the previous suggestion of using some ajax/jquery to allow them to show/hide those details in the final report (currently there is a checkbox where they set the report parameters for basic or detailed report). Doing it this way would allow them to see only 1 customers details if they so desired whereas the current design is an all or nothing. I would need to add some options for expand/collapse all, but figure that would be a fairly straightforward addition.

14

After looking back on this and I understand a little morewhats going on, I SUPPOSE you can achieve you desired result from your temp table already made… Not best practice though, IMO.

Non detailed:


select
company, 
sum(job_amount) as job_amount_sum,
sum(labor_amount) as labor_amount_sum,
sum(material_amount) as material_amount_sum,
sum(balance_amount) as balance_amount_sum
from
jobs
group by 
company
order by company asc

Detailed:


select
company, 
concat(jobnum, ' - ', description),
sum(job_amount) as job_amount_sum,
sum(labor_amount) as labor_amount_sum,
sum(material_amount) as material_amount_sum,
sum(balance_amount) as balance_amount_sum
from
jobs
group by 
company,
concat(jobnum, ' - ', description),
order by company asc

To use the detail view you can simply use something like excel to make a pivot table using company name… or get into a simple PHP iteration to display results in your desired fashion. NOTE: this will sum() up records with the same jobnum, ensure you dont have duplicate jobnums. If there are no duplicate jobnums, you can remove all aggregate functions from the second query.

15

Personally I’d leave the Ajax side of things till later ditto the hide/show rollups etc.

Just get the data in a straight html tables for now.

So we will take for granted that you have all the data request / transfer tied up and it gets you the data you want.

You seem to say you have 3 main tasks:

  • show all jobs summaries
  • show all jobs summaries along with their subtasks
  • show a single job along with its subtasks

Those first 2 could actually be the same query, you’d simply grab the all jobs summaries from the resulting array.

However that could involve moving a lot of extra unnecessary data, but you can go back and deal with that later.

What are you using to get the data out?
Which notation are you using to access this data? Arrays $row[‘name’] or Objects $row->name

How about you show us the result of say, a few rows of the data from your database, and show that as the result of:


echo var_export($results, 1);

rather than the usual


var_dump($results);

Where $results is an array of rows, a result set with some subtasks would be nice, along with a job that has no subtasks (or do they all have a minimum of 1 subtask?)

16

There will only be 2 main tasks:

  1. Show All Jobs summaries
  2. Show All Jobs summaries along with their subtasks

There is a different report for an individual job (where they can get details of HOW labor and materials costs have accrued).

Currently using Arrays $row[name] but am considering switching to Objects, though that would require another rewrite of some sections, but I can probably mix and match some of it and make the rest of the conversions at a later time. I’m more familiar with the Arrays, though I recognize the improved capabilities of using Objects and am going through some of the Sitepoint books to get me more up to speed on them.

I can’t quite provide output just yet, still putting together some of the other parts that lead up to outputting the final results, was just trying to get a little ahead of the game as I realized I was not completely clear on how I was going to handle the display of the results to the end user.

Greg