glennn.php Posted September 25, 2016 Share Posted September 25, 2016 firstly, I am not a db programmer. I ply my trade in WordPress stuff, but not in-depth db structuring from scratch. please keep that in mind as I do my best to ask this question? I have made some headway creating two tables that I think will almost, kinda do what I want, which is: Phillips pid1Barnes pid2Moore pid3de Mohrenschildt pid4Oswald pid5 Hunt pid6Sturgis pid7Moore pid3 I've created a (really large) outline in html that simply shows, in effect: Phillips (knows) » Barnes (supervised) » Moore (supervised) » de Mohrenschildt (knows) » Oswald ... AS WELL AS, Phillips (knows) » Hunt (knows) » Sturgis (followed, who also knows) » Moore (same, pid3) ... I put Sturgis followed by Moore specifically to show that these relationships are in no way numerically sequential from the ASSOCS table. There will be 4 or 500 Persons, (and 40 or 50 Organizations, once I get this solved). You can see an example of it here (there's lots of data going on in this outline): http://stemmonsfreeway.com/military-industrial-intelligence-anti-castro-syndicated So, I've come up with these tables: [persons] id, name ----------------- 1 Phillips 2 Barnes 3 Moore 4 de Mohrenschildt 5 Oswald 6 Hunt 7 Sturgis [assocs] p_id, a_id ---------------- 1 2 2 3 3 4 4 5 1 6 6 7 7 3 And I have this query: SELECT a1.p_id, p1.name AS 'Name', a2.p_id, p2.name AS 'FName1', a3.p_id, p3.name AS 'FName2' FROM assocs a1 JOIN assocs a2 ON a1.p_id = a2.a_id JOIN assocs a3 ON a2.p_id = a3.a_id JOIN persons p1 ON a1.p_id = p1.id JOIN persons p2 ON a2.p_id = p2.id JOIN persons p3 ON a3.p_id = p3.id WHERE p1.id = 1 AND p2.id = 2 AND p3.id = 3;which returns: 1 Phillips 2 Barnes 3 Moore *** The problem I have is where a trail ends, Phillips to Oswald, and starts again, Phillips to Hunt to Moore, for instance. *** What I need is a way to define an end to a string of associations and a start of the next one, perhaps with another field or two in the ASSOCS table, or another table... (I'd also love to be able to denote one of a few types of relationships, i.e. "friend" "foe" "supervised" "worked for" ...) *** I'm hoping some kind soul can help me with a query that can do this, and some advice on how to handle it in the tables I've started with...? Quote Link to comment Share on other sites More sharing options...
requinix Posted September 25, 2016 Share Posted September 25, 2016 You can't get an arbitrary number of columns in MySQL, so you have to find a way to represent all that information in multiple rows instead. I'd just do that with two queries, one for the contents of each table. Fetch all that into your PHP and then process it from there. But I have a question: why does the second list not continue from Moore to de Mohrenschildt and Oswald like the first list did? Or does it and you just didn't show that much in your example? And another question: what happens if you have a circular association, like Philips > Barnes > Moore > Philips? And one more: should the relationships be bidirectional? Philips knows Barnes, but doesn't that also mean Barnes knows Philips? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 25, 2016 Share Posted September 25, 2016 You could try something like this, storing the data into an array then processing that array recursively //********** DATA ********************************* SELECT person_id,fname,lname FROM person; +-----------+-------+------------------+ | person_id | fname | lname | +-----------+-------+------------------+ | 1 | Curly | Phillips | | 2 | Larry | Barnes | | 3 | Mo | Moore | | 4 | Peter | de Mohrenschildt | | 5 | Paul | Oswald | | 6 | Mary | Hunt | | 7 | Tom | Sturgis | +-----------+-------+------------------+ SELECT * FROM assoc; +----------+------+------+-------------------+------------------+ | assoc_id | p_id | a_id | p_to_a | a_to_p | +----------+------+------+-------------------+------------------+ | 1 | 1 | 2 | employs | works for | | 2 | 2 | 3 | knows | knows | | 3 | 3 | 4 | worked with | worked with | | 4 | 4 | 5 | knows | knows | | 5 | 1 | 6 | brother-in-law to | sister-in-law to | | 6 | 6 | 7 | wife of | husband of | | 7 | 7 | 3 | knows | knows | +----------+------+------+-------------------+------------------+ The processing $db = new mysqli(HOST,USERNAME,PASSWORD,DB); $sql = "SELECT p_id , a_id , CONCAT(p1.fname,' ',p1.lname) as name1 , p_to_a as association , CONCAT(p2.fname,' ',p2.lname) as name2 FROM assoc a INNER JOIN person p1 ON p1.person_id = a.p_id INNER JOIN person p2 ON p2.person_id = a.a_id"; $data = []; $res = $db->query($sql); while (list($pid, $aid, $n1, $ass, $n2) = $res->fetch_row()) { if (!isset($data[$pid])) { $data[$pid] = [ 'name' => $n1, 'assocs' => [] ]; } $data[$pid]['assocs'][$aid] = ['name' => $n2, '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 "<b>{$data[$pid]['name']}</b><br>"; } $indent = str_repeat(' ', $level*10); foreach ($data[$pid]['assocs'] as $aid=>$adata) { echo "$indent{$adata['rel']} <b>{$adata['name']}</b><br>\n"; listAssociates($aid, $data, $processed, $level+1); } } The results Curly Phillips employs Larry Barnes knows Mo Moore worked with Peter de Mohrenschildt knows Paul Oswald brother-in-law to Mary Hunt wife of Tom Sturgis knows Mo Moore 2 Quote Link to comment Share on other sites More sharing options...
glennn.php Posted September 27, 2016 Author Share Posted September 27, 2016 Dude, I believe you've nailed it. I didn't get a notif. for this, so I'm just now seeing it, but it looks more than perfect. I'm trying it now, and will come back and thank you... You could try something like this, storing the data into an array then processing that array recursively //********** DATA ********************************* SELECT person_id,fname,lname FROM person; +-----------+-------+------------------+ | person_id | fname | lname | +-----------+-------+------------------+ | 1 | Curly | Phillips | | 2 | Larry | Barnes | | 3 | Mo | Moore | | 4 | Peter | de Mohrenschildt | | 5 | Paul | Oswald | | 6 | Mary | Hunt | | 7 | Tom | Sturgis | +-----------+-------+------------------+ SELECT * FROM assoc; +----------+------+------+-------------------+------------------+ | assoc_id | p_id | a_id | p_to_a | a_to_p | +----------+------+------+-------------------+------------------+ | 1 | 1 | 2 | employs | works for | | 2 | 2 | 3 | knows | knows | | 3 | 3 | 4 | worked with | worked with | | 4 | 4 | 5 | knows | knows | | 5 | 1 | 6 | brother-in-law to | sister-in-law to | | 6 | 6 | 7 | wife of | husband of | | 7 | 7 | 3 | knows | knows | +----------+------+------+-------------------+------------------+ The processing $db = new mysqli(HOST,USERNAME,PASSWORD,DB); $sql = "SELECT p_id , a_id , CONCAT(p1.fname,' ',p1.lname) as name1 , p_to_a as association , CONCAT(p2.fname,' ',p2.lname) as name2 FROM assoc a INNER JOIN person p1 ON p1.person_id = a.p_id INNER JOIN person p2 ON p2.person_id = a.a_id"; $data = []; $res = $db->query($sql); while (list($pid, $aid, $n1, $ass, $n2) = $res->fetch_row()) { if (!isset($data[$pid])) { $data[$pid] = [ 'name' => $n1, 'assocs' => [] ]; } $data[$pid]['assocs'][$aid] = ['name' => $n2, '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 "<b>{$data[$pid]['name']}</b><br>"; } $indent = str_repeat(' ', $level*10); foreach ($data[$pid]['assocs'] as $aid=>$adata) { echo "$indent{$adata['rel']} <b>{$adata['name']}</b><br>\n"; listAssociates($aid, $data, $processed, $level+1); } } The results Curly Phillips employs Larry Barnes knows Mo Moore worked with Peter de Mohrenschildt knows Paul Oswald brother-in-law to Mary Hunt wife of Tom Sturgis knows Mo Moore Quote Link to comment Share on other sites More sharing options...
glennn.php Posted September 27, 2016 Author Share Posted September 27, 2016 Sensei, you Rock (that's American for "thanks, Mate"). This is going to be an enormous database and hopefully a terrific addition to the JFK Assassination community. I will add your name as an appreciated contributor when it's public, with your permission. Thanks so much, Mate. You could try something like this, storing the data into an array then processing that array recursively //********** DATA ********************************* SELECT person_id,fname,lname FROM person; +-----------+-------+------------------+ | person_id | fname | lname | +-----------+-------+------------------+ | 1 | Curly | Phillips | | 2 | Larry | Barnes | | 3 | Mo | Moore | | 4 | Peter | de Mohrenschildt | | 5 | Paul | Oswald | | 6 | Mary | Hunt | | 7 | Tom | Sturgis | +-----------+-------+------------------+ SELECT * FROM assoc; +----------+------+------+-------------------+------------------+ | assoc_id | p_id | a_id | p_to_a | a_to_p | +----------+------+------+-------------------+------------------+ | 1 | 1 | 2 | employs | works for | | 2 | 2 | 3 | knows | knows | | 3 | 3 | 4 | worked with | worked with | | 4 | 4 | 5 | knows | knows | | 5 | 1 | 6 | brother-in-law to | sister-in-law to | | 6 | 6 | 7 | wife of | husband of | | 7 | 7 | 3 | knows | knows | +----------+------+------+-------------------+------------------+ The processing $db = new mysqli(HOST,USERNAME,PASSWORD,DB); $sql = "SELECT p_id , a_id , CONCAT(p1.fname,' ',p1.lname) as name1 , p_to_a as association , CONCAT(p2.fname,' ',p2.lname) as name2 FROM assoc a INNER JOIN person p1 ON p1.person_id = a.p_id INNER JOIN person p2 ON p2.person_id = a.a_id"; $data = []; $res = $db->query($sql); while (list($pid, $aid, $n1, $ass, $n2) = $res->fetch_row()) { if (!isset($data[$pid])) { $data[$pid] = [ 'name' => $n1, 'assocs' => [] ]; } $data[$pid]['assocs'][$aid] = ['name' => $n2, '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 "<b>{$data[$pid]['name']}</b><br>"; } $indent = str_repeat(' ', $level*10); foreach ($data[$pid]['assocs'] as $aid=>$adata) { echo "$indent{$adata['rel']} <b>{$adata['name']}</b><br>\n"; listAssociates($aid, $data, $processed, $level+1); } } The results Curly Phillips employs Larry Barnes knows Mo Moore worked with Peter de Mohrenschildt knows Paul Oswald brother-in-law to Mary Hunt wife of Tom Sturgis knows Mo Moore Quote Link to comment Share on other sites More sharing options...
glennn.php Posted September 27, 2016 Author Share Posted September 27, 2016 incidentally, this is the raw html that i'm attempting to convert to database driven data: http://stemmonsfreeway.com/ - if you take a look, scroll down past the "Intro" and open "Military Industrial..." You'll see what I'm talking about, how much data there will be - this is only a start of the project. It's going to continue growing... You could try something like this, storing the data into an array then processing that array recursively //********** DATA ********************************* SELECT person_id,fname,lname FROM person; +-----------+-------+------------------+ | person_id | fname | lname | +-----------+-------+------------------+ | 1 | Curly | Phillips | | 2 | Larry | Barnes | | 3 | Mo | Moore | | 4 | Peter | de Mohrenschildt | | 5 | Paul | Oswald | | 6 | Mary | Hunt | | 7 | Tom | Sturgis | +-----------+-------+------------------+ SELECT * FROM assoc; +----------+------+------+-------------------+------------------+ | assoc_id | p_id | a_id | p_to_a | a_to_p | +----------+------+------+-------------------+------------------+ | 1 | 1 | 2 | employs | works for | | 2 | 2 | 3 | knows | knows | | 3 | 3 | 4 | worked with | worked with | | 4 | 4 | 5 | knows | knows | | 5 | 1 | 6 | brother-in-law to | sister-in-law to | | 6 | 6 | 7 | wife of | husband of | | 7 | 7 | 3 | knows | knows | +----------+------+------+-------------------+------------------+ The processing $db = new mysqli(HOST,USERNAME,PASSWORD,DB); $sql = "SELECT p_id , a_id , CONCAT(p1.fname,' ',p1.lname) as name1 , p_to_a as association , CONCAT(p2.fname,' ',p2.lname) as name2 FROM assoc a INNER JOIN person p1 ON p1.person_id = a.p_id INNER JOIN person p2 ON p2.person_id = a.a_id"; $data = []; $res = $db->query($sql); while (list($pid, $aid, $n1, $ass, $n2) = $res->fetch_row()) { if (!isset($data[$pid])) { $data[$pid] = [ 'name' => $n1, 'assocs' => [] ]; } $data[$pid]['assocs'][$aid] = ['name' => $n2, '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 "<b>{$data[$pid]['name']}</b><br>"; } $indent = str_repeat(' ', $level*10); foreach ($data[$pid]['assocs'] as $aid=>$adata) { echo "$indent{$adata['rel']} <b>{$adata['name']}</b><br>\n"; listAssociates($aid, $data, $processed, $level+1); } } The results Curly Phillips employs Larry Barnes knows Mo Moore worked with Peter de Mohrenschildt knows Paul Oswald brother-in-law to Mary Hunt wife of Tom Sturgis knows Mo Moore Quote Link to comment Share on other sites More sharing options...
glennn.php Posted September 28, 2016 Author Share Posted September 28, 2016 Sensei - how in the world are you getting a value for $level...? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 28, 2016 Share Posted September 28, 2016 it is passed as a parameter to the function $processed=[]; listAssociates(1, $data, $processed, 0); | | +---- 'level' value then incremented on subsequent calls foreach ($data[$pid]['assocs'] as $aid=>$adata) { echo "$indent{$adata['rel']} <b>{$adata['name']}</b><br>\n"; listAssociates($aid, $data, $processed, $level+1); <---- CALL AGAIN } Quote Link to comment Share on other sites More sharing options...
glennnall Posted September 28, 2016 Share Posted September 28, 2016 Thanks so much, and I apologize for being a pest - the one issue I'm having with this is how to denote where a new branch starts over at level '0' within the loop (see my image in the original question). I certainly don't think it's necessary to run a new query for each branch...? it is passed as a parameter to the function $processed=[]; listAssociates(1, $data, $processed, 0); | | +---- 'level' value then incremented on subsequent calls foreach ($data[$pid]['assocs'] as $aid=>$adata) { echo "$indent{$adata['rel']} <b>{$adata['name']}</b><br>\n"; listAssociates($aid, $data, $processed, $level+1); <---- CALL AGAIN } Quote Link to comment Share on other sites More sharing options...
glennnall Posted September 28, 2016 Share Posted September 28, 2016 ah - I think I've got it - i can just call the function as needed? listAssociates(1, $data, $processed, 0); listAssociates(6, $data, $processed, 0); Quote Link to comment Share on other sites More sharing options...
Barand Posted September 28, 2016 Share Posted September 28, 2016 Yes, you've got it Quote Link to comment Share on other sites More sharing options...
glennnall Posted September 29, 2016 Share Posted September 29, 2016 (edited) I had a problem with defining the beginning of a branch: so i did this: I 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_ 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>"; } right, I can't believe it worked, either. You're the best, Barand. You've helped me a lot. Now I'm ready to take this thing on the road... Edited September 29, 2016 by glennnall Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.