Algorithm to retrieve all lines from MySQL

[superx335]

Hello everybody!

 

I am trying to figure out how to obtain all the data related to a key, but I've got no results so far and I am becoming really frustated, let's see if any of you could help me out with this.

 

Imagine I have a table with several columns, but we bother about two of them, let's say we have serial numbers of some product, on the left Incoming serial number (we can repair or swap the unit), on the right the outcoming serial number (same if we have repared the unit, different if we swap it for another unit).

 

Then we have, for example:

 

A -> A (Unit A enters and we repaired it)

A -> B (Same unit came another day for some reason and we couldn't repair it, so we swap it by giving B to the customer)

B -> C (Unluckily B was defective so we have to change it again)

C -> C (C had another problem and we repaired it)

 

We have that in the database from different days and the such, so now, we want to know the historical and we know "C". If we perform a SELECT * FROM... WHERE incoming/outcoming serial number = "C" we'll get:

 

B -> C

C -> C

 

So we should seek now for B and keep going... but I cannot proceed correctly, 'cause if I SELECT using B I'll get again B -> C (and A -> B, what I want), but when do I know I have to finish? How could I implement this as a function or whatever? showing every not repeated line from the beginning.

 

Could your minds help mine? Thank you very much in advance!.

[Barand]

A recursive search might do it

$sql = "SELECT
        id 
        , ser_in
        , ser_out
        FROM repair";
$res  = $db->query($sql);
$data = array();
while (list($id,$in,$out) = $res->fetch_row()) {
    $data[$out][] = [$in, $id];
}

history($data, 'C');

function history(&$data, $ser, $level=0) 
{
    foreach ($data[$ser] as $s) {
        $indent = str_repeat('--- ', $level);
        if ($ser != '')echo "$indent $s[0] | $ser  (record $s[1])<br>";
        if ($s[0]==$ser) {
            return;
        }
        history($data, $s[0], $level+1);
    }
}
/******** RESULTS **********
B | C (record 3)
--- A | B (record 2)
--- --- A | A (record 1)
C | C (record 4)

[superx335]

That looks well!!

 

Gonna try it and sending feedback!

 

Thanks in advance