Pulling multiple layers of information from table

[Janus13]

I'm trying to query information in a db table that is "layered" for lack of a better term. This of it in the sense of a pyramid of information like below, so that you have one or more level 1 people, then under each level 1 person will be multiple people, then under each level 2 person would be multiple level 3, etc etc, if that makes sense.

 

            Level 1                                                              Level 1

      Level 2a                            Level 2b                      level 2    level 2

L3(under 2a)  L3  L3 L3      L3(under 2b)  L3  L3

 

The layers can continue for several more layers. I have it indexed by a main ID number that each has, and on each line is the preceding level person's ID. What I need to be able to display is from level x down as far as it goes. So that if I searched on a level 1 entry I want to be able to display all levels under that entry. The initial search is easy enough, but going down additional levels is that part I'm having trouble with. Would it be just a complex SQL query, or would I add additional index entries and search on those? I hope I've explained it good enough to make sense. Any ideas on how I might accomplish the data query? Thanks!

 

[genericnumber1]

How is your database set up, table/column wise?

[Janus13]

It's a single table, about 60 fields (columns) in the table. The index is the id number, but there is a second id field for the above tier ID number, which is how I can query the direct line of sight.

[genericnumber1]

I think you're confusing rows with columns

 

a row is information stored and a column is the info stored.. for instance

 

Table: Users

Columns: UserId, Username, Password, BloodType, ListOfFears, Social, Address, Allergies

Row: 0, Joe, MyPass, B+, Rabbits, 123-45-6789, 12345 Fake Street, Corn/Wheat

 

We need to know the table/column names so we can help you out with planning your script you're kind of hinting at it, but exact column names would definitely help.

[Janus13]

No, I know what they are, it's just that it's a large table so I was trying to keep it in general terms, but if you think he'd help to have all the column names here they are:

 

distributornumber, felonyquestion, associatefelonyquestion, sponsoringdistributorname, sponsoringdistributornumber, sponsoringdistributorphonenumber, placingdistributorname, placingdistributornumber, placingdistributorphonenumber, applicantassignnumber, applicantssn, federaltaxid, operatingcompany, lastname, firstname, driverslicensenumber, address, apptnumber, suitenumber, city, state, zipcode, todaysdate, dob, homephone, cellphone, businessphone, faxnumber, emailaddress, licensefee, subscriptiontype, paymentmethod, draftagreementsignature, draftfinancialinstitution, drafttelephone, draftaddress, draftcity, draftstate, draftzipcode, draftroutingnumber, draftAccountNumber, licensefeeamountcharged, subscriptionfeeamountcharged, drafttotalcharge, directlicensefeeamountcharged, directsubscriptionamountcharged, directtotalcharge, creditcardnumber, creditcardexpirationdate, creditcardcvvcode, cclicensefeeamountcharged, ccsubscriptionfeeamountcharged, cctotalcharge, directdepositbank, directdepositbankphonenumber, directdepositbankaddress, directdepositbankcity, directdepositbankstate, directdepositbankzip, directdepositbankroutingnumber, directdepositbankaccountnumber, directdepositbankaccounttype, agreement, agreementsignature

 

[genericnumber1]

Ah.. wow you really did mean 60 columns.... sorry, totally my mistake...

 

Well how you could do this is one query per request...

 

Assuming the following...

 

User, TierID, ParentTierID

Jan, 1, null

Joe, 2, 9

Jack, 3, 9

 

How I would do it is store the tierID in the form and have that submitted, then query for all entries with the ParentTierID of the submitted ID

 

For instance if you were doing a select you could have <option value="9">Jan</option>...

 

I hope this makes sense and is what you were wondering... there are a few other ways of doing this but this is the most simple.

[Janus13]

Not exactly. This is basically going to be for a reporting page. What I need to be able to display could be 2 entries, or 20 depending on how high in the tier the person looking information up is. Think of it like an org chart (just thought of this, and it's easier to explain). Say you have a CEO up top, followed by CFO, CIO, COO, then under each of those areas you have 5 direct reporting managers, then under each of those you have 5 managers, etc etc. Well the main report would be to pull up who is under the person querying.. bascially an org chart query based on the person making the request, so if the CEO pulled the report he'd see everyone, but if the CFO pulled up his org chart he'd only see his information and his part of the chart. Make sense?  The bottom level is easy since all that would need to be listed would be the single manager and his/her direct report people in a simple form. It gets more complicated, and it starts to lose me when it gets to the next level, since that person would see himself, his direct managers, but should also see the direct managers employees. Then the next level would need the same.. this is where it gets difficult. Can you do a variable query with a single query with only 2 fields as indexes...

[genericnumber1]

I'm still not quite sure I understand what's going on here.

 

So you want a user to be able to see everyone at a level lower than them?

 

If that's the case it's a simple lesser/greater than

 

User, TierID, Profession

Jan, 1, Manager

Joe, 2, Junior Manager

Jack, 2, Junior Manager

George, 3, Dish Washer

 

Then you could just do a query for all TierID greater than the current user's ID

 

For instance if Jack were to pull up everyone under him

SELECT * FROM table WHERE TierID > 2

it would only be George...

 

But if it were Jan to pull it up (WHERE TierID > 1) it would be everyone under her, which is everyone else

 

You could store the person's TierID in a session variable when they log in, or something similar...

 

Hopefully I understood what you're asking this time...

[Janus13]

Well you've given me an idea I haven't previously thought of. I'll add a new field, tier (like in your example), and all I really have to do is determine how to assign the tier number dynamically to each new person (since they wont know what that is when they are adding themselves.

[genericnumber1]

Well you could always create an array of the titles with their respective tier numbers..

 

array(

  'Manager' => 1,

  'Junior Manager' => 2,

  'Dish Washer' => 3

)

 

Just throwing ideas out there... people often use constants in this way but in your case (multiple titles) I think it would probably be easier to use an array.

[Janus13]

The org chart was really just an example of what I'm doing. It's a distributor database, with the tiers not being specifically named, which makes this a bit harder than I'd like, but you have given me a much easier way than I was thinking of trying to do it previously.

 

If, for example, I have people with numbers:

 

ID        Upstream

A0       

A1        A0

A2        A1

A3        A1

A4        A2

A5        A2

 

As additional people were added it would fan out similar to a pyramid (can't think of a better way to describe it)

 

Each of the entries in the table would be in order by the ID number index, and the upstream column is the number of the person in the organization the person is under. So if I query ID A2 I would want to see everyone under him/her. The tier isn't as straight forward as I would like since I can put tier #'s on everyone, but I also have to tie them to someone else above them that a single number can't do, but I have a new idea that may work. Thanks for the help in getting in a direction that may work.

 

If you have any other suggestions I'd be happy to hear them.

[Janus13]

Bump just in case there are any other suggestions on this...