embedded queries based on the results of previous query

[showman13]

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
Douglas

[showman13]

I am truly disappointed in what has become the response level in this forum.

This is the third question in a row that I've posted without a single response from anyone as to what I could try or do to solve the issue.

I have come up with a solution to this one as well, but it could I'm sure be a much more efficient solution if I had a little feedback from those that are much more knowledgable than I am in here...

This is my current solution, but hopefully before the matrix gets too large I will find a better way to do it.

Maybe it will be of assistance to someone else as a starting point...

<?php

function downline_count($under_mem) {

    $down_elem = array(); // start with an empty array each time
    $sql = "
      SELECT mem_id
      FROM members
      WHERE up1_id = '".$under_mem."'
    ";

    if($result=mysql_query($sql)) {
      while ($filled=mysql_fetch_array($result)){
        $down_elem[] = $filled[0];
      }
    }
    return $down_elem; // return array with 0 to 3 elements
  }

// Determine number of downline members
$down_ct = 0; // set counter
$downline = downline_count($default_id); // get initial array if one exists
$element_ct = sizeof($downline); // should be from 0 - 3 elements in array

if ($element_ct > 0){ // must be some downline on first level in matrix

  while (sizeof($downline) > 0){ // start walking array until it runs out
    ++ $down_ct;
    $under_mem = array_shift($downline);// remove and return first element
    $temp_array = downline_count($under_mem); // check for positions under it
    $downline = array_merge($downline, $temp_array);// merge elements with array
  }
  // when it drops out $down_ct should hold number in downline
?>

[pritaeas]

I am truly disappointed in what has become the response level in this forum.

That's a shame.

Your question is hard to answer. You yourself say it's cryptic, and you post no sample data, nor table structures, nor expected output. This is hard to filter out from the above text. Then again, it's only posted a day ago, so I can't say you've been patient.

From your first line I gather you need some kind of recursion, and I think that's only possible with a stored procedure.

[iamthwee]

Remember not ALL queries can be solved via SQL alone, you need some clever handling... this is where PHP or some other server side language comes into its own.

Looks like you've solved this.

[showman13]

Yes, I did solve it, and it works well, but the point of the question was to see if there was any way to do it within a query itself, because I was thinking it would have been more efficient.

I may be wrong with that thought process, and what I came up with works very well. Just not sure what it will do when we have 100K plus positions in someones downline.

thanks to both of you for at least responding and letting me know that my posts aren't completely ignored...;-)

[pritaeas]

I was thinking it would have been more efficient.

It's recursive so never very efficient. You can still try to create a stored procedure of the above and see if it matters perfomance wise.