PHP - skip display duplicate fields(not records) in MySQL query

Question

My code is just query a database and output to a HTML table:

<?php

  include("incl\dbcon.php");

  $sql = "SELECT * FROM attendanceRecord";

  $result = $db_con->query($sql);

  echo "<table>
  <tr>
  <th>Date</th>
  <th>Building Name</th>
  <th>Room Name</th>
  <th>Student</th>
  <th>Time</th>
  </tr>";

  while($row_result = $result->fetch_assoc()){
    echo "<tr>";
    echo "<td>" . $row_result['rDate'] . "</td>";
    echo "<td>" . $row_result['rBuildingName'] . "</td>";
    echo "<td>" . $row_result['rRoomName'] . "</td>";
    echo "<td>" . $row_result['rStudent'] . "</td>";
    echo "<td>" . $row_result['rTime'] . "</td>";
    echo "</tr>";
  }

  $db_con->close();

  echo "</table>";

?>

the result is like:

Date         Building Name    Room Name  Student    Time
2018-07-12   Building A       1A         Sam        08:32:33
2018-07-12   Building A       1A         David      08:54:21
2018-07-12   Building A       1A         Dragon     08:50:10
2018-07-12   Building A       1B         John       08:43:11
2018-07-12   Building A       1B         Coco       08:51:39
2018-07-12   Building B       3A         Mary       08:21:23
2018-07-12   Building B       3A         Martin     08:46:57
2018-07-12   Building B       4B         Ray        08:26:47

How can I don't show or empty the duplicate fields in a row and make the table looks like below?

Date         Building Name    Room Name  Student    Time
2018-07-12   Building A       1A         Sam        08:32:33
                                         David      08:54:21
                                         Dragon     08:50:10
                              1B         John       08:43:11
                                         Coco       08:51:39
             Building B       3A         Mary       08:21:23
                                         Martin     08:46:57
                              4B         Ray        08:26:47

Where should i approach? from sql query(don't know is there a function to empty or NULL duplicate fields while query) or php array(print NULL if duplicate then move the pointer?), please help, thanks in advance!

Why you are trying to do it on mysql side? It is a matter of data presentation and should be done in view logic of app. — DevilaN
– DevilaN, Commented Aug 6, 2018 at 12:12
Thanks for your comment, I am not insist doing it on mysql side, just trying to find a solution in an appropriate way. — Dlun
– Dlun, Commented Aug 6, 2018 at 12:17

ADyson · Accepted Answer · 2018-08-06 12:15:44Z

1

Assuming the rows always come out in the right order (you can use ORDER BY to ensure it!) then in the PHP, keep a variable which contains the value of the date from the previous row. If it matches the date on the current row, don't print that value in the td. If it doesn't, then print it, as it must be a new one. Same approach for the Building name and Room name, but within the context of the current date (and building):

$date = "";
$buildingName = ""; //declare these outside the loop so they persist between loops
$roomName = "";

while($row_result = $result->fetch_assoc()){
    echo "<tr>";
    echo "<td>" . ($row_result['rDate'] == $date ? "" : $row_result['rDate']) . "</td>";
    echo "<td>" .($row_result['rBuildingName'] == $buildingName $row_result['rDate'] == $date ? "" : $row_result['rBuildingName']) . "</td>";
    echo "<td>" . ($row_result['rRoomName'] == $roomName && $row_result['rBuildingName'] == $buildingName && $row_result['rDate'] == $date ? "" : $row_result['rRoomName']) . "</td>";
    echo "<td>" . $row_result['rStudent'] . "</td>";
    echo "<td>" . $row_result['rTime'] . "</td>";
    echo "</tr>";
    //update the references to the specific fields
    $date = $row_result['rDate'];
    $buildingName = $row_result['rRoomName'];
    $roomName = $row_result['rRoomName'];
}

P.S. I would never attempt to do this in SQL, since it's really specific to the presentation of the data in this particular context, rather than the nature of the data itself. It's also much simpler in PHP.

answered Aug 6, 2018 at 12:15

ADyson

62.9k16 gold badges88 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dlun Over a year ago

Thanks for the answer! will try it tonight and get back to you!

Dlun Over a year ago

Dude, your answer is exactly what i want, thank for the teaching!

manjeet · Accepted Answer · 2018-08-06 12:14:42Z

0

In PHP you can do something like this

  include("incl\dbcon.php");

  $sql = "SELECT * FROM attendanceRecord";

  $result = $db_con->query($sql);

  echo "<table>
  <tr>
  <th>Date</th>
  <th>Building Name</th>
  <th>Room Name</th>
  <th>Student</th>
  <th>Time</th>
  </tr>";

  $rDate = $rBuildingName = $rRoomName = $rStudent = $rTime = [];

  while($row_result = $result->fetch_assoc()){

    echo "<tr>";
    echo "<td>" . (!in_array(  $row_result['rDate'] , $rDate) ? $row_result['rDate'] : ''). "</td>";
    echo "<td>" . (!in_array(  $row_result['rBuildingName'] , $rBuildingName) ? $row_result['rBuildingName'] : '') . "</td>";
    echo "<td>" . (!in_array(  $row_result['rRoomName'] , $rRoomName) ? $row_result['rRoomName'] : '') . "</td>";
    echo "<td>" . (!in_array(  $row_result['rStudent'] , $rStudent) ? $row_result['rStudent'] : '') . "</td>";
    echo "<td>" . (!in_array(  $row_result['rTime'] , $rTime) ? $row_result['rTime'] : '') . "</td>";
    echo "</tr>";

    $rDate[] = $row_result['rDate'];
    $rBuildingName[] = $row_result['rBuildingName'];
    $rRoomName[] = $row_result['rRoomName'];
    $rStudent[] = $row_result['rStudent'];
    $rTime[] = $row_result['rTime'];


  }

  $db_con->close();

  echo "</table>";

answered Aug 6, 2018 at 12:14

manjeet

1,51710 silver badges15 bronze badges

3 Comments

B001ᛦ Over a year ago

Why should OP do something like this? A good answer will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO.

manjeet Over a year ago

@B001ᛦ i think it is self-explanatory, should it explain ternary operator here?

Dlun Over a year ago

Thanks for your answer!, will digest and try it tonight!

Collectives™ on Stack Overflow

PHP - skip display duplicate fields(not records) in MySQL query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related