Milap Milap - 1 year ago 96
SQL Question

Android: Check a high score SQL table and add score

I have a sqlite database that holds the high scores of a game. I can successfully add a new score and I can show all the scores, but I'd like to add a check so that

  1. There are at most 5 rows

  2. The rows are always sorted

  3. Only add if the new score beats the scores already on the table

Here's my adding a random score:

// Check if high score

// Add to leaderboard
if (Difficulty.equals("Hard")){
BackgroundTask backgroundTask = new BackgroundTask(this);
backgroundTask.execute("add_hminfo", rank, correct, timed);

Me adding a new row using ASyncTask in

else if (method.equals("add_hminfo")){
String rank = params[1];
String score = params[2];
String time = params[3];

SQLiteDatabase db = dbops.getWritableDatabase();
dbops.addhmLeaderboard(db, rank, score, time);
return "High Score added";


And my SQL insertion into the database

public void addhmLeaderboard(SQLiteDatabase db,String rank, String score, String time){

ContentValues cv = new ContentValues();

cv.put(TableInfo.LB_RANK, rank);
cv.put(TableInfo.LB_SCORE, score);
cv.put(TableInfo.LB_TIME, time);

db.insert(TableInfo.TABLE_NAME3, null, cv);
//Log.d("Database Operations", "One row inserted");

I currently have rank in my database simply as a placeholder as all "1"s. Ideally this would be my primary key that automatically increments 1-5, but I'm a little confused on that as well.

How can I check to see if a new score is in the top 5 of scores, and then add that new score in the necessary spot?

Answer Source

In order to get the top 5 scores you can query:

db.query(TableInfo.TABLE_NAME3, null, null, null, null, null, TableInfo.LB_SCORE + " DESC, " + TableInfo.LB_TIME + " ASC", "5");