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

[Phil Latio]

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_descriptio n
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

[lorento]

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

I think it is not a good table design. You need to learn database
normalization first.

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

--

http://www.mastervb.net

http://www.theukmap.com

On Feb 2, 7:58 am, "Phil Latio" <phil.la...@f-in-stupid.co.ukwro te:

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_descriptio n
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

[asdf]

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


"lorento" <laurente1234@y ahoo.comwrote in message
news:1170384094 .157629.187360@ q2g2000cwa.goog legroups.com...

>I think it is not a good table design. You need to learn database
normalization first.

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

>
--

http://www.mastervb.net

http://www.theukmap.com

On Feb 2, 7:58 am, "Phil Latio" <phil.la...@f-in-stupid.co.ukwro te:

>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_nam e
>parent_categor y (FK) //references category_id in this table
>>
>Table: link
>link_id (PK)
>link_name
>link_descripti on
>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

>

It's often called 'breadcrumb navigation'.

Further to lorento's incredibly useful top post (:p)... I think what he's
trying to say that perhaps the relationship between parent and children in
your tree structure (which seems to be what you are trying to represent in
your table), would be best expressed in a separate table... so you'd have
two tables:

Category
-------------------------
category_id (PK)
category_name

Category_Relati ons
-------------------------
parent_category _id (FK)
child_category_ id (FK)

parent_category _id + child_category_ id in the second table together would
form a compound primary key for the table.

The result of this, of course, would be that each child *might* become a
child of multiple parents. This could be an advantage or a disadvantage,
depending on how you want the children to behave.

The other approach you could use, I guess, would be to identify the
following entities/tables (... yes, virginia, I know they're not the same
thing):

Country
Region
Town

If your structure requires that you have infinite tree 'nodes' however, this
approach will not work, and so your current structure works better. I guess
it depends on whether you are trying to construct an n-leafed tree, or
enforce strict locality rules... time for a specification?

It is incorrect IMO to say that your current structure is 'not a good table
design', since you may, indeed be trying to construct a 'tree' rather than a
strict entitiy model.

Horses for courses.

[Geoff Muldoon]

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

laurente1234@ya hoo.com says...

I think it is not a good table design. You need to learn database
normalization first.
http://dev.mysql.com/tech-resources/...alization.html

Internally self-referencing tables are actually *good* database design,
and satisfy normalization rules.

<aside>

Because it's difficult to follow context.

Why is top-posting bad?

</aside>

Here is the data in the category table
1, England, 0
2, West Yorkshire, 1
3, Batley, 2
4, Leeds, 2
5, Bradford, 2

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.

It's most commonly called "breadcrumb ing" (from the Hansel & Gretel fairy
tale).

The following pseudo-code should do the job, but is *not* optimised as I
nowadays use Oracle which supports the wonderful CONNECT BY construct to
recursively query self-referencing tables, so this is a simple
slegdehammer MySQL version.

My MySQL (and some of my PHP) is *very* rusty, but if there are any
errors, at least it will give you an idea of how it might be done:

Note also that it doesn't cater for bad data, eg. a link_id which won't
resolve back to an eventual parent with a category_id of 0.

<?php
function makeBreadcrumbs ($link_id) {
// set up a counter
$counter=1;
// get the name for this link_id
$query="SELECT link_name, category_id FROM link WHERE link_id=$link_i d";
$result=mysql_q uery($query);
$row=mysql_fetc h_array($result );
// put it as the first row in an array
$bread_crumb_ar ray=array();
$bread_crumb_ar ray[$count]=$row("link_nam e");
// get the parent
$category_id=$r ow("category_id ");
// now climb the tree if not already at the top
while($category _id!=0) { // careful of errors here !!!
// this time get the parent's name
$query="SELECT link_name, category_id FROM link WHERE link_id=
$category_id";
$result=mysql_q uery($query);
$row=mysql_fetc h_array($result );
// increment the counter
$counter++;
// put it in the array
$bread_crumb_ar ray[$counter]=$row("link_nam e");
// get the next parent
$category_id=$r ow("category_id ");
}
// now you have an array of names to the top in reverse number order
// get the last one first
$breadCrumbStri ng=$bread_crumb _array[$counter];
// work back through the array
while($counter> 0) {
$breadCrumbStri ng.=" >".$bread_crumb _array[$counter];
// decrement the counter
$counter--;
}
return $breadCrumbStri ng;
}
// test using the example
$link_id=3;
echo makeBreadcrumbs (link_id);
?>

Geoff M

[asdf]

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


"Geoff Muldoon" <geoff.muldoon@ trap.gmail.comw rote in message
news:MPG.202d73 c2bb4170f39898d 4@news.readfree news.net...

laurente1234@ya hoo.com says...

>I think it is not a good table design. You need to learn database
>normalizatio n first.
>http://dev.mysql.com/tech-resources/...alization.html

>
Internally self-referencing tables are actually *good* database design,
and satisfy normalization rules.
>
<aside>
>
Because it's difficult to follow context.
>
Why is top-posting bad?
>
</aside>
>

[snip - see OP]

I agree... nothing wrong with self-reference if you are trying to construct
a tree.

