Fetch all related parent and child ids by given ID

Question

I have a table 'table' with following structure

   ID   LinkedWith
    12     13
    13     12
    14     13
    15     14
    16      0
    17      0
    18     21

We are given an ID say "12" (or may be 15), with it we have to fetch all other related IDs.

Here 12 is linked to 13, 14 is linked to 13, and 15 is linked to 14 and so on. In our case, there are 4 IDs indirectly related, there could be many. For example, in above case we need the final result of IDS 12,13,14,15.

I have got this incomplete function, but couldn't think of further. I know we might need recursive function but not sure how to do that

function testFetchRelated($id){
        $arrayIDs = array($id);


        try{
            $dbh = new PDOConfig("db_test");
            $stmt = $dbh->prepare("SELECT * FROM table WHERE ID='".$id."' OR LinkedWith='".$id."'");
            $stmt->execute();

            if($stmt->rowCount()>0){
                $fetch = $stmt->fetchAll(PDO::FETCH_OBJ);

                foreach($fetch as $f){
                    if($f->LinkedWith>0){
                        array_push($arrayIDs, $f->LinkedWith);
                    }

                    if($f->ID!=$id){
                        array_push($arrayIDs, $f->ID);
                    }
                }
            }else{

            }

            $stmt = null;
            $dbh = null;

            return $arrayIDs;
        }catch(PDOException $e){
            echo "AN ERROR OCCURRED: ".$e->getMessage();
        }

Can anybody help me on this

Answer 1

I have knocked this up which does seem to do what you want in a MySQL procedure:-

DELIMITER ;;

DROP PROCEDURE IF EXISTS `find_relations`;;
CREATE PROCEDURE `find_relations`(IN_id INT)
BEGIN

    CREATE TABLE ids_tmp 
    (
        ID  INT(11),
        PRIMARY KEY (ID)
    );

    INSERT INTO ids_tmp
    SELECT a.ID FROM some_table a WHERE a.LinkedWith = IN_id
    UNION 
    SELECT a.LinkedWith FROM some_table a WHERE a.ID = IN_id
    UNION 
    SELECT a.LinkedWith FROM some_table a WHERE a.LinkedWith = IN_id
    UNION 
    SELECT a.ID FROM some_table a WHERE a.ID = IN_id;

    WHILE (ROW_COUNT() > 0) DO
        INSERT INTO ids_tmp
        SELECT a.ID FROM some_table a 
        INNER JOIN ids_tmp b ON a.LinkedWith = b.ID 
        LEFT OUTER JOIN ids_tmp c ON a.ID = c.ID 
        WHERE c.ID IS NULL
        UNION 
        SELECT a.ID FROM some_table a 
        INNER JOIN ids_tmp b ON a.ID = b.ID 
        LEFT OUTER JOIN ids_tmp c ON a.LinkedWith = c.ID 
        WHERE c.ID IS NULL;
    END WHILE;

    SELECT ID FROM ids_tmp;

    DROP TABLE IF EXISTS ids_tmp;

END;;

DELIMITER ;

You can invoke it using CALL find_relations(12);

Not full debugged it (it would be better to use a temporary table, but it relies on accessing the same table multiple times in various single queries, which MySQL doesn't support for temp tables), but hopefully give you some ideas.

If you want to actually use this then probably need to ensure that the created table has a name that is unique for the session.