SQL Query

Sketch · 12-11-2006, 03:44 AM

Hello,

I have a table that is setup as;

cat_id int
cat_link int
cat_text text

Pretty simple stuff, the cat_id is unique and the cat link isn't.

Say I have 5 values in the table such as

1, 0, Title
2, 0, Title 2
3, 1, Sub Title
4, 2, Sub Title3
5, 2, Sub Title4

I want a query which will bring these values out in the following order

Title
- Sub Title
Title 2
- Sub Title3
- Sub Title4

So as you can see I want to pull out the main headings (cat_link of 0) with the children directly under them (the cat_link is equal to the cat_id of the parent)...

I hope this makes sense, if anyone can help me out that would be must appreciated.

Thanks

Taltos · 12-11-2006, 09:10 AM

First question is what SQL are you using? MySQL or something else?

You also may want to consider, if it is possible, a little different design for that table. For example...how are you going to order the titles? If you are going to pull them out in the order of the cat_id column, what happens later when you want to insert a new set of titles/subtitles in the middle somewhere? There doesn't appear to be a good way to rearrange the order as you edit and add titles.

Sketch · 12-11-2006, 01:22 PM

I'm using mySQL.

I haven't really built the site yet so I can rebuild the tables into a different structure. I was thinking of pulling them out in alphabetical order, as in att the main titles in order, then each set of sub titles are in order as well... so

a
-a
-b
-c
b
-a
-b
-c
c
-a
-b
-c

ect.

Taltos · 12-11-2006, 02:54 PM

Off the top of my head, I would add 2 columns to your table:

cat_level - Used to designate what level the title is at in the "outline". 0 = top level, 1 = sub-level 1, etc.

cat_order - used to establish the order of titles

Then you can pull out all the top level titles into an array with a simple query and do the same for each deeper sublevel as you need them. You may not even need help with the query then but as an example:

$maintitlequery = 'select * from Headings where cat_level = 0';
$toplevelresult = mysql_query($maintitlequery);

loop through the results and for each main heading, check for sub-headings:
$level1subheadingquery = 'select * from Headings where cat_level = 1';
$level1result = mysql_query($level1subheadingquery);

This way you are just creating nested loops in PHP?? or whatever to pull headings as you need them.

Does this make sense?

If you really need to pull them all out at once, you can use cursors if you are on MySQL 5.x but I don't believe 4.x supports cursors.

By the way, been working with MSSQL mostly for the last year so double check any MySQL syntax you get from me

littleFella · 12-11-2006, 06:17 PM

Without stored procedures I can't see how you could do what you want to do in mySQL.

I'd go for the following:

Create 2 tables:

tbl_titles
cat_id int
cat_link int
cat_text text

tbl_subtitles
cat_sub_id int
cat_id int
cat_subtitle_text text

Red color code indicates foreign key in tbl_subtitles.
The SQL statement will be trivial:

select * from tbl_titles t, tbl_subtitles s
where t.cat_titles =s.cat_titles
[order.... etc]

that will yield these sample results:

Title, Sub Title
Title 2, Sub Title3
Title 2, Sub Title4

You will need to format the desired level display in your CGI code (php, perl or whatever elese you may be using).

Sketch · 12-11-2006, 06:43 PM

Thanks guys for your help. I think I'll go with littleFella's solution as I wanted to try and keep it in the one mysql query. I am very new to sql and I assumed I could do it all within the one table, but thats ok it won't take me long to change the table structure.

Thank you both.

Taltos · 12-12-2006, 08:43 AM

np, glad to help. Are you only going to have one sub-level to titles or could it go deeper? Even way down the road? If it can, you still need a level field or some way to know how deep in the hierarchy you subtitle is. Plan for growth as it is much easier now then to find out you have to completely re-architect your whole core later.

Good Luck and have fun!