Recursive mysql queries via PHP 1

[flugh]

I am so close, but I just can't quite grasp this! Some hand-holding would be appreciated

I have Fred. Fred owns Store A and Store B. Store A has Truck 1. Store B has Truck 1 and Truck 2.

We track deliveries for each truck individually, with each truck having a unique 'ID' in the clients table (clients.ID). I would like to loop my code in such a way that I can get query results for just Truck 1 or 2, or for Store B (which would include Truck 1 and 2) or Store A, or for Fred himself, which would include both stores and all trucks.

I know this isn't as hard as I'm making it out to be. A good push in the right direction, or outright code, would be greatly appreciated! I'm going thin on details, sorry. Not to be too verbose:

Table: clients
| ID | MID | full_name |

MID is the 'master ID', or the client that 'owns' the client. Any client with an MID of '0' will be the top owner (ie, "Fred&quot.

Sorry if this is a badly formed post. Long day at work ;-)

----
JBR

 

[skiflyer]

so uhm... I'm just going to wing some pseudo code out there, let me know if this is what you're thinking

get Whatever ID you want to start with, we'll do Fred's ID

$owned=array();
getChildren(&$owned, $fred'sid)

function getChildren(&$owned, $parent_id) {
  $sql = SELECT * FROM clients WHERE mid=$parent_id;
  foreach ($row) {
    $owned[]=$full_name
    getChildren(&$owned, $id);
  }
}

 

[flugh]

Thanks for the quick reply. Just got in from work (long day!).

I found a link and 'borrowed' this code from it (lost the link though, not even in my History that I can find):

function getClients($group_id, $found=NULL){
    $conn = mysql_connect($host, $user, $pass);
    mysql_select_db($db) or die ("Crud!");
     static $myarray;
     if (!$found) $found = array();
     $myarray[]=$group_id;
     $sql_query=mysql_query("SELECT * FROM dat_clients WHERE MID='$group_id'");
     while(list($list_children)=mysql_fetch_row($sql_query)){
          $found[] = $list_children;
          $return_value=getClients($list_children, $found);
     }//end while
     $final_array=$myarray;
     unset($myarray);
     return $final_array;
}//end function

Works well enough. So I did this:

                $wow = getClients($_POST['client'], NULL);
                echo "size is " . sizeof($wow);

and end up with a 'size is 7' or whatever is appropriate (the master, plus all the sub clients added up). Great, but now that I have an array with client.ID as values, what do you do with it? I'm figuring it's maybe a 'list' or something. Not sure with a regular array (can't find an array_fetch_row like for mysql ).

I will admit to not having done much research the last couple days. Sorry. Training a new manager to replace me so I can get this IT project rolling has me dead on my feet and whining for some wisdom handouts! ;-)

Thanks for you help.

----
JBR

 

[flugh]

Well, I got it working. An ugly kludge in the middle of it still though.

To refresh, here's the code I stole from the forgotten link (thanks to whoever put it on the 'net though), plus the last couple lines are mine:

function getClients($group_id, $found=NULL){
    $conn = mysql_connect("localhost", "jwadb", "jwadb");
    mysql_select_db("jwadb_be") or die ("damn");
     static $myarray;
     if (!$found) $found = array();
     $myarray[]=$group_id;
     $sql_query=mysql_query("SELECT ID FROM dat_clients WHERE dat_clients.MID='$group_id'");
     while(list($list_children)=mysql_fetch_row($sql_query)){
          $found[] = $list_children;
          $return_value=getClients($list_children, $found);
     }//end while
     $final_array=$myarray;
     unset($myarray);
     return $final_array;
}//end function
$wow = getClients($_POST['client'], NULL);
$added = "";
while (list($key, $ID) = each ($wow)) {
        $added = $added . "OR inv_stops.client = '" . $ID . "' ";
}

Then I end the query with this (the preceding INNER JOINs aren't anything special):

WHERE ddate = '" . date("Y-m-d", strtotime($_POST['ddate'])) . "' AND (0 " . $added . ")";

The (0 part is what is my kludge. I figured it was more efficent sql-wise than stating the same criteria twice (as any given client would be added to the $wow array during the recursive 'who is the child of this parent' query).

Anyway, nothing glamorous, just following up. Thanks skiflyer for your push in the right direction

----
JBR