PHP MYSQL Add up number of entries in single table and display in HTML
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 proje开发者_开发知识库cts, 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.
What you want is to use COUNT(), or possibly SUM(), and GROUP_BY() in your MySQL query. Here's the Documentation.
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.
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
精彩评论