How can I recursively search through a database?

Question

I have a MySQL table with 3 columns *ID, Item_ID, Dependency*

I want to know the best way to recursively check through the dependencies for example say i am checking for Item_ID 3's dependencies and it requires Item_ID 2 i would then need to check the dependencies for Item_ID 2

What is the best way to achieve this with PHP?

ID | Item_ID | Dependency |
---+---------+------------+
1  | 3       | 2          |
---+---------+------------+
2  | 2       | 4          |
---+---------+------------+
3  | 2       | 5          |
---+---------+------------+

do you want to keep going until dependency is null? What is your exit condition? — CFL_Jeff, Mar 08 '12 at 22:06
This has been asked numerous times already. Just search on stackoverflow or even google. — Basti, Mar 08 '12 at 22:07
also is dependency a self referencing columns (ie. to ID on the same table) r does it point at a different table(s)? — prodigitalson, Mar 08 '12 at 22:08
Check out this post: http://stackoverflow.com/questions/3704130/recursive-mysql-query — CFL_Jeff, Mar 08 '12 at 22:09

score 0 · Accepted Answer · answered Mar 08 '12 at 22:11

pseudo code:

func myQuery(item_ID) {
    query (item_ID)
    if (!item_ID.dependency())

    else (myQuery(item_ID.dependency())
}

This will continue down the column until you reach an item that does not have a dependency.

Summary of process: Pass the first item to the function. Check if it has a dependency, if it does, call the function again with that dependency. Otherwise do nothing and the function will return. of course you'll want to save the items returned by the query but I'll leave that to you. You would probably want that statement at the very end of the function so when all is said and done you have your data ordered from no dependencies to most dependencies.

score 0 · Answer 2 · answered Mar 08 '12 at 22:13

For a pure SQL solution:

If you are looking to perform a traversal of the hierarchy represented in the table and return the data in one query, you can use a series of LEFT OUTER JOIN statements against the table to "walk" the relationships - this works up to a specified depth (the number of JOINS = the depth of the tree). Then you need to decide what data you want to project (SELECT) out of this massively joined table.

How can I recursively search through a database?

2 Answers2