Jump to content

how to group duplicates?


BigTime

Recommended Posts

I have a table that has pairs of matching records that are identified as matching on a specific column.  In other words, 2 users submit data on the same record, and they are identified as matching and belonging to the same record data because the table column gameid is the same for the pair.

 

How can I output the information for the pair of matching records for each of these into a table repeating it until Ive looped through all records?

 

In other words I want to create 1 table with headers for each matching pair all the way down...

 

thanks in advance.

Link to comment
Share on other sites

a simplified version of the structure is:

ID (unique)

gameid (records match on this)

hometeam

awayteam

homescore

awayscore

week

status (turns to 1 when data is accepted by admin and therefore eliminated from query results)

 

 

My current query is:

SELECT * FROM table WHERE week='$week' AND status='0'

 

$week is passed via post

 

Thanks for the help, MasterACE14

 

Link to comment
Share on other sites

all data resides in the same table, but 2 records for each week will have the same gameid

 

so awayteam reports scores and hometeam reports scores for the same gameid

 

I then want to view that data kind of in a per gameid table so I see them together.  I have to compare their reports and if they match accept them - if they dont I auto email them with a notification of a conflict in scores reported.

 

hope that helps clarify.

 

right now Im just ordering by certain columns and its a giant wall of text table, and Id like to make it easier on the eyes to see the matching gameids together - if one has not reported then I only see one record, but if both have reported I can easily see a table with 2 record rows.

 

thanks again.

Link to comment
Share on other sites

Add an ORDER BY gameid to your query and you'll get the rows in that order.  Your script simply needs to loop through the result set checking to see if the $gameid changed, and setting a boolean based on finding 2 records for that gameid (home report and away report).  Whichever one is read first is your baseline.  If any of these conditions occur:

 

- Only 1 report

- Scores don't match

 

Then you generate the report. 

Link to comment
Share on other sites

thanks Gizmola

 

Ive got an order by now in there which is my wall of text Im trying to get away from.

 

Its the second part of your response Im looking for guidance in - the loop and checking. 

 

Can you point me to something that may help guide me, or provide an example?

Link to comment
Share on other sites

You can put them into an array, then loop through by key.

 


$rs = mysql_query("SELECT * FROM table WHERE week='$week' AND status='0'");
$games = array();
while ($r = mysql_fetch_assoc($rs)) {
  $games[$r['gameid']][] = array(
    'hometeam' => $r['hometeam'],
    'awayteam' => $r['awayteam']
  );
}


foreach ($games as $game_info_arr) {
   echo "<table><thead><tr><th>Home</th><th>Away</th></tr>";
   foreach ($game_info_arr as $game) {
     echo "<tr><td>{$game['hometeam']}</td><td>{$game['awayteam']}</td></tr>";
   }
   echo "</table>";
}

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.