overwriting values into logs

DragonHighLord

Hello everybody,
What I want to do is to maintain a lsit of high scores of games played. I'm guessing that the easiest way to do this is with .txt file logs instead of database tables, because I would have numerous amounts of tables (1 for each game). I have no problem with writing to the file in the format of "name||game||score" then pulling the data out in variable form $name, $game, $score, so I can do a database query on the name,(even pull the names out in order of highest score firsall) but what I want to know......All I can do is just keep adding to the file. How do I restrict the file to say only 100 entries? and then have it check the contents of the file and only enter a score if it is higher than the 100 in there?

Weedpacket

Originally posted by DragonHighLord
I'm guessing that the easiest way to do this is with .txt file logs instead of database tables, because I would have numerous amounts of tables (1 for each game).

No, that's the precise reason why you would use a database. And why would you need a separate table for each game? (I can think of a couple of reasons, but I don't know your situation well enough to know if they apply.)

DragonHighLord

Well, I would use a database, but I'm not sure how you could put it in there without seperate tables, the only data I need to enter is the person's name and their score for each game. So if, I have 2 games, I would 2 tables named Game1 and Game2 with the fields name and score, that way I could keep 100 names/scores combos for each game. If you can tell me the easiest way to do this, I'm all ears, thanks

Weedpacket

You could do that in one table:

game  | player | score
------+--------+------
Game1 |Tom     | 176
Game1 |Dick    | 295
Game2 |Tom     | 406
Game1 |Harry   | 200
Game3 |Tom     | 12
...

Normalisation conventions would see that turned into three (one to just list the different games, one to list the different players, and one to link them together with their scores), but the point is that you don't need a seperate table for each game, any more than you need a separate table for each score.

DragonHighLord

Oh, ok, I could just do a query to pull out the entries of that game name to get the top scores of that game...makes sense then, thanks

DragonHighLord

Ok, I just tried that, but I can't make duplicate entries (ie, the same game name),

Weedpacket

What's your table schema? It sounds like you've set the game column to UNIQUE. None of the fields in that single table ought to be unique.

A normalised version would have three tables:

scores

id | gameid | playerid | score
---+--------+----------+------
1  | 1      | 1        | 176
2  | 1      | 2        | 295
3  | 2      | 1        | 406
4  | 1      | 3        | 200
5  | 2      | 1        | 12

games

id | game
---+-----
1  | Game1
2  | Game2
3  | Game3


players

id | player
---+-------
1  | Tom
2  | Dick
3  | Harry

(Using the same data as before).

One advantage is that each game and player is only named once (with UNIQUE id fields), so that, for example, you don't run the risk of misspelling a name at some point, and it's also a lot easier to get a list of all games or all players.

It does make the "top 100 scores" for a given game a bit more complicated, though:

SELECT score, players.player as player from scores, players, games WHERE scores.gameid=games.id AND player.id=scores.playerid AND games.name='$game' ORDER BY score DESC LIMIT 100

DragonHighLord

That makes much more sense and seems much easier to just keep in 1 table like you said before...but I don't have any UNIQUE fields at all

1 table: high_scores
3 fields: game, player, score
and the game field is primary, but there are no unique fields at all, still doesn't let me create multiple records of game1

update: ok I just dropped game as the primary and lets me create multiple records...weird, I thought for some reason you had to have a primary key?

But, the only problem I have is if I have 20 games, I'll need 100 records of each game, (that's 2000 records) seems like there should be some easier way.....reason why I thought of log files at first, just have a txt file for each game in a directory somewhere and let it create them as it goes, with the database, I'll have to create 100 blank records of the game names so that the code can update the player/score fields later.

Weedpacket

Well, that's the problem primary keys are automatically UNIQUE.

No, I didn't have a primary key in that single table, and yes, there should be one there.

Just make a new field "id", make it auto_increment, and make that the primary key.

As for the number of records, don't sweat it - the database will cope.

You shouldn't be needing to set up empty records to start with, though; just add them as you need them. You don't say whether one player could have multiple scores on a given game or not; if they can, it's just an INSERT every time you get a new result, if they can't well, you find the appropriate game/name combo in the list and UPDATE the score.

You probably don't need to DELETE rows all that often; "SELECT id,player,score FROM games ORDER BY score WHERE game='$game' DESC LIMIT 100" will give you no more than the top 100 scores for $game no matter how many records there might be (and you'll still have the scores later if you decide to go up to 200)

So there won't be 100 entries in the database at first for any game: that's probably something that should be handled by the script, not the database. Ask for the top 100 scores, find out how many are actually retrieved, and loop through those.