Avoiding repetitions of same field data

SQL concepts used: SELECT · ORDER BY

Current approach with PDO

Sort the result set by the grouping field, remember the previous value, and print the name only when it changes. This is a presentation rule, so all database rows are still available to the page.

<?php
$stmt = $pdo->query(
    'SELECT name, subject, mark FROM student_mark ORDER BY name, subject'
);

$previousName = null;
foreach ($stmt as $row) {
    $name = $row['name'] === $previousName ? '—' : $row['name'];

    echo '<tr>';
    echo '<td>' . htmlspecialchars($name, ENT_QUOTES, 'UTF-8') . '</td>';
    echo '<td>' . htmlspecialchars($row['subject'], ENT_QUOTES, 'UTF-8') . '</td>';
    echo '<td>' . (int) $row['mark'] . '</td>';
    echo '</tr>';

    $previousName = $row['name'];
}
?>

There can be a requirement where we have to limit display the value of a filed once only in a group while displaying all records. We can display the filed value if it changes only. This way we can display a more user friendly way to our visitors. A group by query will display the records in groups but the value of the field will be displayed in every record. Here we will try to avoid repetition of same field data

Let us take the example of a student table where we have stored all the marks of the students. While displaying we will show all the mark of a student in one group but we don't want the student name should be displayed ( or repeat ) along with every records of different subject. It should display once only. After finishing one student the next group will show another group with student name display only for the first record.

As you can see here we are only managing the display part through PHP but collecting the records are same only.

See the PHP database display tutorial for the basic record-fetching pattern

We will use one variable to store the name inside the while loop. In next looping cycle we will check whether the next name is same as the name stored in variable. If it is same then we will display some empty space or dashes, else (a new name has come) let us display that name. Here is the display part .

The grouped display uses the PDO result loop shown above. Compare each row's name with the previous row, print the name only when it changes, and keep the query ordered by name so each student's rows remain together.

Download the full script for displaying records
Query with Error message →

← Subscribe to our YouTube Channel here

plus2net.com