php - 如何从2个表中获取结果并且不显示重复项

Question

我有 2 张 table

USERS
------------
id   | user
1    | john
2    | George
3    | Andy

Text
--------------------------------
id | user |date        |text
1  |  1   |2012/10/2   | ABC
2  |  3   |2012/11/2   | ABCD
3  |  2   |2012/12/2   | ABCDE
4  |  2   |2012/1/2    | ABCDE

在 Text 表中，user 列从 Users 表获取 id。

我有以下查询:

   $sql= mysql_query ("SELECT
    user.id,
    users.user,
    CASE
        WHEN text.date = CURDATE() THEN '1'
        ELSE '0'
    END AS 'today',
    text.date,
    text.text
FROM  users user, text text
WHERE users.id = text.user;");

if (mysql_num_rows($sql)<=0)
echo  "NO ENTRIES FOUND";

while ($row = mysql_fetch_assoc($sql))
{

          $user =$row['user'];  
           $id  =$row['id'];  
             $date2  =$row['date'];  

            if ($row['today'] == 1) { echo"
 <div class='z'><a href='viewuser.php?id=$user'>$user</a></div><br>         
        "; } else { echo"
 <div class='zx'><a href='viewuser.php?id=$user'>$user</a></div><br>         
        "; }; 

}

我的问题是:

如何显示日期与今天日期相同的用户，第一个 div(zx)，如果不与第二个 div(z) 进行比较来自 2 个表，如果是用户的 2 倍，则显示一次。

Answer 1

<!-- language: lang-sql -->

SELECT
    u.id,
    u.user,
    CASE
        WHEN t.date = CURDATE() THEN '1'
        ELSE '0'
    END AS 'today',
    t.date,
    t.text
FROM  users u, text t
WHERE u.id = t.user;

现在要显示结果，只需说

if ($row['today'] == 0) { class="zx"; } else { class="z"; }

代码已缩写，如果您需要详细信息，请告诉我。

这是您使用上面的解决方案更新的代码:

$sql = mysql_query ("
SELECT
    u.id,
    u.user,
    CASE
        WHEN MAX(t.date) = CURDATE() THEN '1'
        ELSE '0'
    END AS 'today'
FROM  users u, text t
WHERE u.id = t.user
GROUP BY u.id, u.user");

if (mysql_num_rows($sql) <= 0)
    echo  "NO ENTRIES FOUND";

while ($row = mysql_fetch_assoc($sql))
{
    $user =$row['user'];
    $id =$row['id']; 


if ($row['today'] == 1) {
     echo "<div class='zx'><a href='viewuser.php?id=$id'>$user</a></div><br>";
} else {
     echo "<div class='z'><a href='viewuser.php?id=$id'>$user</a></div><br>";
}

}