Sorry, I think I got myself confused when I wrote my last replies.
I’m calling the php file from Java app on client side. The php script connects to SQLite. I was worried that if someone finds that path to the database file, they can write their own script to connect it. (though I don’t know how they can find out the folder hierarchy)
Don’t put your database in a folder that’s accessable from the web. It’s that simple. Even if you do, the worst anyone should be able to do is download it.
As expected no ra4king here. He has leader board for his snake applet game. You may ask him.
;D I have over 50 unread posts, I haven’t been catching up with JGO posts these days.
Anyway, for my Snake and JDoodle Jump, I use text files, not databases. However, it may be fun and all to try out text files but it’s best to go the database way.
More info on my highscore system:
I use a 2 text file system, where one contains the top daily/weekly/monthly/all-time top scores, and the other (ALL file) holds every single submitted score.
When a player submits a score, my server adds the score to the ALL file, then it checks if it alters the top scores, the score is inserted in order into the top scores list and then the file is re-written.
Thanks for the comments.
I have got the basic operations working with SQLite. Though I’m not done, yet. I placed the db file outside web root.
I thought about just storing the top 20 records to keep the operation time as fast as possible. Every time a record is added when there are 20 records, it would update the lowest score possessing the oldest date. The newer and higher score would be on top. Problem is eventually the score board gets full with same high scores. I never thought of daily/weekly/monthly thing. I think it would be a good idea to implement on mine, too.
Again, don’t bother with UPDATE. Just insert new scores into the DB and use a query to pick the top ones. Just add an index on the score and it’ll be as fast as anything you can imagine.
It used to be that a skillful player could own the entire high score board, but the modern idiom (for games that still actually keep score at all) is to only allow one entry per player. So what you want is a query like this:
select name, max(score) as highscore from scores group by name order by highscore limit 20
Yea, same names get messy. Excluding same names from display sounds good.
I will implement these things I learned from here.
I appreciate you guy’s comments. Thanks a lot.
I’m almost done with this system.
There is one thing I’m struggling with. If I insert a new high score with new name, it gets on the top.
My issue is that the player with same name scoring the same score as the highest after second time does not put his position up. For example…
I insert a highest score with name “A” which gets on top.
Then I insert a higher score with name “B” which appear on top.
If I insert a score same as “B” with name “A”, it does not go on top.
However, if I insert a same score with name “C”, it gets on top.
My table is structured as following: “CREATE TABLE Records (name TEXT, score INTEGER, date DATE)”
The date contains with the format, “Y-m-d H:i:s”.
This is the query I’m doing to get them.
$query = “SELECT name, MAX(score) as highscore FROM Records GROUP BY name ORDER BY score DESC, date DESC LIMIT 0, 10”;
Can someone suggest what I’m doing wrong?
Yah sorry that ordering should have been DESC unless you wanted a low-score list 
You probably should order by highscore, not score, but that shouldn’t actually change the results. I’m otherwise not seeing anything obviously wrong with the query. Could you maybe put up a sql dump of your current score database on a pastebin? Should be able to do it with sqlite3 /path/to/scores.db ‘.dump Records’
Sorry, I didn’t understand how to dump data.
Edit: I will get that as soon as I find some time. Sorry about that.
I’m really disinclined to work with that html output, sorry. Use the .dump command I gave you. If you don’t have shell access to run commands like that, I suggest you develop on a machine where you do.
Just a quick question. If I put a link to the db file itself, would that work? I downloaded the command line shell. I tried your advise and searched around, but sadly, I can’t get to access to my db for lack of my understanding.
That would work, sure.
Here is the file. I would appreciate if you could point out why I’m wrong. I want to learn from this.
Thanks a lot for your time.
Your version of sqlite is so old, I can’t even open the database with recent versions of sqlite. If I can reproduce this on my own, I’ll give it a try, otherwise I think someone else may have to eyeball the sql and see what’s wrong with it.
Let me try to make a table with the same data using SQLite Administrator program. I see that file is in SQLite 2.1.
Edited:
I’m starting to have a concern that this is an issue from sqlite version and the way I open the file in php. I want to try something before I bother anyone else.
2nd Edit:
After sproingie pointed out my sqlite version is old, I created a sqlite3 file allocated with same data using SQLite Administrator. The results would return the way I wanted correctly (different from the old db). I replaced my old file with that and tested. The php code would not open it. I found out that the problem was the class I was using to manipulate the sqlite file. I used the PDO and it worked perfectly.
sproingie,
Thanks a lot for your help and time. I’m really sorry that after all the hassle, it turned out that the mistake was my php code.
Thanks to everyone who commented and gave me advises.
Glad you found the problem in the end
Some anecdotal experience…
The company I work (Coverity) for uses and used SQLite quite extensively with our product over the course of the last 8 years. It does not scale well and seems susceptible to both corruption and concurrency issues.
[edit] While I don’t know if it’s any better in this regard, I would recommend either derby or hsqldb as they’re easy to setup, work well with Java, and will get around your original blocker.
[edit] I noticed that sproingle already recommended the above…