This is a "meta" question that I am asking in a effort to better understand some tough nuts I've had to crack lately. Even if you don't get precisely what I'm reaching for here or there is too much text to read through, any practical input is appreciated and probably useful.
Assume you have a website that needs to use data that is stored in multiple tables of a database. That data will need to be iterated through in a multitude of ways, used for calculations in various places, etc.
So on a page that needs to display a collection of projects (from one db table) that each contain a collection of categories (from another db table) that each contain 1 or more items (from another db table) what is the best way to gather the data, organize it and iterate through it for display?
Since each project can have 1 or more categories and each category can have one or more items (but the items are unique to a specific category) what's the best way to organize the resulting pile?
My goal in the below example is to generate a table of projects where each project has the associated categories listed with it and each category has the associated items listed with it but I also need to aggregate data from the items table to display next to the project name
A Project Name (43 items and 2 of them have errors!)
- category 1
- item 1
- item 2
- category 2
- item 1
Another Project Name (12 items and no errors)
- category 1
- item 1
- category 2
- item 1
What I did was to retrieve the data from each table and stick it in a variable. Giving me something like:
var $projects = array("id" => 1, "proj_id" => 1, "name" => "aname");
var $categories = array("id" => 1, "cat_id" => 1234, "proj_id" => 1, "cat_name" => "acatname");
var $items = array("id" => 1, "item_id" => 1234, "location" => "katmandu");
Then I went through the variables in nested foreach() loops building the rows I needed to display.
I ran into difficulties with this as the foreach() loop would work fine when building something 2 levels deep (associating categories with projects) but it did not work as expected when went three levels deep (I N C E P T I O N .. hah, couldn't resist) and tried adding the items to each category (instead adding all of them to one item... first or last I don't recall which). Also, when something was present in the third level of the array, how would you add up that data and then get it out for use back up in the top level of the array being built?
I suppose I could have constructed a mega SQL query that did it all for me and put everything into a single array, saving me the loop confusion by flattening it out, but... well, that's why I'm here asking you all.
So, I suppose the heart of this question is: How do you handle getting lots of data from different tables and then combining it all for display and use in calculations?