Adding up row number and displaying total using COUNT (PHP MySQL)

Question

I'm attempting to run a query that adds up the total number of subjects in a class. A class has many subjects. There is a 'teachersclasses' table between teachers (the user table) and classes. The principles sounds pretty simple but I'm having some trouble in getting my page to display the number of subjects for each class (directly associated with the teacher)

This is what I have so far, trying to make use of the COUNT with a nested SELECT:

SELECT (SELECT count(*) FROM subjects WHERE subjects.classid = class.classid) AS total_subjects, class.classname, class.classid 

FROM class

Then I am calling up 'num_subjects' to present the total within a while loop:

            <?php echo $row['total_subjects']?>

From the above, I am receiving the total subjects for a class, but within the same table row (for one class) and my other while loop doesnt run anymore, which returns all of the classes associated with a teacher :( ... Bit of a mess now!

I know to return the classes for a particular teacher, I can do an additional WHERE clause on the session of 'teacherid' but I think my query is getting too complicated for me that errors are popping up everywhere. Anyone have a quick fix for this! Thanks very much

Answer 1

SELECT count(*) FROM subjects GROUP BY subjects.classid

Answer 2

You don't need the "subselect", you can just do a JOIN and count()

SELECT 
  class.*, 
  count(subjects.*) AS total_subjects 
FROM 
  class 
  LEFT JOIN subjects ON class.classid = subjects.classid
WHERE 
  class.teacherid = ?
GROUP BY
  class.classid

Answer 3

Your query is probably not optimal. It might be a good idea to rewrite it as a join:

SELECT
    total_subjects,
    class.classname,
    class.classid 
FROM class
JOIN (
    SELECT classid, count(*) AS total_subjects
    FROM subjects
    GROUP BY classid
) AS T1
ON T1.classid = class.classid

As for your problem, you don't need two loops. This is a single result set with three columns, as my rewrite clearly shows. You only need one loop to read all the results.