使用PHP删除从MySQL数据库获得的重复结果

Chris.C 发表于 Dev

克里斯·C

我已经从学生房间预订记录的数据中分离出两个表。有两个表，“申请人”存储学生的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中加入表，非常感谢。

亚历克斯·卡辛

如果您愿意按照描述的方式显示表格，则应使用JOIN。但是，我看不到两个表之间的关系。考虑两个示例表：

Table Vendors
+ id + vendor_name + product_related +
|----|-------------|-----------------|
| 1  | Coke        | Coca Cola       |
| 2  | Pepsi       | Pepsi Diet      |
+----+-------------+-----------------+

Table Addicts
+ id + name + addicted_to +
|----|------|-------------|
| 1  | Mike | 1           |
| 2  | Sarah| 1           |
| 3  | Jake | 2           |
+----+------+-------------+

addicted_to列代表上表中产品的ID。因此，如果我们像这样加入他们：SELECT * FROM Addicts JOIN Vendors ON Addicts.addicted_to = Vendors.id我们将获得所需的表：

+ id + name + addicted_to + id + vendor_name + product_related +
|----|------|-------------|----|-------------|-----------------|
| 1  | Mike | 1           | 1  | Coke        | Coca Cola       |
| 2  | Sarah| 1           | 1  | Coke        | Coca Cola       |
| 3  | Jake | 2           | 2  | Pepsi       | Pepsi Diet      |
+----+------+-------------+----+-------------+-----------------+

当心，因为列id将是模棱两可的，并要计划好数据库和表的结构，同时要牢记关系。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。