Page 1 of 1

Remove duplicate records

Posted: Wed Apr 22, 2009 2:58 pm
by gj0519
I have a script that pulls in a date, time, id, name, and ref name from my database. When I display my data it gives duplicate date,time, and ref, the other records are different. I was a thinking of doing a count and if it found 2 or more records the same then only display it once. Here is what I have so far which works, but not sure where to add in a counter. Any ideas?

Thanks,

GJ

Code: Select all

$message="";
while($list=mysqli_fetch_array($result)) {
   $date=$list['gm_date'];
   $time=$list['gm_time'];
   $gid=$list['gameid'];
   $fldname=$list['fld_name'];
   $tname=$list['team_name'];
   $rlname=$list['ref_lname'];
    $message.="<tr>
        <td>$date</td>
        <td>$time</td>
        <td>$gid</td>
        <td>$fldname</td>
        <td>$tname</td>
        <td>$rlname</td>
         </tr>";

Re: Remove duplicate records

Posted: Wed Apr 22, 2009 6:57 pm
by califdon
Be careful, those are not duplicate records. And you do not want to remove anything. I think what you probably mean is that you have a GROUP BY query and you want to display a Group Header of the data so that it is displayed once, at the beginning of a group of records, rather than repeated for every record.

Assuming that that's what you mean, it's done by storing the value of the Group By field(s) in a variable, then as you cycle through the records, test each one to see if its group by field(s) is/are different from the previously stored value. Only display that information when it is different from the previous (and store it in the variable, so you can test the next record). It should look something like this:

Code: Select all

$lasthdr="";
while($list=mysqli_fetch_assoc($result)) {
    extract($list);
    $hdr=$gm_date . $gm_time . $ref_lname;
    if($hdr != $lasthdr) {
        echo "<tr><td>$gm_date</td><td>$gm_time</td><td>$ref_lname</td>";
        $lasthdr=$hdr;
    } else {
        echo "<tr><td colspan='3'>";
    )
    echo "<td>$gameid</td><td>$fld_name</td><td>$team_name</td></tr>";
}
At least, that's the way I'd do it.

Re: Remove duplicate records

Posted: Thu Apr 23, 2009 11:39 am
by gj0519
Thanks,
Now I am having a few problems getting things lined up correctly. Here is the code you gave me that I have tweaked some.
I also have attached a png of my output. It's close but not lining up, I am trying to get the cells lined up with my headers.
Any ideas?
GJ

Code: Select all

$lasthdr2="";
 $lasthdr="";
echo "<h3 align='center'>Game Schedule</h3>";
echo "<table width='550' border = '1' align='center'>";
echo "<tr><th>Day</th>
          <th>Time</th>
          <th>Game</th>
          <th>Field</th>
          <th>Team</th>
          <th>Referee</th>
     </tr>";
echo "</table>";
 while($list=mysqli_fetch_assoc($result)) {
     extract($list);
     echo "<table width='550' border='1' align='center'>";
     $hdr=$gm_date . $gm_time . $gameid . $fld_name;
 
     if($hdr != $lasthdr) {
         echo "<tr><td>$gm_date</td><td>$gm_time</td><td>$gameid</td><td>$fld_name</td>";
         $lasthdr=$hdr;
     } else {
         echo "<tr><td></td></tr>";
     }
     echo "<td>$team_name</td>";
     $hdr2=$ref_lname;
     if($hdr2 != $lasthdr2)
          echo "<td>$ref_lname</td></tr>";
          $lasthdr2=$hdr2;
 
     echo "</table>";
 }
?>

Re: Remove duplicate records

Posted: Thu Apr 23, 2009 12:27 pm
by califdon
That's because you're creating a new Table for every row. Everything should be in one Table, then the columns will line up. There should be just ONE <table> tag, before the beginning of your loop, and ONE </table> tag, after the close of your loop.

Re: Remove duplicate records

Posted: Thu Apr 23, 2009 2:49 pm
by gj0519
Thanks,
Did not even think about that. Now everything lines up.

GJ

Re: Remove duplicate records

Posted: Thu Apr 23, 2009 3:01 pm
by gj0519
1 more thing, my 2nd team name displays below the row it's associated with which is what I want but it left aligns, and I can't figure out how to get it to align under the other team name column.
GJ

Re: Remove duplicate records

Posted: Thu Apr 23, 2009 8:53 pm
by califdon
I suggest that you draw out on a piece of paper just how you want this to appear. Then look at where each <tr> and <td> tag must go to make that happen. Then look at your code logic and place the tags where they need to be. That's always the way you have to design something like this.

Re: Remove duplicate records

Posted: Fri Apr 24, 2009 10:48 am
by gj0519
Thanks for the suggestion.
That's what I'll do.