2

given these tables:

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_

The output (from the Query and Loops below) is this:

Lyndon B Johnson 1
   employs Bobby Baker 2
   knows Malcolm Wallace 3
      worked with Billy Sol Estes 4
         knows E. Howard Hunt 6
            wife of Frank Sturgis 7
               knows Malcolm Wallace 3
Clifton Carter 5 »
   brother-in-law to Malcolm Wallace 3
   test Lyndon B Johnson 1

I can see that it "chainlinks" 1 » 3 » 4, and because of this it's continuing the chain and does not start a new branch where I need it to (denoted here with » »).

(I'd also love it if someone could help me include this "info" field that I've added. I've made several attempts in the Query and PHP - i'll note them in the code at the bottom):

Lyndon B Johnson **<div>"info..."</div>** 
   employs Bobby Baker
   knows Malcolm Wallace
      worked with Billy Sol Estes
         knows Howard Hunt »
» Howard Hunt 
   wife of Frank Sturgis
Clifton Carter 
   brother to Malcolm Wallace
      test LB Johnson

This fantastic snippet/query works well - almost. What I need to do is either find a new way to define the associations between 2 persons in my ASSOCS table in such a way that a new branch is defined, or get this query and loop to do so in some fashion. It would probably take both, I'm thinking, but I'm not good with arrays or this depth of php. (I hope php is appropriate here - it seems to me to still be administration of a db.)

There are also my (commented) attempts at including the new "info" field.

$sql = "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,
    /*** i tried p3.info **/
        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
        /** INNER JOIN persons p3 ON p3.info = a.p_id **/
    ";

$data = [];
$res = $db->query($sql);
while (list($pid, $aid, $n1, $ass, $n2, /** $nf **/) = $res->fetch_row()) {
    if (!isset($data[$pid])) {
        $data[$pid] =  [ 'name' => $n1, /** 'info' => $nf, **/ 'assocs' => [] ];
    }
    $data[$pid]['assocs'][$aid] = ['name' => $n2, /** 'info' => $nf, **/, 'rel' => $ass];
}

$processed=[];

function listAssociates($pid, &$data, &$processed, $level)
{
    if (!isset($data[$pid])) {
        return;
    }
    if (in_array($pid, $processed)) return; // prevent circular references
    $processed[] = $pid;

    // this is where a branch starts
    if ($level==0) {
     echo "<div class=''>{$data[$pid]['name']}</b> » </div>";
    }
        $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);
        }
   /** I tried using various unset() or reset() here, to no avail **/
}

listAssociates(1, $data, $processed, 0);
listAssociates(6, $data, $processed, 0);
listAssociates(5, $data, $processed, 0);

very grateful for anyone's help. I really have tried hard to do this on my own.

1 Answer 1

0

SOLVED (yay!):

I simply put a Start ['s'] toggle, 1/0, in ASSOCS

ASSOCS
p_id    a_id   s   p_to_a      a_to_p
1       2      1   employs     worked for
1       3      0   knows       knows
3       4      0   workedwith  worked with
4       6      0   knows       knows
5       3      1   brother to  sister-in-law to
6       7      1   wife of     husband of
7       3      0   knows       knows
5       1      0   test        test_

and dug it out in my query (a.s AS s):

SELECT a.p_id, a.a_id, 
    CONCAT(p1.fname,' ', p1.lname) as name1,  
    p_to_a,
    CONCAT(p2.fname,' ', p2.lname) as name2, a.s AS s, a.info
        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

stored it and called it in place of the $level switch:

if (!isset($data[$pid])) {
        return;
    }
    if (in_array($pid, $processed)) return; // prevent circular references
    $processed[] = $pid;

    if ($data[$pid]['s']==1) {
     echo "<div class=''>{$data[$pid]['name']}</b></div>"; 
}

/* was » if ($level==0) { */

right, I can't believe it worked, either.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.