Categorised Links (mysql)

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

category: HOT SITES

(hope that's clear)

Re: Categorised Links (mysql)

frizzle wrote:
Quoted text here. Click to load it
how about select c.category, l.url from categories c, links l where  
l.cat_id = order by category;

The result you then get is
cool sites
cool sites
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=")){
// this is your url, where parentID here equals your cat_id
    while($subsection= mysql_fetch_array($subsections)){          

Re: Categorised Links (mysql)

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.

Re: Categorised Links (mysql)

frizzle wrote:
Quoted text here. Click to load it

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

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("SELECT c.category, l.url " .
                      "FROM categories c, links l " .
                      "WHERE l.cat_id = ORDER BY category;");
$previous_category = null;

while ($row = mysql_fetch_array($result)) {
    if ($row['category'] != $previous_category) {
        echo "<h1>" . $row['category'] . "</h1>\n";
        $previous_category = $row['category'];

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

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

-- David

Re: Categorised Links (mysql)

David  Wahler wrote:
Quoted text here. Click to load it

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
never ever thought of this!  Thanks a lot!

Greetings Frizzle.

Site Timeline