An Efficient Category List

@lightnbDec 13.2007

I’m generating a list of categories, each with one or more subcategories (topics). There are currently two tables- one of categories, and one of subcategories (which has a key to the categories table).

The code I have (posted below) technically works, but it runs a query for every category that exists, which seems inefficient.

Does anybody know a more effective way to get the same results, perhaps with only a single query, or different database structure?

Thanks in advance,

Nick

[code=php] function BuildTopicList() { $Q = “SELECT CategoryName, CategoryID FROM Categories C WHERE 1 = 1;”;

$R = SafeQuery($Q);

while($W = mysql_fetch_array($R)) { $HTML .= SmallColumnCodeIndent . “<span class=”Category”>” . $W[‘CategoryName’] . “</span>”;

$Q1 = “SELECT TopicName, TopicID, CategoryID FROM Topics T WHERE T.CategoryID = “” . $W[‘CategoryID’] . “”;”;

$R1 = SafeQuery($Q1);

while($W1 = mysql_fetch_array($R1)) { // It’s a topic $HTML .= SmallColumnCodeIndent . “<a class=”” . TopicAClass . “” href=”#” . $W1[‘TopicID’] . “”>” . $W1[‘TopicName’] . “</a>nr”;

} }

return $HTML; } [/code]

to post a comment

PHP

5 Comments(s) _↴

@callumdDec 13.2007 — #It's difficult to be 100% sure of this because I can't see your table structures. But if all you want to do is select each category and display it with its subcategories, the query would probably look like this:

$query = "SELECT CategoryName, TopicName from Categories, Topics WHERE Topics.CategoryID = Categories.CategoryID";

@SyCoDec 14.2007 — #What callumd has done in his example is called a join. They take a bit of understanding but are essential if you want to move to the next level in SQL based web dev

Have a look at this page it has a nice intro to the different types of joins and the results you can expect when you join different ways. The most useful IMO being INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN.

http://en.wikipedia.org/wiki/Join_(SQL)

They'll bake your noodle but they're really cool.

@lightnbauthorDec 15.2007 — #Thanks guys:

It's difficult to be 100% sure of this because I can't see your table structures. But if all you want to do is select each category and display it with its subcategories, the query would probably look like this...[/QUOTE]

Basically, the table structure is flexible at this point, so if there's a more efficient database design, I'm open to suggestions. Currently I have two tables, one for categories and one for subcategories (which I'm calling "topics"), but the idea of the function is to create a list of all categories, with their sub categories, in order.

The program also needs to know which items are categories, and which are sub-categories, since sub-categories become links, while their parents do not.

I have revised the function to use a single query with a loop, and nested foreach loops, but I don't know if this is any faster / more efficient?

Revised version:

[code=php]
 function BuildTopicList()
 { 
 $Q = "SELECT C.CategoryName, C.CategoryID, T.TopicName, T.TopicID
 FROM Topics T 
 LEFT JOIN Categories C 
 ON C.CategoryID = T.CategoryID
 ORDER BY C.CategoryOrder, T.TopicName;";
 
   $R = SafeQuery($Q);
 
 
   if($R)
   {
   while($W = mysql_fetch_array($R))
   {
   $Topics[ $W['CategoryID'] ]['Name'] = $W['CategoryName'];
 
   $Topics[ $W['CategoryID'] ]['Topics'][ $W['TopicID'] ] = $W['TopicName']; 
   }
 
   foreach($Topics as $CatKey => $T)
   {
   $HTML .= SmallColumnCodeIndent . 
   "<span class="" . TopicListCategory . "">" . 
   $T['Name'] . 
   "</span>";
 
   foreach($T['Topics'] as $Key => $F )
   {
   $HTML .= SmallColumnCodeIndent . 
   "<a class="" . TopicAClass . "" 
   onclick='UpdateDisplay("displaylist", 
   "$Key", "1"); return false' href="" . 
   $_SERVER['PHP_SELF'] . 
   "?action=displaylist&id=" . $Key . "&page=1">$F</a>nr";
   }
   }
   }
 
   return $HTML;
 }
 [/code]

@sutabiDec 15.2007 — #I've wrote a 2 part blog on category listings, check it out:

http://my.opera.com/sutabi/blog/mysql-join-introduction

http://my.opera.com/sutabi/blog/mysql-join-introduction-part-2

@lightnbauthorDec 15.2007 — #I've wrote a 2 part blog on category listings, check it out:

http://my.opera.com/sutabi/blog/mysql-join-introduction

http://my.opera.com/sutabi/blog/mysql-join-introduction-part-2[/QUOTE]

Thanks for the links. ?

I read through the blogs, but am not sure how to apply that to multiple categories with multiple sub items, as the example assumes you only want results from one category.

Also in #PHP _↴

Multipule Table Insert What does this do?adding a sql retreived value to a 'href'

Success!

Help @lightnb spread the word by sharing this article on Twitter...

Tweet This

An Efficient Category List

5 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

An Efficient Category List

5 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

5 Comments(s) _↴

Also in #PHP _↴