Query the MySQL table once to get all listings for all categories

**categories** mysql table
id|category_name

**listings** mysql table
id|listing_title|category_id

I want to list all categories on 1 single/same PHP page, with all the listings shown below each category. How can I do this by query开发者_JAVA技巧ing the listings table ONCE. I can do it by making a separate query from within the categories loop, but that would be more than necessary I'm guessing.

//query listings table once
$sql = mysql_query("SELECT * FROM listings", $conn);

//loop through all categories
$sql = mysql_query("SELECT * FROM categories ORDER BY catname", $conn);
while($row= mysql_fetch_array( $sql, MYSQL_ASSOC )){

   //print category name
   print"$row[catname]";

   //print listings of category

}

Here is simple one (Category Name,Listings)

$sql_code = "SELECT C.category_name cat_name,L.listing_title listings FROM categories C,listings L WHERE C.id = L.category_id ORDER BY C.category_name";

$next_catname="";

while($row= mysql_fetch_array( $sql, MYSQL_ASSOC )){
if ( strcmp($next_catname,$row[catname]) < 0 )
{
if ( strlen($next_catname) > 0 )
{ print "<\BR>": }
print "Category : " . $row[catname] . "<\BR>";
} print"$row[catname] : $row[listings]<\BR>"; }

Here is a freaky one (Category Name Along with Comma-Separated List of Listings)

$sql_code = "SELECT C.category_name cat_name,GROUP_CONCAT(L.listing_title) listings FROM categories C,listings L WHERE C.id = L.category_id GROUP BY C.category_name";

$sql = mysql_query($sql_code, $conn);

while($row= mysql_fetch_array( $sql, MYSQL_ASSOC )){
print"Category: $row[catname]<\BR>Listings: $row[listings]<\BR><\BR>";
}

or you could explode $row[listing] by ',' and print them one by one

Have fun with this one !!!

have you thought about

select * from categories, listings 
 where categories.id = listings.category_id 
 order by categories.name, categories.id, listings.listing_title, listings.id

then having the PHP code loop through the list keeping track of whether the current category matches the previous category?