MySQL Displaying 2 tables efficiently

[tomhumf]

Hi,

I have 2 tables

categories:
cat-id
name
order

resources:
cat-id
id
content
name
link

Each resource is in a category with associated cat-id. I want to display the category name as a title, with links to each resource in the category under it. I can display the resources how I want using GROUP BY cat-id, but struggling about how to get the category titles in there, without having a category name for each resource.

Would a some kind of JOIN be the most suitable method in this case?
Thanks

[salnajjar]

Try this:
SELECT categories.name, resources.name FROM categories LEFT OUTER JOIN resources ON (resources.cat-id = categories.cat-id)
ORDER BY categories.name, resources.name ASC;

Seri

[salnajjar]

I don't know if this will work, as I noticed you have two columns with the same name in two different tables:
categories.name & resources.name

But try this PHP code:

$query = mysql_query("SELECT categories.name, resources.name FROM categories LEFT OUTER JOIN resources ON (resources.cat-id = categories.cat-id) ORDER BY categories.name, resources.name ASC");
while($row = mysql_fetch_assoc($query)){
if (($catname == '') ¦¦ ($catname != $row['categories.name'])) {
$catname = $row['categories.name'];
echo $catname." -><br>";
}
echo "--".$row['resources.name']."<br>";
}