PHP/SQL Highscores, avoiding duplicate entries

[Olivier]

As you (probably ) know I'm running some webgames at my website, and they have global highscores. I'd like to get rid of duplicate entires having the same player name. Basically for a given name I'd like to keep the highest score only.

I'm using an SQL database and wanted to know if the comparison/replace stuff can be made directly with the SQL command REPLACE? And if yes how? (I checked the SQL doc and I think the answer is no).
I know I can do it with PHP but it would probably have a higher load on the server as I would have to first read the data, eventually delete a row and then write a new row.

PS: Yes it's rather an easy method assuming each player would use a unique username, but I think setting up a login system would be over complicated.

[Indiepath]

If each player has a unique ID then it's quite simple but you will need to insert a score into the table if the player is not there already.

Code to update :

UPDATE high_score_table
SET score = 'nnn', level = 'nn'
WHERE unique_id = 'user_id'

[Olivier]

AFAIK my players don't have unique IDs. I'd be glad to know how to do it. For the moment I'm simply assuming each player would use a unique name (VARCHAR).

As you guess I'm pretty new to SQL. According to the official doc I could use the INSERT command with the ON DUPLICATE KEY UPDATE statement, that would act like an UPDATE command.
Anyway I think the main problem is comparing the old score with the new one?

[Adam]

I don't think running an extra SQL query on score submission is going to be a big server hit.

Are you trying to stop a user constantly submitting a score from a single play session or are you trying to have one score per player? If you are doing the latter without unique user tracking i think you are going to a lot of trouble just to anoy players.

[Olivier]

I'm just trying to have one score per player. How to track users then?

Would players really be annoyed to see only their highest score listed? Do some players *feel better* after having filled 10 entries in a row?

[Teq]

Why not let them submit multiple times? That way the player has a summary of their progress and for your _main_ scoreboard you can always do something along the lines of

SELECT player, MAX(score) AS m_score FROM score_board GROUP BY player ORDER BY m_score 

[Olivier]

>That way the player has a summary of their progress
That's not a bas idea.

But I'm having only one scoreboard. Could you please explain what your query does?
EDIT: Ok I think your query displays in growing order all the scores of a given player, and that for each player in the table. Am I right?

[Sharkbait]

Actually, the query displays the maximum score for each player, with the list sorted in increasing score order.

If there is more than one entry with the same player name, only the entry with the highest score is retained.

[Olivier]

Makes more sense Sharkbait, thanks. I have a problem with Teq's code. I think it's because I'm using a WHERE clause which is incompatible with AS:
http://dev.mysql.com/doc/refman/5.0/...ith-alias.html

Unfortunately I cannot remove the WHERE clause. Any ideas on mixing both SQL queries? Mine is below:

SELECT player, score, level, date FROM scoreboard WHERE IDgame='$IDgame' ORDER BY score DESC LIMIT 50 

[Danimal]

replace where with having, something like...

SELECT player, level, date, max(score) AS 'm_score' FROM scoreboard GROUP BY player HAVING IDgame='$IDgame' ORDER BY m_score DESC LIMIT 50

[Olivier]

Thanks Danimal but I get a "Query failed" error with your code. After several trial and errors I came up with the code below:

SELECT player, MAX(score), level, date FROM scoreboard WHERE IDgame='$IDgame' GROUP BY player ORDER BY score DESC LIMIT 50 

I don't really know why it works as I'm not using an alias. The only thing that doesn't actually work is if I have "ElecStar" and "elecStar" as player entries, both a discarded. Strange, maybe something to do with upper/lower case letters. Any ideas?

[Olivier]

No my code must be false, I don't get an error but the score listing is badly ordered.

[Sharkbait]

Not sure about this but ORDERing simply by 'score' wouldn't work because the ORDER clause only works with columns (or possibly expressions) that are included in your query.

Try ORDERing by MAX(score) instead of score.

[Olivier]

Try ORDERing by MAX(score) instead of score.

You are right Sharkbait, the ordering is correct now. Thank you very much.

