SQL/PHP question (Grouping results)

[quadrant6]

First off, I have 2 simple tables:

region
region_id
region_name

item
item_id
region_id
item_name

I wish to list on my page all regions with items belonging to each region displayed underneath. To complicate, If a region has no items then it should still be listed.

I have pissed around and can't get it to work if a region contains no items. Can anyone offer some insight on the PHP/SQL needed to achieve this?

[raf]

you need to use an outer-join so that all regions are included in the recordset. Like

SELECT region.region_name, item.item_name FROM region LEFT JOIN item ON region.region_id=item.region_id ORDER BY region.region_id

the order by clause is to allow you to display the items like you want. You just compaire the region_name to the one of the previous record (stored in a variable) and only display it if it's different from the previous record.

if you have a mysql version of 4.1 or up, then you can use a group_concat().