[thelist] Re: another mysql order problem
Dunstan Orchard
dunstan at 1976design.com
Mon Feb 3 05:53:01 CST 2003
> From: "rudy" <r937 at interlog.com>
>
> > Can anyone suggest what I can do?
>
> 1. never do a SELECT inside a loop -- the same results can usually be done
> with a join
really?
damn, I always do that... it seems like JOIN is about the most useful thing to
learn. I can't get my head round it. I think I have it, but then my examples
hardly ever work. Ah well...
Oh, well, sometimes I think I have to do a SELECT inside a loop because I have
to assign variables at each stage. But ok, I get your point :o)
*goes off to try to find some good articles on JOIN*
> 2. decide whether a given subcategory can belong to more than one parent
>
> if the answer to 2 is yes, then you need two tables, otherwise you
> only need one
Oh. Well, no a subcategory doesn't belong to more than one parent as such, but
it kinda does because there can be several depths of categories, eg.:
courses
courses > free courses
courses > free courses > online courses
etc...
So while the parent of 'online courses' is actually 'free courses' its
grandparent 'courses' is also listed as a parent. Eg.:
category parent
---------------+------------
courses -
free courses courses
online courses free courses
online courses courses
I don't know to what depth this structure could go. I use the 'level' value in
category to work out in which order these families are ordered.
> once you've got that straightened out, could you please give some
> sample rows
>
> your column names are rather confusing -- parentid, categoryparentid,
> categoryid...
sure thing, sorry about that:
category table
----------------------------------------------
categoryid category level
------------+--------------------------+------
1 Web Standards 1
2 Usability 1
3 User Statistics 1
4 Return on Investment 1
5 Courses 1
6 Accessibility 1
7 Free Courses 2
11 Legislation docos 3
10 Accessibility Legislation 2
categoryparent table
-----------------------------------------
categoryparentid categoryid parentid
------------------+-----------+----------
1 7 5
6 11 10
7 11 6
5 10 6
If we were looking at a list of the categories and their family structure we'd
see this:
Accessibility
Accessibility Legislation
Accessibility > Accessibility Legislation > Legislation docos
Courses
Courses > Free Courses
Return on Investment
Usability
User Statistics
Does that help at all?
Thankyou very much,
- Dunstan
---------------------------
Dorset, England
http://www.1976design.com/
http://www.orchard.it/
http://www.maccaws.org/
More information about the thelist
mailing list