Eliminating duplicate results

bigtall

I'm progressing on a player database for a side interest and need some help. I'm not sure what exactly I'm not doing right here. I have an HTML form that passes a string to the php page for processing. My search works fine, I can enter a first or last name in the form and bring up the player list of those matching said data in a table. The problem is that I'm entering about 9 seasons (to date, more to come) of data so there will be one entry for each player for each season. On the results page I only want to have one result per player show up as I have a working link to a stat page for that player. I've fooled around with the array_search() but haven't had any luck yet. Here's my code:

$resultID = mysql_query("SELECT * FROM hitting WHERE lastname='$data' OR firstname='$data'") or die(mysql_error());

print "<table border=\"1\">";
print "<tr><th>Player</th></tr>";

$temp=array();

for ($x=0; $x<mysql_num_rows($resultID); $x++)
{
$row = mysql_fetch_assoc($resultID);

$firstname=$row[firstname];
$lastname=$row[lastname];

if(array_search($firstname,$temp)!==TRUE && array_search($lastname,$temp)!==TRUE)
{
print "<tr>";
print "<td><center><a href=\"player.php?firstname=$firstname&lastname=$lastname\">" . $row[firstname] . " " . $row[lastname] . "</a></center></td>";
print "</tr>";
}
else {
exit();
}

//After I've found the name once, I store it in the temp array so that it will HOPEFULLY be caught when the for loop starts again and it will exit out of the loop before it prints the duplicate results again.

array_unshift($temp, "$firstname");
array_unshift($temp, "$lastname");
}
print "</table>";

It's the if statement that is the work in progress. Taking out the if statement and array_unshift() statements and leaving the 3 print statements work fine, but as I said, I get duplicate results if the player is in the DB twice.

FoTo50

Does it matter which db row of the player shall be displayed?

If not, just take out the "for"-loop and take the first result for displaying:

...
$temp=array();

$row = mysql_fetch_assoc($resultID);

$firstname=$row[firstname];

........

array_unshift($temp, "$firstname");
array_unshift($temp, "$lastname");

print "</table>";
...

If yes you need to alter the "SELECT" in the first line to get the propper row of the player as the first one.

bigtall

That would solve my problem if I had unique first names or last names, but in a DB that will include some 2500+ names (and counting), chances are I'll have more than just the first case that's found. The code I have will search the entire DB thanks to the for() loop, but the reason i need some help is because since i'm going to have not only multiple matches on the first or last name, but multiple records for most of the players in the table I need a way to only post the individual player match once. Thanks for the thought though.

For instance: Jim Magilicuty has 3 entries in the table, Jim Brown has 2, and Jim Francis has 6. When I do a search, my results will be an HTML table of Jim Magilicuty's name 3 times, J.B.'s 2 times and J.F.'s 6 times. I only want the table to show their name once as clocking on the name will lead the web user to a bio page for said player.

FoTo50

I'm not sure if I understood your problem correct, but usually, I would solve this with 2 different tables:

1st table (maybe called 'hitting') stores all gaming records (seasons or whatever) referring to a 2nd table (lets call it 'player') with one entry for each player (with its unique ID).

Like this you have a unique row for each player where you can point to read out more information (stats, bio, etc...).

In other words: Jim Magilicuty can have maybe 3 entries in 'hitting' but only one in 'player'. This way you can have endless players and also endless seasons but you'll always be able to read out the exact datas for each player and vice versa.

bigtall

Yeah two tables might make this a little easier. Good thinkin :-p