some query assistance for a non-coder, please

glennn.php · 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? :rolleyes:

I have made some headway creating two tables that I think will almost, kinda do what I want, which is:

Phillips pid1
- Barnes pid2
  - Moore pid3
    - de Mohrenschildt pid4
      - Oswald pid5
- Hunt pid6
  - Sturgis pid7
    - Moore 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...?

requinix · 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?

Barand · 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

glennn.php · 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

glennn.php · 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

glennn.php · 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

glennn.php · September 28, 2016

Sensei - how in the world are you getting a value for $level...?

Barand · 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
        }

glennnall · 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
        }

glennnall · 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);

Barand · September 28, 2016

Yes, you've got it

glennnall · September 29, 2016

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

Sign In

some query assistance for a non-coder, please

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information