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.