PHP MYSQL Add up number of entries in single table and display in HTML

I have this so far... can it be done?

[scankified]

I have a table called tasks which contains a number of tasks for projects (for example 6 tasks are related to one project). On 'view projects' page, you can see the projects, and I have one column which needs to display the number of tasks associated with the project (in that particular row).

So basically, I need a query or PHP calculation that can add up the number of tasks for the project and then display this number dynamically! So far I have this running correctly with:

SELECT (SELECT count(*) FROM tasks where tasks.project_id = projects.projectid) as num_tasks, projects.projectname, projects.projectid 
FROM projects

This displays the total number of tasks for a project in 'num_tasks' row, and then I have simply called this into the table row. HOWEVER, this displays all tasks totals for each project, and not only for the project in the row. Plus, each user is associated with a project (I have usersprojects table to split the many-to-many) so I want the task totals displayed only to be relevant to the current user viewing (of whom is associated with the projects obviously!).

Without the above query, I simply have the projects displaying that the user is associated with. However, I just need something that displays the task totals for each of THEIR projects! Thanks for any help!

[rocknbil]

edit: once again, missed a point . . .

this displays all tasks totals for each project, and not only for the project in the row.

Number of ways. First, you're not limiting it to the project creator, so this may suffice:

SELECT (SELECT count(*) FROM tasks where tasks.project_id = projects.projectid) as num_tasks, projects.projectname, projects.projectid
FROM projects where projects.user_id=".$logged_in_user . " and projectid=" . $current_proj_id;

Or, instead of logged in user, the user you're searching up.

Another way is

SELECT sum(tasks.active),sum(tasks.completed),sum(tasks.canceled) from tasks,projects where tasks.project_id = projects.projectid and projects.user_id=" . $logged_in_user . " and projectid=" . $current_proj_id . " group by projects.user_id";

The syntax may be incorrect and need tweaking, but you need to use group by to use sum. Note how it gives you a sum of various columns for multi-facet reporting.

IMPORTANT: Sum is just what it says it is, so the targeted columns are expected to be tinyint(1) fields (or similar) with an expected value of 1 or 0. This wouldn't work pointed at an auto increment field, obviously.

[scankified]

Thanks for this detailed reply... the bold code that you have added to my original query; I take it I need to declare $logged_in_user and $current_proj_id ? Sorry I am new to this stuff, but how do you I do this.

[scankified]

Sorry also forgot to say, there is no user_id in projects table, as it would be many to many relationship, so i have users_projects there instead to separate the relationship.