|
|
Posted by Jerry Stuckle on 02/03/07 04:50
Phil Latio 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
>
>
>
Phil,
What you need is recursive SQL, which isn't supported by MySQL (yet,
anyway).
If you're running a recent version of MySQL you can do it with a stored
procedure. Try asking in comp.databases.mysql.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|