php - 使用 PHP 删除从 mysql 数据库中获取的重复结果

Question

我从学生房间的预订记录数据中分离出两个表。有两个表，“Applicant”存储学生ID和昵称，“rmBooking”存储预订记录(时间、日期等)。

  -Table Applicant

    user_id  |   name
   ---------------------
       1     |   Benny
       2     |   Chars
       3     |   Harry
    ......   |   ......

  -Table rmBooking

    room   |  booking_date  |  from_time  |  to_time  
  ----------------------------------------------------
    101    |  22-09-2014    |     1       |     3
    101    |  28-09-2014    |     2       |     4
    101    |  02-10-2014    |     1       |     4
    101    |  04-10-2014    |     3       |     6
   ......  |   ......       |   ......    |  ......

我想在浏览器中将结果显示为一个表格，就像:

    user_id  |   name    |    room   |  booking_date  |  from_time  |  to_time
   -----------------------------------------------------------------------------
       1     |   Benny   |    101    |  22-09-2014    |     1       |     3
       2     |   Chars   |    101    |  28-09-2014    |     2       |     4
       3     |   Harry   |    101    |  02-10-2014    |     1       |     4
    ......   |   ......  |  ......   |   ......       |   ......    |  ......

当我尝试完成这项工作并在浏览器中进行测试时，表格显示:

    user_id  |   name    |    room   |  booking_date  |  from_time  |  to_time
   -----------------------------------------------------------------------------
       1     |   Benny   |    101    |  22-09-2014    |     1       |     3
       1     |   Benny   |    101    |  22-09-2014    |     1       |     3
       1     |   Benny   |    101    |  22-09-2014    |     1       |     3
    ......   |   ......  |  ......   |   ......       |   ......    |  ......
       2     |   Chars   |    101    |  22-09-2014    |     1       |     3
       2     |   Chars   |    101    |  22-09-2014    |     1       |     3
    ......   |   ......  |  ......   |   ......       |   ......    |  ......
       3     |   Harry   |    101    |  22-09-2014    |     1       |     3
       3     |   Harry   |    101    |  22-09-2014    |     1       |     3
    ......   |   ......  |  ......   |   ......       |   ......    |  ......
       1     |   Benny   |    101    |  28-09-2014    |     2       |     4
       1     |   Benny   |    101    |  28-09-2014    |     2       |     4
    ......   |   ......  |  ......   |   ......       |   ......    |  ......
       2     |   Chars   |    101    |  28-09-2014    |     2       |     4
       2     |   Chars   |    101    |  28-09-2014    |     2       |     4
    ......   |   ......  |  ......   |   ......       |   ......    |  ......

         ..............................................................

       1     |   Benny   |    101    |  02-10-2014    |     1       |     4
    ......   |   ......  |  ......   |   ......       |   ......    |  ......      

还有我的 PHP 代码:

    <?php
$dbConnection = mysql_connect("localhost", "aaaaa", "bbbbb");
if (!$dbConnection) {
    die("Could not connect database: " . mysql_error());
}
mysql_select_db("aaaaa");
$show_rmBooking = mysql_query("SELECT DISTINCT user_id, name, booking_date, from_time, to_time, room FROM rmBooking, Applicant
                                 GROUP BY user_id
                                 ORDER BY user_id, booking_date;
                                 ");

if(!$show_rmBooking){
    die("Cannot select Database: rmBooking or Applicant, Error:" . mysql_error());
}

    $rmTable = "<table border=1>";
    $rmTable .= "<tr>";
        $rmTable .= "<th colspan=6 align=center>Room's Enrollment report</th>";
    $rmTable .= "</tr>";
    $rmTable .= "<tr>";
        $rmTable .= "<td style='text-align:center'>ID</td>";
        $rmTable .= "<td style='text-align:center'>name</td>";
        $rmTable .= "<td style='text-align:center'>Room Number</td>";
        $rmTable .= "<td style='text-align:center'>Booking date</td>";
        $rmTable .= "<td style='text-align:center'>From</td>";
        $rmTable .= "<td style='text-align:center'>To</td>";
    $rmTable .= "</tr>";
    $rmTable .= "<tr>";
    if(mysql_num_rows($show_rmBooking) > 0){
        while ($rows_Applicant = mysql_fetch_array($show_rmBooking)){
            foreach($rows_Applicant as $key => $value){
                $rmTable .= "<tr>";
                    $rmTable .= "<td>" . $rows_Applicant['user_id'] . "</td>";
                    $rmTable .= "<td>" . $rows_Applicant['name'] . "</td>";
                    $rmTable .= "<td>" . $rows_Applicant['room'] . "</td>";
                    $rmTable .= "<td>" . $rows_Applicant['booking_date'] . "</td>";
                    $rmTable .= "<td>" . $rows_Applicant['from_time'] . "</td>";
                    $rmTable .= "<td>" . $rows_Applicant['to_time'] . "</td>";                                                  
                $rmTable .= "</tr>";
            }               
        }
    }   
    $rmTable .= "</tr>";
$rmTable .= "</table>";

echo $rmTable;
mysql_free_result($show_rmBooking);
mysql_close($dbConnection);

?>它显示错误的结果并且预订记录与学生重复。我需要先在 mysql 服务器中加入表还是编码不正确？谁能教我如何在 mysql 中连接表，非常感谢。

Answer 1

因此，对于两张表，一张用于申请人，一张用于 rmBooking，您需要在 rmBooking 表中使用指向申请人表的外键。然后，当您执行 JOIN 时，关系将很容易识别，从那时起，您所需要的只是一个 JOIN 来获取正确的数据:

-表申请人

user_id  |   name

---

   1     |   Benny
   2     |   Chars
   3     |   Harry
......   |   ...…

-表rmBooking

room   |  booking_date  |  from_time  |  to_time  | user_id

---

101    |  22-09-2014    |     1       |     3     |    1
101    |  28-09-2014    |     2       |     4     |    2
101    |  02-10-2014    |     1       |     4     |    3

...... | ...... | ...... | ……

rmBooking 表中的“user_id”是连接回申请人表的外键。这种情况下的每个用户都有一个预订

下面的JOIN会得到你想要的结果:

SELECT a.user_id, a.name, b.room, b.booking_date, b.from_time, b.to_time 
FROM rmBooking b JOIN applicant a ON a.user_id = b.user_id

注意表名的别名的使用，这使得语句的阅读更加容易，并且避免类似命名的字段对数据库产生歧义；您可以准确地看到每个值来自哪个表。

希望对您有所帮助!