top 100 scoreboard php/mysql

[barrax]

I'm hoping someone can help me with this - its been bugging me for ages. I made a flash game (www.camperstrike.co.uk) and im trying to restrict the number of scores that get entered into the database.

Currently i do this:
1.insert the score into the db at the end of the game.
2.if the number of records ($num) is more than 100 look at the db in ascending order (scores column).
3.remove the lowest score.

But for some reason the db continues to add scores 🙁 is there a better way of doing this?

Im guessing this is happening because more than 1 person could be adding to the db at a particular time.

Ideally i'd like to look at the DB and "trim" the lowest scores back so there are only 100.

Heres the current code.

<?php
include_once ("./Connections/db_csscores_include.php");

$playername = $_GET['playername'];
$accuracy = $_GET['accuracy'];
$score = $_GET['score'];
$num = $_GET['num'];


$sql = "INSERT INTO csscores (playername,accuracy,score) VALUES (\"$playername\",\"$accuracy\",\"$score\")";
$result=mysql_query($sql,$connection) or die ("The was a problem updating the DataBase");
echo "inserted $score into DB....\n";
if($num>="100"){
	echo "there are more than 100 records\n";
	//review current scores
	$sql="SELECT * FROM csscores ORDER BY 'score' ";
	$result=mysql_query($sql,$connection) or die ("there was a problem ordering records");
	$lowscore = mysql_result($result, 0, 'score');
	//delete the lowest score
	$sql="DELETE FROM csscores WHERE score = $lowscore";
	$result=mysql_query($sql,$connection) or die ("there was a problem deleting a record");
	echo "deleted score: $lowscore\n";
	}
?>

Thanks
B

[barrax]

bumpers

[barrax]

ok then - another way of looking at it:

I insert the score,
order the scores asc,
delete any record above the 100th row.

how do i do the latter? I can look it up if i know what to look for.

Cheers
B

[Jason_London]

I wouldnt delete by low score, cos if two people at the bottom of the table both have the same score they will both be deleted.

what I'd do is insert an int field into the database as the primary key, then use that to indicate which entry is to be deleted.

the code you have already looks fine...

P.S. i like the game

[Jason_London]

another Idea...

find the value of the 100th row i.e.

$result = "select * from CSScores as order by score desc"
$Last_Score = mysql_query($result, 99, 'score'); //returns the 100th score
delete from CSScores where score< $Last_Score

[barrax]

I did it this way in the end

1. insert the score
2. order the scores DESC
3. if number of scores > 100 remove records past #100

Thanks anyway

[laserlight]

Would it be better if you left the scores there, but did a

SELECT * FROM tablename ORDER BY scores DESC LIMIT 100

?

[barrax]

not sure tbh - i'm a bit new to this.
Wouldnt that mean that my database would still be growing when in fact i only need 100 records!?