Remove duplicate results which got from mysql database with PHP

Question

I had been separated two table from the data which is students room's booking record. There are two tables, "Applicant" is stored students' ID and nickname, and "rmBooking" is stored the records of booking(time,date etc).

  -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
   ......  |   ......       |   ......    |  ......

I would like to show the results as one table in browser, just like:

    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
    ......   |   ......  |  ......   |   ......       |   ......    |  ......

When I tried to do this work and test int he browser, the table showed:

    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
    ......   |   ......  |  ......   |   ......       |   ......    |  ......

And my PHP code:

    <?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);

?> It shows the wrong results and the booking records are duplicating with the students. Shall I need to join the tables first in mysql server or coding incorrectly? Can anyone teach me how to join the tables in mysql, thanks a lot.

I think you need to recheck the schema of your tables, how is Applicant and rmBooking related? MySQL isn't able to guess which student is booking which room, you need to store that — Mauricio Trajano
– Mauricio Trajano, Commented Nov 11, 2014 at 3:42
Thanks Mauricio Trajano, that means I need to add a foreign key with these tables? — Chris.C
– Chris.C, Commented Nov 11, 2014 at 3:48
There is no more support for mysql_* functions, they are officially deprecated, no longer maintained and will be removed in the future. You should update your code with PDO or MySQLi to ensure the functionality of your project in the future. — Kermit
– Kermit, Commented Nov 11, 2014 at 4:05

tronmcp · Accepted Answer · 2014-11-11 04:06:20Z

So with two tables one for applicant and one rmBooking you need a foreign key in the rmBooking table that points to the applicant table. Then when you do the JOIN the relationship will be easy to identify, from that point all you need is a JOIN to get the correct data:

-Table Applicant

user_id  |   name

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

-Table 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

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

"user_id" in the rmBooking table is the foreign key that connects back to the applicant table. Each user in this case has a booking

The following JOIN will get the result you want:

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

Notice the use of an alias for the table names this makes reading the statement much easier and keeps like named fields from being ambiguous to the database; you can see exactly where each value is coming from what table.

I hope this helps!

Kevin Kopf · Accepted Answer · 2014-11-12 23:52:32Z

If you are willing to display the table the way you described, you should use JOIN. But, I see no relation between the two tables. Consider two example tables:

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 column represents the id of the product from the previous table. So, if we join them like this: SELECT * FROM Addicts JOIN Vendors ON Addicts.addicted_to = Vendors.id we will get the needed table:

+ 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      |
+----+------+-------------+----+-------------+-----------------+

Beware, as column id will be ambiguous and plan your database and table structure, keeping in mind the relations.

kyo · Accepted Answer · 2014-11-11 03:58:16Z

Since you haven't linked your Applicant and rmBooking together with a condition, mysql will basically show a Cartesian product of Applicant and rmBooking.

Probably you would like to store Applicant information in your rmBooking table like below:

 -Table 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     |    1
    101    |  04-10-2014    |     3       |     6     |    3
   ......  |   ......       |   ......    |  ......   |  ......

and then link them together with a join condition like this:

SELECT 
    a.user_id, a.name, r.booking_date, r.from_time, r.to_time, r.room
FROM
    Applicant a left join rmBooking r on a.user_id = r.user_id
ORDER BY
    a.user_id, r.booking_date;

PS: I checked your SQL and your table description, it turns out that they don't match. In your SQL there is BookingDate and RmBooking_Applicant but your tables are Applicant and rmBooking. So I designed based on your table description.

Collectives™ on Stack Overflow

Remove duplicate results which got from mysql database with PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related