How to read hierarchical data in MySQL using self-joins?

Question

I have a table called "offers"; this is my structure:

|  id   | rep_id |
------------------
| 25704 | 25060  |
| 25060 | 24419  |
| 24419 | 23785  |
| 23785 |  NULL  |

Lets start with offer.id 23785 (the last row), it is the first offer therefore the repost id (rep_id) is NULL, after one day 23785 this will be reposted with a new id, this time it is 24419 and I know that it was 23785 and that 24419 will be 25060 and so on.

Now, my task is to call any of the above id's and identify how many times the offer has been reposted and to list all. For example if I say that I want to know how many times 25060 has reposted, it should result all the above id's because in a weird way they are connected, but I don't know how to tell MySQL that they are connected. I know this is not the best structure, but this is all I have.

My query:

SELECT go1.id, go1.rep_id
            FROM offers go1
            JOIN offers go2
            ON go2.rep_id = go1.rep_id
            WHERE go1.rep_id = 24419 OR go1.id = 24419

My result:

|  id   | rep_id |
------------------
| 25060 | 24419  |
| 24419 | 23785  |

Wanted result:

|  id   | rep_id |
------------------
| 25704 | 25060  |
| 25060 | 24419  |
| 24419 | 23785  |
| 23785 |  NULL  |

P.S. If MySQL can't do this alone, how will PHP do this?

Answer 1

Here is how you can do it with MySQL query. No need to use PHP for this:

SELECT 
    Id, 
    rep_id  
FROM(
    SELECT
        @r AS _id,
        (SELECT @r := Id FROM posts WHERE rep_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r :=23785, @l := 0) vars,posts m
    WHERE @r <> 0) T1
JOIN posts T2
    ON T1._id = T2.Id
GROUP BY Id
ORDER BY T1.lvl DESC;

OUTPUT

|    ID | REP_ID |
|-------|--------|
| 25704 |  25060 |
| 25060 |  24419 |
| 24419 |  23785 |
| 23785 |      0 |

SQL Fiddle Demo

Take a look at this

 (SELECT @r :=23785, @l := 0) vars,

You just need to provide starting id as i have provided 23785

With id 25060

OUTPUT

|    ID | REP_ID |
|-------|--------|
| 25704 |  25060 |
| 25060 |  24419 |

SQL Fiddle Demo

More details about how this works can be found here.

If you want to provide some middle id then use this query

SELECT 
    * 
FROM (
    (
    SELECT 
        num,    
        Id, 
        rep_id  
    FROM(
        SELECT
            @r AS _id,
            (SELECT @r := Id FROM posts WHERE rep_id = _id) AS parent_id,
            @l := @l + 1 AS lvl
        FROM
        (SELECT @r :=25060, @l := 0) vars,posts m
    WHERE @r <> 0) T1
    JOIN posts T2
        ON T1._id = T2.Id
    GROUP BY Id
    ORDER BY T1.lvl DESC
    ) UNION (
    SELECT 
        num,
        Id, 
        rep_id  
    FROM(
        SELECT
            @r AS _id,
            (SELECT @r := MAX(rep_id) FROM posts WHERE Id = _id) AS parent_id,
            @l := @l + 1 AS lvl
        FROM
        (SELECT @r :=25060, @l := 0) vars,posts m
        WHERE @r <> 0) T1
    JOIN posts T2
        ON T1._id = T2.Id
    GROUP BY Id
    ORDER BY T1.lvl DESC )
)l 
ORDER BY num

SQL Fiddle Demo

OUTPUT

| NUM |    ID | REP_ID |
|-----|-------|--------|
|   1 | 25704 |  25060 |
|   2 | 25060 |  24419 |
|   3 | 24419 |  23785 |
|   4 | 23785 |      0 |