Finding all parents in mysql table with single query (Recursive Query)

I have this schema

Sample Data

| ID |             TITLE | CONTROLLER |            METHOD | PARENT_ID |
|----|-------------------|------------|-------------------|-----------|
|  1 |         Dashboard |      admin |         dashboard |         0 |
|  2 |           Content |      admin |           content |         0 |
|  3 |           Modules |      admin |           modules |         0 |
|  4 |             Users |      admin |             users |         0 |
|  5 |          Settings |      admin |          settings |         0 |
|  6 |           Reports |      admin |           reports |         0 |
|  7 |              Help |      admin |              help |         0 |
|  8 |             Pages |    content |             pages |         2 |
|  9 |             Media |    content |             media |         2 |
| 10 |          Articles |    content |          articles |         2 |
| 11 |            Menues |    content |            menues |         2 |
| 12 |         Templates |    content |         templates |         2 |
| 13 |            Themes |    content |            themes |         2 |
| 14 |              Blog |    content |              blog |         2 |
| 15 |             Forum |    content |             forum |         2 |
| 16 |      Core Modules |    modules |       core_module |         3 |
| 17 |      User Modules |    modules |       user_module |         3 |
| 18 |         All Users |      users |         all_users |         4 |
| 19 |            Groups |      users |            groups |         4 |
| 20 |       Permissions |      users |       permissions |         4 |
| 21 | Import and Export |      users |     import_export |         4 |
| 22 |        Send Email |      users |         send_mail |         4 |
| 23 |     Login Records |      users |     login_records |         4 |
| 24 |  General Settings |   settings |  general_settings |         5 |
| 25 |    Email Settings |   settings |    email_settings |         5 |
| 26 |   Popular Content |    reports |   popular_content |         6 |
| 27 | Most Active Users |    reports | most_active_users |         6 |
| 28 |     Documentation |       help |     documentation |         7 |
| 29 |             About |       help |             about |         7 |
| 30 |          Products |   products |           product |        17 |
| 31 |        Categories | categories |          category |        17 |

SQL Fiddle demo.I have inserted some sample data.

Challange

I need to find all the parents of the record where the title is Categories. How can I get all the parents with only a single query?
I mean I need this result:

Desired Output

id | title        |  controller  | method      | url     | parent_id 
----------------------------------------------------------------  
3  | Modules      |   admin      | modules     | (NULL)  | 0           
17 | User Modules |   modules    | user_module | (NULL)  | 3           
31 | Categories   |   categories | category    | (NULL)  | 17

Let suppose I want to fetch an entry with all of its parent and I want to use the where condition id = 31, it should fetch above records.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

SELECT T2.id, T2.title,T2.controller,T2.method,T2.url
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 31, @l := 0) vars,
        menu m
    WHERE @r <> 0) T1
JOIN menu T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;

Demo

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0