PHP: MYSQL COUNT query with linked tables

Question

I am looking for a cleaner way to do this. My code works, but I know it can be better. I have three tables: one with a list of Category Groups, One with a list of categories that are linked to category groups, and one with a list of news stories that are linked to the categories.

I need to loop through all of the names of the Category Groups, followed by the names of the categories that are in the category groups, with the number of news stories in each category listed as well.

I have three tables: CategoryGroups, Categories, News.

I have a set of queries. The first queries all the rows from the CategoryGroups table:

$result = mysql_query( '
SELECT cat_group_id, cat_group_name FROM CategoryGroups 
' );

The second query is inside the looped results of the first query and finds all the categories that have a news item and are linked to a specific category group:

<?php
while( $row = mysql_fetch_assoc( $result ) ){
    $id = $row['cat_group_id'];     
    $name = $row['cat_group_name'];

echo "<h3>$name</h3>";

    $sql =  mysql_query("
      SELECT  category_id, title FROM `Categories`  
      WHERE cat_group_id = $id 
      AND category_id IN 
      (SELECT news.category_id FROM news)
      "); 
    while( $row = mysql_fetch_assoc($sql) ) {
    $title = $row['title'];
    $catid = $row['category_id'];
    $numbers =  mysql_query("
       SELECT * FROM news 
       WHERE category_id =$catid"
       );
    $nums = mysql_num_rows($numbers);
    echo "$title ($nums)<br/>\n";
}
?>

I would like to limit this to one or two queries, with efficiency in mind. I know this can be done, however I have not been successful in my attempts.

thanks.

Answer 1

Why not JOIN the tables?

SELECT cat_group_name, title, count(newsid)
FROM CatagoryGroups
INNER JOIN Categories ON cat_group_id
INNER JOIN News ON category_id
GROUP BY cat_group_name, title

looks like it should be close, if table news has a newsid column (it's gotta have SOME primary key, right? well, count that;-). With the obvious indexes the JOINs should be quite fast, and your PHP code can do whatever output formatting you may need from that.

Answer 2

I suggest you need to get a book on SQL, such as "SQL Queries for Mere Mortals."

$sql = mysql_query("
        SELECT  cg.cat_group_name, c.title, COUNT(n.category_id) AS NumNews
        FROM `CategoryGroups` cg 
        JOIN `Categories` c USING (cat_group_id)
        JOIN `News` n USING (category_id)
        GROUP BY cg.cat_group_name, c.title");

Then loop over the result and output a new <h3> each time the cat_group_name is different from the previous row.