[Resolved] Left Joins and Groups

martian

Hey there,

I'm trying to create a PHP script that will display categories of items and list the items in each category below the proper category. For example:

Philosophy
The Republic
Symposium
Nicomachean Ethics

Fiction
The Sun Also Rises
Crime and Punishment
Siddhartha

I am storing the categories in one table in a database (identifying each with a unique ID number), and storing the items in another table (tagging each with a number that corresponds to the category.

So the tables look like this:

Categories
1 Philosophy
2 Fiction

Books
2 The Sun Also Rises
1 The Republic
1 Symposium
2 Crime and Punishment
2 Siddhartha
1 Nichomachean Ethics

I would like to query the two tables with a LEFT JOIN that might look like this:

SELECT Categories., Books. FROM Categories LEFT JOIN Books ON Categories.catID = Books.catID

And then I would like to output the results as shown above. I know it is possible to do this in ColdFusion, by nesting the CFOUTPUT tags, but don't know if it is possible in PHP.

Thanks!

Pig

$result = mysql_query("SELECT * FROM Books WHERE Books.catID=Categories.catID");
while( $row = mysql_fetch_array($result) ) echo($row[book_name] . "<br>");

something like that. I don't think you need to do a left join in this scenario.

martian

Pig,

I see how that could be done -- but the method you propose would require me to query the 'Books' table for each of the categories. I would prefer to use just one query (using LEFT JOIN), and then set up a system of nested loops (perhaps?). The outer loop would loop through the categories, and the inner loop would be through the books in each category.

Here is the pseudo-code for one possible inelegant solution:

for each category {
display category name;
loop through entire list of books {
if book is in category
display book title;
}
}

Does anyone know a more elegant solution?

Pig

You would want to do a nested query for that:

$result = mysql_query("SELECT categories_id, categories_name FROM categories");
while( $row = mysql_fetch_array($result) )
{
     echo("<b>$row[categories_name]</b><br");
     $sub_result = mysql_query("SELECT books_name FROM books WHERE books_catid=$row[categories_id]");
     while( $sub_row = mysql_fetch_array($sub_result) ) 
     {
          echo("$sub_row[books_name]<br>");
     }
}

martian

Thanks. I was hoping to do everything with one query, but it should work to use a new query for each category.