[RESOLVED] Problems with table structure and nested repeat

ChetUbetcha

I've seen this http://phpbuilder.com/board/showthread.php?t=10232764&highlight=nested+repeat and tried using the code, but I think I have the table structure wrong - only using 1 table for main and sub offices and 1 table for team leads and team members.

I want to display:

Main Office
...Sub Office
......Team Lead
..........Team Member
..........Team Member
..........Team Member
..........Team Member
..........Team Member

What would be the best approach for the table construction? 1 table with everything? 1 table for each element (total of 4)? If I build 4 tables, should they all share the same element of main_office?

After that, how do I get the nested repeat to work correctly?

Sorry, no code to show because I have not been able to get this even close to what I need. I have searched and read all the threads here by searching "nested" and "nested repeat" and even went to outside links that were posted. I have Googled "php nested repeat" and "php nested repeat 4 level" and read through tons of links.

:xbones:

Horizon88

Hey.

I had a similar problem recently, still re-working some of it. If you check out the threads I posted on it you should be okay.

http://phpbuilder.com/board/showthread.php?t=10343279

ChetUbetcha

Thanks. I'm still trying to decipher how to build the tables as well, and then I don't know which code will help display things properly.

The main office table would have:
main_office
main_office_manager
main_office_manager_email
main_office_poc
main_office_poc_email

The sub_office table would have:
main_office
sub_office
sub_office_manager
sub_office_manager_email
sub_office_poc
sub_office_poc_email

The team_lead office table would have:
main_office
sub_office
last_name
first_name
username
password
email

The team_member table would have:
main_office
sub_office
team_lead
last_name
first_name
username
password
email

So my dilemma is that I don't know if I should have four separate tables or one. ANd once that's solved, I still need to know how to parse everything correctly in nested loops.

paulnaj

Hi, I wrote this before seeing your table structure but I think it will still help.

There's always more than one way to do it but here's how I'd decide:

Main offices and suboffices: if both types have all the same details barring the fact that some are main and some are sub, then I would store all same db table. I'd have an extra field called 'main_office' or something and here store the primary key of the associated main office (leave it empty for actual Main offices who don't have parent offices).
Team leaders and members: Use same criterion: if the only difference between the two types is just that some 'members' are 'leaders' then one table should cover it. Here the extra field would be 'team_leader', say, where you would store the primary key of the team leader (and leaving it blank for actual team leaders). You'll also need a field to store the 'sub office' primary key.

Here's some pseudo-code to get you going on the nested loop:

RETRIEVE Main offices (SELECT * FROM offices WHERE main_office IS NULL)
FOREACH Main Office
--- #Assume we have a primary key called $main_office_id
--- PRINT Main Office Name
--- RETRIEVE Sub offices (SELECT FROM offices WHERE main_office = $main_office_id)
------- FOREACH Sub Office
----------- PRINT Sub Office Name
----------- #Assume we have a primary key called $sub_office_id
----------- RETRIEVE Team leader (SELECT FROM members WHERE sub_office = $sub_office_id AND team_leader IS NULL)
----------- PRINT Team Leader Name
----------- #Assume we have a primary key called $team_leader_id
----------- RETRIEVE Team members (SELECT * FROM members WHERE sub_office = $sub_office_id AND team_leader = $team_leader_id)
----------- FOREACH Team Member
--------------- PRINT Team Member Name
----------- END FOREACH (Team Member)
------- END FOREACH (Sub Office)
END FOREACH (Main Office)

ChetUbetcha

Thanks for the input. Funny thing, I was creating this that way to begin with. I had create and modify user pages, with a checkbox for team lead. I then had another section where you could assign people to a list of available team leads. I had a offices table with main and sub fields, and similar create and modify pages for both. I had display pages for teams and offices, but thought it would be better to display all on one page for ease of administration. I think I'm going to go back to my old approach (and yours) and see how things go.

paulnaj

Hi again,

Yeah, it's always 'fun' trying to sort out content into tables ... there are some general guidelines like 'don't have duplicate fields in separate tables except foreign ids'. Most are common sense but others are more abstract.

For instance, 'don't build your database to fit your HTML'. Store the data in the most logical format for the data itself, regardless of the HTML. One very good reason is that often you have to change the way you have to display the data and things could then get messy.

These things get more important for complex jobs. I don't always apply them.

PS. I noticed I'd made a mistake in the ordering of the pseudo-code ('PRINT Sub Office Name' came before the 'FOREACH Sub Office Name'), but it's fixed now.