Posted by Siv Hansen on 11/08/94 11:31

frizzle wrote:
> Hi there,
>
> I've looked in many places for what i need, but i just
> can't seem to find it. I keep ending up in ad-managing
> systems, or link-trackers ...
>
> I have 1 mySQL table called 'categories':
> -> id (INT), category (VARCHAR)
>
> And 1 called 'links':
> -> id (INT), cat_id (INT), url (VARCHAR)
>
> I think this part speaks for itself.
> What i want is to order the categories alphabetically,
> and have them 'populated'. (example below)
>
> Categories without any links in them (yet) shouldn't show
> up at all. I Just cannot figure out the query.
>
> Any help out there?
>
> Greetings Frizzle.
>
>
> ********* E X A M P L E **********
>
> category: COOL SITES
> - http://www.icecold.com
> - http://www.freezing.com
> - http://www.frost.com
> - http://www.freezing.com
>
> category: HOT SITES
> - http://www.fire.com
> - http://www.sunbathing.com
>
> (hope that's clear)
>
how about select c.category, l.url from categories c, links l where
l.cat_id = c.id order by category;

The result you then get is
cool sites http://www.icecold.com
cool sites http://www.freezing.com
osv etc mm

If you want the category name once and the belonging urls you have to
have a nested query (in the php-code)
You first select the categoryname, and then if the category has url's
you choose them as well.
I've done this with sections and subsections like this:

while($sectionrow = mysql_fetch_array($sections)){
print("<li>".$sectionrow['name']); // this is your category
if($subsections = mysql_query("select sectionid, name from section
where parentID={$sectionrow['sectionid']}")){
// this is your url, where parentID here equals your cat_id
while($subsection= mysql_fetch_array($subsections)){
print("\n<ul>\n<li>".$subsection['name']."</li>\n</ul>\n</li>\n");
}
}
}print("</ul>");

[Back to original message]