Categorised Links (mysql)

[frizzle]

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)

[Siv Hansen]

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($sectionr ow = mysql_fetch_arr ay($sections)){
print("<li>".$s ectionrow['name']); // this is your category
if($subsections = mysql_query("se lect sectionid, name from section
where parentID={$sect ionrow['sectionid']}")){
// this is your url, where parentID here equals your cat_id
while($subsecti on= mysql_fetch_arr ay($subsections )){
print("\n<ul>\n <li>".$subsecti on['name']."</li>\n</ul>\n</li>\n");
}
}
}print("</ul>");

[frizzle]

Hmm thanks for the reply!
Your first example is what i have right now, and the result of the
second one
is what i want, only, if i get this right, this would mean i'll have to
run a
separate query for each category...
I'm kind of trying to avoid that ..
Is it possible?

Greetings Frizzle.

[David Wahler]

frizzle wrote:

Your first example is what i have right now, and the result of the
second one
is what i want, only, if i get this right, this would mean i'll have to
run a
separate query for each category...
I'm kind of trying to avoid that ..
Is it possible?

On Usenet, it's frowned upon to reply without quoting the previous
poster.

Running a new query for each category gets inefficient as soon as you
have more than a few categories. Why not just do this:

$result = mysql_query("SE LECT c.category, l.url " .
"FROM categories c, links l " .
"WHERE l.cat_id = c.id ORDER BY category;");
$previous_categ ory = null;

while ($row = mysql_fetch_arr ay($result)) {
if ($row['category'] != $previous_categ ory) {
echo "<h1>" . $row['category'] . "</h1>\n";
$previous_categ ory = $row['category'];
}

echo $row['url'] . "<br>";
}

This allows the database to retrieve the data all at once, without
reconnecting each time.

-- David

[frizzle]

David Wahler wrote:

frizzle wrote:

Your first example is what i have right now, and the result of the
second one
is what i want, only, if i get this right, this would mean i'll have to
run a
separate query for each category...
I'm kind of trying to avoid that ..
Is it possible?

On Usenet, it's frowned upon to reply without quoting the previous
poster.

Running a new query for each category gets inefficient as soon as you
have more than a few categories. Why not just do this:

$result = mysql_query("SE LECT c.category, l.url " .
"FROM categories c, links l " .
"WHERE l.cat_id = c.id ORDER BY category;");
$previous_categ ory = null;

while ($row = mysql_fetch_arr ay($result)) {
if ($row['category'] != $previous_categ ory) {
echo "<h1>" . $row['category'] . "</h1>\n";
$previous_categ ory = $row['category'];
}

echo $row['url'] . "<br>";
}

This allows the database to retrieve the data all at once, without
reconnecting each time.

-- David

First sorry for not quoting. I have only recently discovered the
quoting part
in Google Groups.

Second, thank you so much for your code. This is just great! I would
have
never ever thought of this! Thanks a lot!

Greetings Frizzle.