Hello,
I have a query to creates breadcrumb, on a site where each article might belong to multiple categories.

So for each level in the breadcrumb, I must join 2 tables: one holding the breadcrumb's description (what the user sees) and the other to find the relation between each item and its parent.

So the question is:
1. should I build the breadcrumb into on big query, where I'd have 10 joins to get the titles of a 5 level breadcrumb?, or
2. should I fire 5 separate queries, joining 2 tables on each and getting 1 title each query?

Is there any information I'm missing as to get proper advice?
Thanks for any help on this!

-Manuel

I don't know what your data-structure is, and generally you like to structure hierarchies so that you don't have to run a query for each level of that hierarchy. (Usually this is done by having a pointer to the root-node in each record.)

But nonetheless, you certainly can have multiple queries to solve a problem and, as long as there aren't too many of them, it usually works just fine.

In fact, given the choice between "a bunch of joins" and "a bunch of queries," I would definitely lean toward multiple queries. Temporary tables can be a really great thing. The more complex a query becomes, the less control you have over "what the heck the computer's gonna do."

Hello, thanks for answering my post.

For the parent/child relation, I have:

CREATE TABLE `itemRel` (
`idItem` int(4) unsigned default '0',
`idParent` int(4) unsigned default '0',
) TYPE=MyISAM;

Then there's another table holding the string (titleBcrumb) the users sees
CREATE TABLE `category` (
`idItem` int(4) unsigned default '0',
[snip]
`titleBcrumb` varchar(100) default '',
PRIMARY KEY (`idItem`)
) TYPE=MyISAM;


I'm not getting what you mean by "having a pointer to the root-node in each record". Is there any way I can extend my first table (itemRel) to avoid the multiple joins or queries?

For example, to get a 3 level breadcrumb I currently use:
SELECT cat1.titleBcrumb, cat2.titleBcrumb, cat3.titleBcrumb
FROM category AS cat1
INNER JOIN category as cat2 ON cat1.idItem <> cat2.idItem
INNER JOIN category as cat3 ON cat2.idItem <> cat3.idItem
INNER JOIN itemRel as itemRel1 ON cat1.idItem = itemRel1.idParent AND cat2.idItem = itemRel1.idItem
INNER JOIN itemRel as itemRel2 ON cat2.idItem = itemRel2.idParent AND cat3.idItem = itemRel2.idItem
WHERE cat1.titleURL = 'home' AND cat2.titleURL = 'section' AND cat3.titleURL = 'subsection'

Thanks again!!

Or, what you could do is add a path variable to category table. For example, say you have a category called "Shoes", the path to that category could be "/Apparel/Accessories/Shoes/".

Then, you select the Path column for the selected directory and use your cgi program to split the path into Shoes => /Apparel/Accessories/Shoes/, Accessories => /Apparel/Accessories/, and Apparel => /Apparel/ to create your bread crumb links.

I forgot to mention if you use the above, then your category management code has to remember that when you change the name of a category that it has to recursively go through all of the child categories to update their path information accordingly.

Recently implemented crumbs in Perl, flatfile but you could do it via db queries. Would be a few fast ones.
http://cvs.livejournal.org/browse.cg...-cvsweb-markup

Basically your table would look like

id
name
url
parent