|
|
Posted by strawberry on 02/02/07 19:20
On 2 Feb, 00:58, "Phil Latio" <phil.la...@f-in-stupid.co.uk> wrote:
> Let's say I have a simple web application running with just two MySQL
> tables. The tables structure is as follows:
>
> Table: category
> category_id (PK)
> category_name
> parent_category (FK) //references category_id in this table
>
> Table: link
> link_id (PK)
> link_name
> link_description
> category_id (FK) //references category_id in category table
>
> Here is the data in the category table
> 1, England, 0
> 2, West Yorkshire, 1
> 3, Batley, 2
> 4, Leeds, 2
> 5, Bradford, 2
>
> As you can see Batley, Leeds and Bradford are sub-categories of West
> Yorkshire which itself is a sub-category of England.
>
> What I want to display when I am browsing through sub-categories of links is
> not only the name of that sub-category but it's category's parents, grand
> parents and so on like the below example.
>
> UK >> West Yorkshire >> Batley
>
> I see this on a lot of directory sites but none of my PHP books cover how
> this is done. Must be quite simple so can someone please point me in the
> right direction. Hope I've explained it well enough, haven't a clue what
> this process is called.
>
> Cheers
>
> Phil
This is an adjacency list model and the solution, as others have
pointed out, is to use a php loop - or, alternatively, switch to a
nested set model! However, if you know that the 'depth' of the tree is
never going to be greater than 4, say, then you can just join the
table to itself that number of times to guarantee you're at the top of
the tree. Here's an example...
SELECT *
FROM relations AS A
LEFT JOIN relations AS B ON B.parent = A.id
LEFT JOIN relations AS C ON C.parent = B.id
LEFT JOIN relations AS D ON D.parent = C.id
WHERE ISNULL( A.parent )
Notice in this example that the id at the very top of the tree has a
parent id of NULL and not 0.
Navigation:
[Reply to this message]
|