查询中的Sum值，并在按项目ID筛选的表中显示

Question

I have been working on a website for school but I ran into a problem. I have to sum values which are in a different table then what is being displayed on the front page.

Let me explain further:

Front page

In this screenshot, you see the headers which contains: Plan and Do

• Plan = The amount of time you plan on spending on the project in total.
• Do = The amount of time you have spend on the total project.

This is the simple query and code I use to populate the table:

<?php
$query = mysqli_query($conn, "SELECT * FROM `projecten`"); 
while ($row = mysqli_fetch_array($query)) {?>
    <tr class="active">
        <td style="display:none;" class="id"><?php echo $row['ID'];?></td>
        <td><?php echo $row['Naam'];?></td>
        <td><?php echo $row['Plan'];?></td>
        <td><?php echo $row['Do'];?></td>
        <td>
        <a class="btn btn-default btn-success" href="ExpandedProject.php?id=<?php echo $row['ID'];?>">Expand</a>
                <a class="btn btn-default btn-danger" href="Delete_Project.php?id=<?php echo $row['ID'];?>">Verwijderen</a>
        </td>
    </tr>
<?php  } ?>

Now what i tried doing:

SQL = SELECT *, sum(tasks.Plan) FROM `projecten`,`tasks`

This worked but because I have 1 table for all the tasks for different projects this will be adding tasks which are not part of the project. So i deleted this again.

I also tried using the above query and then adding a where which filtered the project id's in the tasks table.

My question is how do I make it so it will sum Plan and Do and display these values properly and being filtered by the project ID

Just wanted to let you know that I have 2 left hands when it comes to PHP so I'm sorry if there are some mistakes in the code :)

I also wanted to put in more pictures of the tables I have but I couldn't add them because of my reputation.

Thanks for the help!

Answer 1

You could do it like this:

SELECT    p.id, 
          p.naam, 
          coalesce(sum(t.plan),0) plan, 
          coalesce(sum(t.do),0) do 
FROM      projecten p 
LEFT JOIN tasks t 
       ON p.id = t.projectid 
GROUP BY  p.id, 
          p.naam

The LEFT JOIN is an outer join that will link records from the two tables, with the additional feature that if no corresponding tasks record is found for given projecten record, the projecten record will still be in the result, but with null values for what normally would be columns from the tasks record.

The group by clause will aggregate multiple tasks records, so that you still get only one record per projecten record. The sum expressions tell the SQL engine how to make this aggregation.

The coalesce function is included to make sure that a projecten record without any tasks record, will not show null for Plan and Do, but 0 instead. If you prefer the blanks in that case, then remove coalesce(..., 0), leaving just the sum(...).