I'm still experiencing a strange thing. Example:
I have several "ElecStar" and "elecStar" as player entries. One of the "ElecStar" entries has the highest score, only that highest score is displayed which is good, but with "elecStar" as player name.
Same thing with "seth" and "Seth", both have several entries too.

EDIT: Maybe do I have to use a particular function when selecting "player" with the SELECT clause?

[Werzel]

You are right Sharkbait, the ordering is correct now. Thank you very much.

I'm still experiencing a strange thing. Example:
I have several "ElecStar" and "elecStar" as player entries. One of the "ElecStar" entries has the highest score, only that highest score is displayed which is good, but with "elecStar" as player name.
Same thing with "seth" and "Seth", both have several entries too.

EDIT: Maybe do I have to use a particular function when selecting "player" with the SELECT clause?

That depends on whether you want "ElecStar" and "elecStar" to be different names. If you do, try this:

SELECT DISTINCT(player), etc. 

If you don't, you could format them all in upper or lower case using:

SELECT UPPER(player), etc. 

or

SELECT LOWER(player), etc. 

You'll have to add this to your GROUP BY as well. Hope this helps

Caveat: I haven't tried these in a SQL editor so they may not be perfect

[Olivier]

Thank you Werzel. Using SELECT DISTINCT doesn't change nothing, and GROUP BY DISTINCT causes an error. It doesn't really matter if "ElecStar" and "elecStar" are considered as different names.

I don't understand because I have (take look at the CrazyCrab listing):
ElecStar 58530
elecStar 53430
elecStar 50310
elecStar 45730
elecStar 43140
elecStar 41070

And then with the previous code (using Sharkbait's suggestion) the output is:
elecStar 58530

EDIT:
Ideally I would like the output to be:
ElecStar 58530
Or if the name entries are considered different:
ElecStar 58530
elecStar 53430

EDIT2: Ok maybe I should stick with the previous working query, I guess ElecStar won't complain about his name not begginning with a capital letter.

[Teq]

Sorry for taking so long to reply, the ASC order on score was a mistake in my reply :P

ok, so you want users to be distinct by name but case insensitive? np, just do

SELECT DISTINCT UPPER(user_name), MAX(score) ..... 

[Werzel]

I could've posted better examples ...

Using the data you posted I created a simple table with player and score. I'm using Oracle 10g and TOAD to run the SQL, but the SQL is standard stuff.

SELECT DISTINCT(player), MAX(score) FROM scoreboard GROUP BY player ORDER BY MAX(score) DESC 

or

SELECT player, MAX(score) FROM scoreboard GROUP BY player ORDER BY MAX(score) DESC 

gives me:

ElecStar 58530
elecStar 53430

[since the player names are slightly different, the grouping treats them differently]

and

SELECT UPPER(player), MAX(score) FROM scoreboard GROUP BY UPPER(player) ORDER BY MAX(score) DESC 

gives me:

ELECSTAR 58530

[Here we convert all the names to uppercase in the GROUP BY so that ElecStar and elecStar both come out as ELECSTAR]

[Olivier]

ok, so you want users to be distinct by name but case insensitive?]

Not really Teq, or maybe we don't understand correctly. Your query outputs all player names in uppercase letters, that's bad.

I meant that it doesn't matter if "ElecStar" and "elecStar" are considered as the same name, the output being:
ElecStar 58530

Or if "ElecStar" and "elecStar" are considered as different names, the output being:
ElecStar 58530
elecStar 53430

[Teq]

Werzel's example should do what you want, just use something MAX(user_name) or another group by function to select one of the user inputed names on the field output.

[Olivier]

