How to get recursively all analogs from table by an article?

Question

I've create a simple table of analogs:

+----+-------+-------+
| id | sku_1 | sku_2 |
+----+-------+-------+
|  1 | a1    | abcd  |
|  2 | a2    | a3    |
|  3 | a3    | a1    |
+----+-------+-------+
3 rows in set (0.00 sec)

What it mean? It mean that product with article abcd has an analog with article a1, otherwise for example product with article a3 has an analog with article a1.

How to recursively get all the products from this table by a single article?

My solutions is wrong:

// Small Class to get analogs of products
class Analogs {

    public function get_analogs($sku)
    {
        if (!$sku) return false;

        $link = mysql_connect('localhost','','');
        mysql_select_db('test');

        $sku = mysql_real_escape_string($sku,$link);

        $query = mysql_query("SELECT * FROM analogs WHERE sku_1='".$sku."' OR sku_2='".$sku."'");

        while($analogs[]=mysql_fetch_assoc($query))
        continue;

        return $analogs;    
    }


    public function MixedAnalogs($sku)
    {
        if (!$sku) return false;

        $link = mysql_connect('localhost','','');
        mysql_select_db('test');

        $sku = mysql_real_escape_string($sku,$link);

        $query = mysql_query("select sku_1 sku from analogs where sku_2 = '$sku' UNION
                              select sku_2 sku from analogs where sku_1 = '$sku'");

        while($analogs[]=mysql_fetch_assoc($query))
        continue;

        return $analogs;
    } 


}

$mixed_analogs = AnalogsMix('abcd',$ids=array());

echo "<pre>";
print_r($mixed_analogs);
echo "</pre>";

// Recursive function to get analogs of analog
function AnalogsMix($sku,$ids=array())
{
    $class_analogs = new Analogs();
    $analogs = $class_analogs->get_analogs($sku);

    foreach ($analogs as $analog)
    {
        $cross = null;

        if ($analog['sku_1']==$sku)
        {
            $cross->sku = $analog['sku_2'];
        }
        else
        {
            $cross->sku = $analog['sku_1'];
        }

        $cross->id = $analog['id'];

        if (!in_array($analog['id'],$ids))
        {
            $ids[] = $analog['id'];
            $mixed[] = AnalogsMix($cross->sku,$ids);
        }
    }

    if (isset($mixed))
    {
        return $mixed;
    }
    else
    {
        return false;
    }
}

Answer 1

SQL UNION

select sku_1 sku from analogs where sku_2 = $yourid
union
select sku_2 sku from analogs where sku_1 = $yourid

Then you will get in results only ids of analogs.

Answer 2

Here, I suppose you have all your pairs in an array. For example, for your example, you would call analogsOf(array(array("a1", "abcd"), array("a2", "a3"), array("a3", "a1")), "abcd").

The idea is that you build a list of analogs containing initially only the string you are looking for and, every time you find an analog, you add it to the list of analogs and reiterate. You do so until you iterated the whole array of pairs without finding anything new.

function analogsOf(array $pairs, $key) {
    $res = array($key); // The result, with only the given key
    $i = 0;             // Index of the current item
    $changed = false;   // Have we added an item to $res during that iteration ?

    while ($i < count($pairs)) {
        $current = $pairs[$i];

        foreach ($res as $item) {
            if (($current[0] === $item) && (!in_array($current[1], $res)) {
                $res[] = $current[1];
                $i = 0;  // Reiterate as $res changed
            }
            else if (($current[1] === $item) && (!in_array($current[0], $res)) {
                $res[] = $current[0];
                $i = 0; // Reiterate as $res changed
            }
            else {
                $i++;  // Nothing found here, go to next item
            }
        }
    }

    return $res;
}

Note that this code was NOT tested, so there might be a few bugs here and there, but you've got the idea. Also note that I considered you could put the whole database content in an array, but that is probably not possible for obvious reasons, so you will probably have to adapt the code above.