[Michael Fesser]

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

..oO(Phil Latio)

>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.

Unfortunately you can't do that with a single SQL statement (unless you
use a nested set structure, but that would be real overkill). You have
to walk up the tree in a loop and fetch every parent node until you
reach the root node.

Micha

[strawberry]

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

On 2 Feb, 00:58, "Phil Latio" <phil.la...@f-in-stupid.co.ukwro te:

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_descriptio n
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.

[Phil Latio]

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

Unfortunately you can't do that with a single SQL statement (unless you

use a nested set structure, but that would be real overkill). You have
to walk up the tree in a loop and fetch every parent node until you
reach the root node.

Thanks for your answer.

Cheers

Phil

[Phil Latio]

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

It's most commonly called "breadcrumb ing" (from the Hansel & Gretel fairy

tale).

Thanks. Popped that into Google and came up with whole load of people asking
the same question as me. Surprised I didn't find any tutorials on this
subject.

<?php
function makeBreadcrumbs ($link_id) {
// set up a counter
$counter=1;
// get the name for this link_id
$query="SELECT link_name, category_id FROM link WHERE link_id=$link_i d";
$result=mysql_q uery($query);
$row=mysql_fetc h_array($result );
// put it as the first row in an array
$bread_crumb_ar ray=array();
$bread_crumb_ar ray[$count]=$row("link_nam e");
// get the parent
$category_id=$r ow("category_id ");
// now climb the tree if not already at the top
while($category _id!=0) { // careful of errors here !!!
// this time get the parent's name
$query="SELECT link_name, category_id FROM link WHERE link_id=
$category_id";
$result=mysql_q uery($query);
$row=mysql_fetc h_array($result );
// increment the counter
$counter++;
// put it in the array
$bread_crumb_ar ray[$counter]=$row("link_nam e");
// get the next parent
$category_id=$r ow("category_id ");
}
// now you have an array of names to the top in reverse number order
// get the last one first
$breadCrumbStri ng=$bread_crumb _array[$counter];
// work back through the array
while($counter> 0) {
$breadCrumbStri ng.=" >".$bread_crumb _array[$counter];
// decrement the counter
$counter--;
}
return $breadCrumbStri ng;
}
// test using the example
$link_id=3;
echo makeBreadcrumbs (link_id);
?>

Thanks for this code. It will assist me greatly.

I will actually post a working solution when I have one so everyone can
share it.

Cheers

Phil

[Jerry Stuckle]

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

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_descriptio n
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@attgl obal.net
=============== ===

[strawberry]

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

Try asking in comp.databases. mysql.

Oh, the irony of it.

[Jerry Stuckle]

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

strawberry wrote:

>Try asking in comp.databases. mysql.

>
Oh, the irony of it.
>

And why is that? This can easily be done with recursive SQL. MySQL
doesn't support it yet, but there are ways to do it.

But it wouldn't be appropriate for a PHP newsgroup.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[strawberry]

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

On Feb 3, 3:41 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

strawberry wrote:

Try asking in comp.databases. mysql.

>

Oh, the irony of it.

>
And why is that? This can easily be done with recursive SQL. MySQL
doesn't support it yet, but there are ways to do it.
>
But it wouldn't be appropriate for a PHP newsgroup.
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attgl obal.net
=============== ===

?
The OP originally posted 'accidentally' to comp.databases. mysql before
quickly re-posting here -and hence, apparently 'multi-posting' - for
which he was soundly chastised.

[Jerry Stuckle]

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

strawberry wrote:

On Feb 3, 3:41 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

>strawberry wrote:

>>>Try asking in comp.databases. mysql.
>>Oh, the irony of it.

>And why is that? This can easily be done with recursive SQL. MySQL
>doesn't support it yet, but there are ways to do it.
>>
>But it wouldn't be appropriate for a PHP newsgroup.
>>
>--
>============== ====
>Remove the "x" from my email address
>Jerry Stuckle
>JDS Computer Training Corp.
>jstuck...@attg lobal.net
>============== ====

>
?
The OP originally posted 'accidentally' to comp.databases. mysql before
quickly re-posting here -and hence, apparently 'multi-posting' - for
which he was soundly chastised.
>

Ah, OK.

I can see from his original question he was asking how to do it in PHP.
But I think that was the wrong question. Just asking how to do it in
comp.databases. mysql could have given him an SQL answer.

But it's also why cross-posting is better than multi-posting.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Phil Latio]

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

Ah, OK.

>
I can see from his original question he was asking how to do it in PHP.
But I think that was the wrong question. Just asking how to do it in
comp.databases. mysql could have given him an SQL answer.
>
But it's also why cross-posting is better than multi-posting.

Sincere thanks for your input, Jerry. The person who chastised me for cross
posting actually posted again and apologised so that is sorted now.
Originally I was debating which group would be best but since I am learning
PHP, I thought comp.lang.php would be most appropriate. I wasn't trying to
cross post or multi-post, just hit this group which I failed miserably with
my first shot

I actually now have a working solution written in PHP. It's built upon what
Geoff Muldoon posted on this thread although it does have a minor bug (well
irritation more than bug) which currently has me puzzled.

Cheers

Phil