Jump to content

Algorithm to retrieve all lines from MySQL


superx335

Recommended Posts

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!.

Link to comment
Share on other sites

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)


Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.