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

Question

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

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! I know exactly what I need, just do not know how to implement it.

Answer 1

Select sum(tasks) as totalTasks from activities where project_id=<id> group by project_id;

Something along those lines maybe?

Or if tasks is a foreign key and not a number of tasks then:

Select count(tasks) as totalTasks from activities where project_id=<id> group by project_id;

With out knowing more about your program and table structure it's hard to suggest a good way of doing this in more detail.

Answer 2

What you want is to use COUNT(), or possibly SUM(), and GROUP_BY() in your MySQL query. Here's the Documentation.

Answer 3

You could do this using a GROUP BY as such:

SELECT sum(activities.activity_id) as num_activities, project.project_name, project.project_id 
FROM project LEFT OUTER JOIN activities ON activities.project_id = project.project_id
GROUP BY project.project_id

Or using a nested select statement

SELECT (SELECT count(*) FROM activities where activities.project_id = project.project_id) as num_activities , project.project_name, project.project_id 
FROM project