SQL Categories

[wemustdesign]

Hi,

 

I have two tables and I am wanting to output them all on a page but I would like to do this by sorting them by cat name so would look like:

 

Animals

  Tigers

  Dogs

Buildings

  Castles

 

This is how I have my tables set up:

 

tbl: subject

 

id              name                  cat

1              Tigers                  1

 

tbl: cat

 

id              name

1                Animals

 

any ideas are much appreciated

[fenway]

A two-table JOIN and an ORDER BY clause should do the trick.

[wemustdesign]

What I don't understand is the order by bit, how would I output 'Animals' cat and then all of the animals from a different table and then 'Buildings' cat and all the buildings from another table.

 

How would I create this kind of order?

[fenway]

That kind of order is up to you -- you can list two columns in the ORDER BY clause -- but perhaps scripting this in PHP will be more flexible.

[wemustdesign]

Hi,

 

Thanks for the reply, yes I am going to code this in PHP. I know you can order by 2 columns but how would this work is what I am wandering?

 

So if I 'ORDER BY name ' for the categories how do I then output all of the subjects under that category before the mysql outputs the next  category?

 

This is the part I cannot get my head round?

[fenway]

Well, before we move any further, I'm not sure why you have a table called "cat".

[wemustdesign]

I have a category table to categorise all of my subjects. This way they can output (when I get it working) in a category structure and will be used for other things as well.

 

Animals (Cat)

    Tiger (Subject)

    Birds

    Lion

Buildings (Cat)

    Castles (Subject)

    Church

     

[fenway]

Oh, 'cat' like 'category', not like the animal.  That's why abbreviations are evil.

 

So, back to your original question -- simply use a seen hash, and create a structure accordingly (push()-ing onto a keyed hash by each category for each animal), and then the output it is a breeze.

 

That way, you can drop the ORDER BY, too -- and save the DB some work.