how to group duplicates?

[BigTime]

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.

[MasterACE14]

can you post your table structure?

[BigTime]

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

 

[MasterACE14]

I'm a bit confused about what you're trying to achieve. Are you trying to select data from two or more tables, joined by a common id (`gameid`) ?

[BigTime]

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.

[gizmola]

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. 

[BigTime]

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?

[BigTime]

bump

[jon23d]

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>";
}

[BigTime]

Jon

 

Thank you very much for the example.  I'll give it a go.

[BigTime]

Jon

 

Thanks again for your help.  I was able to use your example to work my way into a solution.