2

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.

Improve this question

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

0

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
Improve this answer

6 Comments

How would I then call up the value using PHP in the table row? Is it simply a case of something like this: <?php echo $row['activity_id']?>
Sorry I mean <?php echo $row['num_activties']?>? I did try inserting this as the calling in statement within my table, but I can't get anything to display.
Hi again, I have managed to get the total number of activities associated with each project to display correctly, thanks very much for this. However this query produces values for all projects in the database, and not those only assigned to the user if you get what I mean! I also have a users table with usersprojects table to form the one-to-many between users table and projects table. I'm not sure if this would come in handy for it. Would it be possible to insert a WHERE statement in the select query to pull in tasks associated with a project, which is also associated with the logged in user
Of course, you can add any WHERE clause you'd like. In the first example (using the JOIN ), you can extend the join say for example JOIN activities ON activities.project_id = project.project_id AND activities.userid = user.userid. In the second example you can do the same by extending the WHERE clause in a similar manner.
Ok, the only problem I think I have is I have a user_project table between users and projects. And each user has a level (by a number) which identifies their permission... And the user viewing the projects in this view is an admin with a particular level, they are not assigned tasks (only projects as they create them) I'm doubting this is possible now ha!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.