I have sub-categories but want to display full category path

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

Oopps.. sorry wrong newsgroup.

Phil,

Not for the faint of heart, but you can find Celko's adjacency list model in
the link below. It might work for you.

http://www.intelligententerprise.com...equestid=51675

Good luck.

-- Bill

"Phil Latio" <phil.latio@f-in-stupid.co.uk> wrote in message
news:RZvwh.89252$5l2.73290@fe02.news.easynews.com. ..
> 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
>
>

On 2 Feb, 00:55, "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

Please DO NOT multi-post. Cross post if you must but DO NOT multi-post

See http://www.blakjak.demon.co.uk/mul_crss.htm

> Please DO NOT multi-post. Cross post if you must but DO NOT multi-post
>
> See http://www.blakjak.demon.co.uk/mul_crss.htm

If you wish to check my second post which was sent a minute after the first,
you would have read that the original message was posted in the wrong group.
However as a reference should I err again, I am seriously interested to know
what I am expected to do?

I eagerly await your reply.

Cheers

Phil

Phil Latio wrote:
>> Please DO NOT multi-post. Cross post if you must but DO NOT
>> multi-post See http://www.blakjak.demon.co.uk/mul_crss.htm
>
> If you wish to check my second post which was sent a minute after the
> first, you would have read that the original message was posted in
> the wrong group. However as a reference should I err again, I am
> seriously interested to know what I am expected to do?
>
> I eagerly await your reply.
>
> Cheers
>
> Phil

I did notice that, but too late :-(

However, bearing in mind the subject matter, this was actually the more on
topic group!

> I did notice that, but too late :-(

Not to worry. Just for anyone who might be interested, I posted a solution
in comp.lang.php.

Cheers

Phil