为用户选择正确的数据

Question

i have two tables in my data base

U_ID of table tasks is a foreign key of id from table users

my code is like this

<?php
    require("common.php");
    if(empty($_SESSION['user']))
    {
        header("Location: login.php");
        die("Redirecting to login.php");
    }
    $query = "
        SELECT
            id,
            username,
            eID
        FROM users
        WHERE id <> '5'
    ";
    $query1 = "
        SELECT
            T_ID,
            task_tom,
            U_ID
        FROM tasks
        ORDER BY T_ID DESC LIMIT 1

    ";
    try
    {
        $stmt = $db->prepare($query);
        $stmt->execute();
    }
    catch(PDOException $ex)
    {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();

        try
    {
        $stmt1 = $db->prepare($query1);
        $stmt1->execute();
    }
    catch(PDOException $ex)
    {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows1 = $stmt1->fetchAll();
?>
<h1>Memberlist</h1>
<table border="1">
    <tr>
        <th>Username</th>
        <th>Employee ID</th>
        <th>Task</th>
    </tr>
    <?php foreach($rows as $row): foreach($rows1 as $row1): ?>
        <tr>
            <td><?php echo htmlentities($row['username'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo htmlentities($row['eID'], ENT_QUOTES, 'UTF-8'); ?></td>
            <td><?php echo $row1['task_tom']; ?></td>
           <?php endforeach; endforeach;?>
</table>
<br/>
<a href="edit_account.php">Edit Account</a><br /><a href="logout.php">Logout</a>  

my question is how could i populate the task column in the table with correspond to the right U_ID.
this code will provide me this answer

supposedly, SSIS(data mining) is just the task_tom assigned to bassil and lawrence should have a blank task because the user didnt input any task for lawrence just yet but it seems i have a flaw to my select statement or is it my for each wherein its printing it on the table to every user. so my question is how to solve this kind of problem?

Answer 1

SELECT
    U.id,
    U.username,
    U.eID, 
    t.task_tom

FROM 
    users u

  INNER JOIN 
   (
      SELECT u_id, max(t_id) as t_id
      FROM tasks
      GROUP BY u_id
   ) tmax
  ON tmax.U_ID = u.id

  INNER JOIN tasks t ON t.t_id = tmax.t_id

WHERE u.id <> '5'

This basically says "what is the latest task id for each user, now get me all info about that task".