Q:

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 querying 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

}

A:

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 !!!

