循环遍历MySQL数据库，直到field ='指定值'

Question

I need some help please! Basically I have a system that has an unlimited amount of categories and the way in which it works is through unique IDs. So basically the system will find the root folder and match all subfolders based on its parent's UID. An endless loop...

But now I want to do the opposite of that in a single MySQL statement (if possible).

Basically I want it to do this.. (By the way this isn't my actual code, it's just how I want it to work)

SELECT UID FROM Table
WHERE UID = 'value'
--AND ALSO:
SELECT * FROM SameTable
WHERE UID = The Parent UID just fetched...

And do this until the UID = 'Specified Value'.

I seriously hope that makes sense!

Is it even possible? I could do it using multiple queries in a PHP loop I know, but that just feels like a long way around, and bad practice.

Answer 1

What you have is called "Hierarchical data". You have to read on it on google. In short, there are three main ways to represent it in a 2-dimensional table:

• Adjacency list (what you have). You scarcely can make it with single query
• Materialized path (my favorite). Natural and readable. Not so efficient though.
• Nested set (Most complicated) yet most powerful.

You can choose any system you like ir stick to your current one. Single query is not Holy grail to pursue at any cost.