I have a question that may be a bit complicated, so let me set it up with what I am working with first.
I am dealing with a 3 x 10 matrix, which I’m sure you realize means on level 1 there are 3 potential positions, and on level 2 there are 9 potential positions and level 3 – 27, and so on until you get to level 10 which has 59,049 potential positions.
These positions at any point within this entire matrix can be filled by any member that is in a position above them, so some of the levels may be full and some may not be, and they are not filled completely from left to right, the positions on any level can be filled in sporadically.
What I need to be able to do is to determine how many positions are filled on the 10 levels below any selected position(I’ll refer to this as the subject position) in the overall matrix.
Each position that is filled has a member record attached to it, that contains the member_id and the up1_id (which is the member_id of the position that it was placed under)
I know the long and drawn out way to do it would be to query the member database for the member_ids that have the subject position’s member_id recorded as their up1_id. This would produce up to 3 records that I would put into an array.
Then I would traverse this array and do the same query for each record, querying for the member_ids that have the member_ids in the array as their up1_id. This would produce up to 9 records that I would put in an array.
I think you can see where I’m going with this…
10 levels deep, with each level containing potentially 3 times as many elements in the array as the previous level.
This could be a long SLOW drawn out process to gather a total number on each level and an accumulated total overall…
What I’m wondering is if there are any magic formulas that could be used in a MySql Query to extract the required information, without doing it the way I have described?
Probably not simple, but I figured if anyone would know how to accomplish it, I would find them in here.
Thanks in advance for your positive feedback.