Web Analytics Made Easy -
StatCounter improve a Query for hierarchical db to end and begin a "branch" in output - CodingForum

Announcement

Collapse
No announcement yet.

improve a Query for hierarchical db to end and begin a "branch" in output

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • improve a Query for hierarchical db to end and begin a "branch" in output

    i've finally arrived at a terrific query and php loop to get almost just what I want, but I could use someone's help with finding a way to define that, even though ASSOCS p_id 4 knows a_id 6, I don't want the next p_id 6 to stay in that branch, but to start a new one, while other times 1 » 3 » 4 » 4 » 6 IS correct. The FUNCTIONS that do this and the function calls are at the end. (god I hope all that makes sense):

    I have 2 tables:
    Code:
    PERSONS
    person_id   fname   lname      info
    1          LB           Johnson   "info..."
    2          Bobby      Baker      (null)
    3          Malcolm   Wallace   (null)
    4          Billy        Estes       (null)
    5          Clifton     Carter     (null)
    6          Howard   Hunt       (null)
    7          Frank      Sturgis    (null)
    
    ASSOCS
    p_id    a_id    p_to_a      a_to_p
    1       2      employs      worked for
    1       3      knows         knows
    3       4      workedwith  worked with
    4       6      knows         knows
    5       3      brother to    sister-in-law to
    6       7      wife of        husband of
    7       3      knows         knows
    5       1      test            test_
    and the query:
    Code:
    SELECT a.p_id, a.a_id, 
    	CONCAT(p1.fname,' ', p1.lname) as name1, 
    	p_to_a as association, 
    	CONCAT(p2.fname,' ', p2.lname) as name2
            FROM assocs a 
            INNER JOIN persons p1 ON p1.person_id = a.p_id
            INNER JOIN persons p2 ON p2.person_id = a.a_id
    and the PHP:
    PHP Code:
    $data = [];
    $res $db->query($sql);
    while (list(
    $pid$aid$n1, $***, $n2$nfo$nf) = $res->fetch_row()) {
        if (!isset(
    $data[$pid])) {
            
    $data[$pid] =  [ 'name' => $n1'assocs' => [] ];
            
    $data[$nfo] =  [ 'info' => $nf'assocs' => [] ];
        }
        
    $data[$pid]['assocs'][$aid] = ['name' => $n2'rel' => $***];
        
    // $data[$nfo]['assocs'][$aid] = ['info' => $nf, 'rel' => $***];
    }

    $processed=[];

    function 
    listAssociates($pid, &$data, &$processed$level)
    {
        if (!isset(
    $data[$pid])) {
            return;
        }
        if (
    in_array($pid$processed)) return; // prevent circular references
        
    $processed[] = $pid;
        
        if (
    $level==0) {
         echo 
    "<div class=''>{$data[$pid]['name']}</b> » </div>";
        }
            
    // $indent = str_repeat('&nbsp;', $level*10);
            
    $indent = ($level*25);
            
    $indent = ($indent+25)."px";
            foreach (
    $data[$pid]['assocs'] as $aid=>$adata) {
                echo 
    "<div style='margin-left:$indent'>{$adata['rel']} <b>{$adata['name']}</b></div>\n";
                
    listAssociates($aid$data$processed$level+1);
            }
    }

    listAssociates(1$data$processed0);
    listAssociates(6$data$processed0);
    listAssociates(5$data$processed0); 
    What it's doing is looping from ASSOCS (p_id) 3 » (a_id) 4 » (p_id) 4 » (a_id) 6 » (p_id) 6 » (a_id) 7, and because of this, it's skipping listAssociates(6...). I have no way of identifying when one "Branch" ends and another begins. The Output I'm getting is:
    Code:
    Lyndon B Johnson // listAssociates(1...)
       employs Bobby Baker
       knows Malcolm Wallace
            worked with Billy Sol Estes
                 knows E. Howard Hunt
                      wife of Frank Sturgis
                           knows Malcolm Wallace
    Clifton Carter 5 // listAssociates(5...)
       brother to Malcolm Wallace
       test Lyndon B Johnson
    but what I need is:
    Code:
    Lyndon B Johnson // listAssociates(1...)
       employs Bobby Baker
       knows Malcolm Wallace
            worked with Billy Sol Estes
                 knows E. Howard Hunt
    E. Howard Hunt // listAssociates(6...)
       wife of Frank Sturgis
            knows Malcolm Wallace
    Clifton Carter 5 // listAssociates(5...)
       brother to Malcolm Wallace
       test Lyndon B Johnson
Working...
X