473,698 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Feb 2 '07 #1
24 4670
I think it is not a good table design. You need to learn database
normalization first.
http://dev.mysql.com/tech-resources/...alization.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

Feb 2 '07 #2

"lorento" <la**********@y ahoo.comwrote in message
news:11******** **************@ 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/...alization.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.
Feb 2 '07 #3
la**********@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
Feb 2 '07 #4

"Geoff Muldoon" <ge***********@ trap.gmail.comw rote in message
news:MP******** *************** *@news.readfree news.net...
la**********@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.

Feb 2 '07 #5
..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
Feb 2 '07 #6
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.

Feb 2 '07 #7
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
Feb 2 '07 #8
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
Feb 2 '07 #9
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.
js*******@attgl obal.net
=============== ===
Feb 3 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
4058
by: John Dewbert | last post by:
*** post for FREE via your newsreader at post.newsfeed.com *** Hello, I have trouble passing a folder object (from a FileSystemObject) to a sub procedure. Consider the following code: =================================
2
2222
by: tshad | last post by:
I have an example I copied from "programming asp.net" (o'reilly) and can't seem to get the Sub (writefile) to execute. It displays all the response.write lines that are called directly, but not any of the response.write lines from inside the sub. ******************************************* <%@ Application Language="VB" %> <script runat="server">
6
6546
by: lenny | last post by:
Hi, I've been trying to use a Sub or Function in VBA to connect to a database, make a query and return the recordset that results from the query. The connection to the database and the query works fine, but passing the resulting recordset back to the sub's caller is not working out.
3
2463
by: Kathy Burke | last post by:
Hi, I'm tired, so this question may be silly. I have a fairly long sub procedure. Based on one condition, I load another sub with the following: If Session("GRN") = "complete" Then txtScan.Text = Session("SN") txtScan_TextChanged(sender, e) Session("GRN") = "" Exit Sub End If
10
21313
by: tmaster | last post by:
When I try to dynamically add a second sub menu item to this ContextMenu item, I get an error 'Specified argument was out of the range of valid values'. Private Sub mnuTopics_Show_Select(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles mnuTopics_Show.Select Dim mShowMenuItem As MenuItem mShowMenuItem = DirectCast(sender, MenuItem)
1
3395
by: dBNovice | last post by:
Please help! I have 3 forms: Task, Subtask, Elements. Elements is a subform of Subtask and Subtask is a subform of Task. I am able to navigate from Task to Subform to Element and from Element to Subtask to Task by pressing a button that makes each subform invisible/visible. The problem exists when I try to navigate from Element to Task to Subtask. Sub-subform Element will become invisible and show Task but when I press the button to go...
7
2651
by: ILCSP | last post by:
Hi, I'm using VB.Net (2003) and I have a question. Does anyone knows how to call a procedure using a variable? The variable will be equal to the name of the procedure. for example, if I have the following variable: Public PrintTheSub as string and the following subs:
20
4803
by: vitorjol | last post by:
Hello. I have 2 forms (Form1 and Form2). When i call the Sub placed in form1 from form2, i get the error "object reference not set to an instance of an object" ! What can i do to solve the problem? Thanks
6
1698
by: Bob | last post by:
Hi, I found this code here below (about cartitems and shoppingcart) and I have two questions about sub New(). In the first class CartItem, there is two times sub New(): Public Sub New() End Sub and
6
4868
by: Greg Strong | last post by:
Hello All, Is is possible to use an ADO recordset to populate an unbound continuous Subform? I've done some Googling without much luck, so this maybe impossible, but let me try to explain why. I've been exploring using Access as a front end to both SQL MSDE and Oracle XE. I'm in the process of writing a class to handle the basics of the ADO connection and recordsets. The basic relationships are as follows:
0
8668
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8598
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9152
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8885
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7708
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6515
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5857
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
2320
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1995
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.