Janus13 Posted April 22, 2007 Share Posted April 22, 2007 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! Quote Link to comment Share on other sites More sharing options...
genericnumber1 Posted April 22, 2007 Share Posted April 22, 2007 How is your database set up, table/column wise? Quote Link to comment Share on other sites More sharing options...
Janus13 Posted April 22, 2007 Author Share Posted April 22, 2007 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. Quote Link to comment Share on other sites More sharing options...
genericnumber1 Posted April 22, 2007 Share Posted April 22, 2007 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. Quote Link to comment Share on other sites More sharing options...
Janus13 Posted April 22, 2007 Author Share Posted April 22, 2007 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 Quote Link to comment Share on other sites More sharing options...
genericnumber1 Posted April 22, 2007 Share Posted April 22, 2007 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. Quote Link to comment Share on other sites More sharing options...
Janus13 Posted April 22, 2007 Author Share Posted April 22, 2007 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... Quote Link to comment Share on other sites More sharing options...
genericnumber1 Posted April 22, 2007 Share Posted April 22, 2007 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... Quote Link to comment Share on other sites More sharing options...
Janus13 Posted April 22, 2007 Author Share Posted April 22, 2007 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. Quote Link to comment Share on other sites More sharing options...
genericnumber1 Posted April 22, 2007 Share Posted April 22, 2007 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. Quote Link to comment Share on other sites More sharing options...
Janus13 Posted April 23, 2007 Author Share Posted April 23, 2007 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. Quote Link to comment Share on other sites More sharing options...
Janus13 Posted April 24, 2007 Author Share Posted April 24, 2007 Bump just in case there are any other suggestions on this... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.