adding a field to an array

[glennn.php]

a really good dude put this Query/output together for me, where i've now tried to add a new field, "info" to the 'persons' table,
and am unable to query it correctly within the array:

person_id|      fname     |      lname      |    [[ info ]]
1           Lyndon B            Johnson          some info here...
2           Bobby               Baker            (null)
3           Malcolm             Wallace          (null)
4           George              de Mohr          (null)
5           Lee Harvey          Oswald           (null)
6           Howard              Hunt             (null)
7           Frank               Sturgis          (null)

and the query and output (i've wrapped my attempts in [[ ]]):

 

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

I'm not good with arrays at all, but i've given this a few different tries, and end up echoing the "info" everywhere in the output but where I want it to be, which is along with the name of the particular person_id of its row, of course:

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

$processed=[];
listAssociates(1, $data, $processed, 0);

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(' ', $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>  [[ {$adata['info']} ]] \n";
            listAssociates($aid, $data, $processed, $level+1);
        }
}

 

could some kind person help me include this field in the query and the output, please?

Edited September 28, 2016 by glennn.php

[Barand]

No need for kinky three-person relationships. You would get the info from persons p1 and persons p2. Store it and use it in the same way that the name is used.

[glennnall]

SOLUTION:

Instead of adding the field to PERSONS, I added put it in ASSOCS:

p_id    a_id     p_to_a     a_to_p         info
1        2      employs   worked for     "info..."
1        3       knows      knows         (null)
3        4    worked with worked with     (null)

 

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.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

 

 

 

the output ($nf, 'info' => $nf, $data[$pid]['info'], $adata['info']):

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


$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) {
     if ($data[$pid]['info'] != '') {
     echo "<div class=''>{$data[$pid]['name']}</b></div><div style='margin-left:0px;'>{$data[$pid]['info']}</div>";
     } else {
echo "<div class=''>{$data[$pid]['name']}</b> » </div>";
}
}
        // $indent = str_repeat(' ', $level*10);
        $indent = ($level*25);
        $indent = ($indent+25)."px";
        foreach ($data[$pid]['assocs'] as $aid=>$adata) {
            echo "<div style='margin-left:$indent'>{$adata['info']}</div><div style='margin-left:$indent'>{$adata['rel']} <b>{$adata['name']}</b></div>\n";
            listAssociates($aid, $data, $processed, $level+1);
        }
}