Hi,
I was wondering if there was perhaps a better way to make the query below. I am currently using 2 queries, one is inside a while loop (which i would possibly like to get out of...)
Basically, what i am doing is getting a list of categories that a post belongs to. For example:
My New Car.
This post belongs to the following categories: Transportation, Things with wheels, Objects larger than my foot.
I have tables set up like this:
Categories: cid, name
Posts: pid, name, etc.
Lookup: pid, cid
When a post is inserted, whatever category was selected is inserted into the lookup table (this was following the book that Sitepoint sells btw). For instance, the lookup table for a post might look like this:
pid = 1, cid = 1
pid = 1, cid = 3
pid = 1, cid = 8
Showing that post no. 1 belongs to 3 categories. And below is how im pulling it for display, but is there a shorter way to accomplish this? I guess i should ask first if this is very resource intensive or is it uncommon to do something like this? There aren't going to be more than say 25 categories at any given moment...
$related_categories = mysql_query("SELECT * FROM lookup_table WHERE pid = '$prid'");
while ($mycategory = mysql_fetch_array($related_categories)) {
$cat_id = $mycategory['cid'];
$selected_categories = mysql_query("SELECT * FROM categories_table WHERE category_id = '$cat_id'");
$category = mysql_fetch_array($selected_categories);
$category_name = htmlspecialchars($category['category_name']);
echo "<a href=\"view_category.php?cat_id=".$cat_id."\">$category_name</a>";
}
