[thelist] Re: another mysql order problem

[Dunstan Orchard]

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