I was wondering if there is any way to do a single mysql query to do a count of filled positions in a multi-level structure to an indefinite depth.
That sounds a bit cryptic, but this is what I would like to do if it is at all possible.
we are working within a 3 x ___ matrix meaning
level 1 has 3 positions
level 2 has 9 positions
level 3 has 27 positions
and so on...
If a sponsor wants to know how many positions are filled within his downline I would like to be able to provide that for them.
But, the only way that I can envision it happening is to first do a query for the 'mem_id' of records with an 'up1_id' that matches the 'mem_id' in the sponsors record.
This would return a maximum of 3 records from level 1 and then store the mem_id from each of them in an array.
and at the same time incrementing a counter for each on found
Then I would need to walk that array, using each 'mem_id' and run the same query for the 'mem_id' of records with an 'up1_id' that matches the 'mem_id' in the array.
This would return a maximum of 9 records from level 2 and then store the mem_id from each of them in an array.
and at the same time incrementing a counter for each on found.
And so on and so on.... until I ran into a level that didn't have any matching records
At which point the counter would hold the number I was looking for...
But that just looks like it would be very laborious and slow in processing, and could potentially create a memory issue for those with very deep downlines (in the 10s of thousands)
Another way to do the same thing would be to go through the same process, but to actually just push results onto the bottom of a single array, as the array is being walked from the top down, and when you run out of array elements, you would be done...
This might be a little faster and cleaner, but still a slow process...
So, the core question is there a way using mysql to accomplish this same thing in a single query? I know it wouldn't be a simple one, but can it be done, and if so, could anyone help me to create the query to accomplish this?
The only important fields in the member record for this purpose are the mem_id - 8 digit integer the is the primary key to the table, and the up1_id, which is also an 8 digit integer that is assigned when the member is placed in the matrix.
Once I get the base structure determined, I would want to be able to do the query based on the value of one other field in the member record, which is mem_status, so the sponsor can get a count of downline in various status values.
Hopefully this is understandable and someone can give me some feedback.
Sorry for the length of the post, but no other way to get the information across.
Thanks you in advance