No success with Werzel's query so far. Whether I'm using SELECT DISTINCT(player) or simply SELECT player, the output is:
elecStar 58530
Despite the real entry being: (as it can be seen here, row #14)
ElecStar 58530

Teq I also tried to use GROUP BY MAX(name), got an error. What other function could I use?

A hint though. I'm sure that the name displayed is actually the first one that entered the table. In other words "elecStar" was submitted before "ElecStar", in the same way "seth" was submitted before "Seth". I know that because each row uses a unique ID number (auto_increment), hope it helps.

Maybe something to do with the WHERE clause? So far my query is:

SELECT player, MAX(score), level, date FROM scoreboard WHERE IDgame='$IDgame' GROUP BY player ORDER BY MAX(score) DESC LIMIT 50 

[Werzel]

Here's a question. If you're avoiding duplicate entries in your high score table (based on player name), then you've really got a "top 50 players" table, right?

If one person is really good at your game, and has your 10 highest scores, they will only appear on your table once (with their highest score). Instead of occupying places 1-10, they'll only take up first place.

[Olivier]

Yes Werzel, this is exactly what I'm trying to achieve.

At the beginning of the thread I was thinking of another way to do it. I would UPDATE a score only if it's higher than the one already in the database, and that for a given player of course.
But if the player isn't in the database, like Indiepath wrote, I still need to INSERT the data.

I guess I'll have to go that way, using 2 SQL queries. First I will have to read all the entries to check if the player isn't already in the database. If no INSERT a new row, if yes check if score is higher than the previous one, and if yes UPDATE row. Hope the load won't get too big on the server.

I think I have a headache now.

[electronicStar]

Okay guys, you are infriging on my copyrights, but we can make a deal, you pay me only 1.50$ for each time my alias is cited

[Werzel]

I think I have a headache now.

This may or may not help your headache:

SELECT DISTINCT(player), score, level, date
FROM scoreboard a
WHERE IDgame='$IDgame'
AND score = (SELECT MAX(score) FROM scoreboard b WHERE a.player = b.player)
GROUP BY player, score, level, date ORDER BY score DESC LIMIT 50 

If you're not bothered about the CaSe of the usernames, this should work:

SELECT DISTINCT(player), score, level, date
FROM scoreboard a
WHERE IDgame='$IDgame'
AND score = (SELECT MAX(score) FROM scoreboard b WHERE UPPER(a.player) = UPPER(b.player))
GROUP BY player, score, level, date ORDER BY score DESC LIMIT 50 

[Sharkbait]

OliverPearl,

I think you have a COLLATION problem here. Again, I'm not a MySql expert but as far as I know, text/char data types in MySql are case insensitive by default. That means 'ElectStar', 'elecstar', 'EleCsTaR' and all the other variants will be treated as identical. The DB engine will have to select one of these variants when grouping query results so which one you get really depends on Mysql's internal implementation.

One way to solve this is to make your 'player' column case sensitive using the COLLATE keywod to specify a case sensitive character set. Depending on whether you're using an admin GUI for MySql, you may even have this option available without having to write SQL Create Table statements from scratch.

I suggest you have a look at:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

[Olivier]

I think you have a COLLATION problem here.

That made it Sharkbait! Yes it was a "simple" collation problem. Using phpMyAdmin I modified the "player" column to use a case sensitive collation. I hope that thread will help other devs.
Thank you very much to Sharkbait and everyone who helped. You guys were very very helpful!

@ electronicStar about the copyright infrigement. Do you take PayPal payments?

[Olivier]

Me again, sorry!
Unfortunately the level got sometimes mixed, don't really know why. If I have (ID is primary & auto_increment):
ID player score level
2 Kunci 55100 10
1 Kunci 10050 2
The output will be:
player score level
Kunci 55100 2

The level doesn't correspond to the highest score because "level 2" was first entered (first ID). In other words the GROUP BY clause seems to take the first entered level, no matter the MAX(score).

My question is: When selecting SELECT MAX(score), is there a clause I could use to force the selection of the corresponding level?

[Werzel]

OliverPearl, did you try the examples I posted? They should solve your "corresponding" and "collation" problems.

[Sharkbait]

Werzel's first query (in the 'headache' post) should solve the correspondence problem now that you have collation for the player column set to a case sensitive charset.

Werzel's second query is great if you want to make the player name case insensitive, but you want the opposite and neither query can achieve that on its own.