How to get the score of a certain player in my sqlite database

Question

I have a table with the following columns:

id, PlayerName, PlayerScore

with the rows of

1, Ahmad, 100

2, Sara, 100

3, Mohammad, 100

and a recylcerview that contains name and score only, I want to add 100 more points to the score column of a certain player if he does a certain condition, my problem here is that I don't know how to get a player's score to add to it 100 more points.

I use this method to insert scores:

fun insertScore(player: PlayersModel): Int {
    val db = writableDatabase
    val value = ContentValues()

    value.put(Col_Name, player.name)
    value.put(Col_Score, player.score + 100)

    val success = db.update(TABLE_NAME, value, "PlayerName='" + player.name + "'", null)
    db.close()

    return success
}

and this method to add points when the condition is satisfied:

    private fun rightAns(button : Button, score: Int) : Boolean{
    val rightAns = intent.getStringExtra("clip")
    if (button.text.contentEquals(rightAns)){
        val player = votingPlayer.text.toString()
        val playersModel = PlayersModel(player, score)
        dbHelper.insertScore(playersModel)
        Log.e("score: ", "100")
        return true
    }else {
        return false
    }
}

This method adds only 100 points and then stops. I thought about getting the player score then storing it in a variable but I don't know how to apply this,

Answer 1

If you want to alter the score by an amount (add or subtract) from the score then you can do this by effectively using the the SQL:-

UPDATE the_table SET the_score_column = the_score_column + the_value_to_change_the_score_by WHERE the_name_column LIKE the_value_of_the_name

• the_.... being indicative rather than the actual name/value

The SQLiteDatabase update convenience method, due to it encasing/wrapping values doesn't facilitate this. So instead you need to execute the respective SQL. You can use the SqliteDatabase execSQL method to do this.

• note that the version that binds the values has been used, this reduces/eliminates SQL Injection (basically it encases the values within single quotes, as does the use of ContentValues).

As such you could use a function along the lines of:-

fun increaseScore(player: PlayersModel) {
    this.writableDatabase.execSQL("UPDATE $TABLE_NAME SET ${Col_Score} = ${Col_Score} + ? WHERE $Col_Name LIKE ? ", arrayOf(player.score.toString(),player.name))
}

To answer the actual question posed (which would be less efficient as it retrieves the existing score to then drive the update), you could use something like:-

fun getPlayerScoreByName(playerName: String): Int {
    var rv = 0
    val csr = this.writableDatabase.query(TABLE_NAME, arrayOf(Col_Score),"$Col_Name=?", arrayOf(playerName),null,null,null,null)
    val idx = csr.getColumnIndex(Col_Score)
    if (csr.moveToFirst()) {
        rv = csr.getInt(idx)
    }
    csr.close() /* SHOULD ALWAYS CLOSE A CURSOR WHEN DONE WITH IT */
    return rv
}

• Note it is not recommended to ever close the database unless it must be closed (it will be closed as part of the App cleanup). As such, the code does not close the database.

---

Working example/demo (based upon what your code may be i.e. some assumptions made)

---

To insert the Players the following function exists along with the 2 functions above:-

fun insertPlayer(player: PlayersModel): Long {
    val cv = ContentValues()
    if (player.id != null) cv.put(Col_Id,player.id)
    cv.put(Col_Name,player.name)
    cv.put(Col_Score,player.score)
    return this.writableDatabase.insert(TABLE_NAME,null,cv)
}

Activity Code (note that the main thread is used for convenience and brevity) used is:-

const val p1 = "Ahmad"
const val p2 = "Sara"
const val p3 = "Mohammad"
class MainActivity : AppCompatActivity() {
    lateinit var db: DBHelper
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = DBHelper.getInstance(this)
        db.insertPlayer(PlayersModel(1, p1, 100))
        db.insertPlayer(PlayersModel(2, p2, 10))
        db.insertPlayer(PlayersModel(3, p3, 100))
        logPlayerScores("STG1")
        db.increaseScore(PlayersModel(-99 /* doesn't matter as id is ignored */,p1,100))
        db.increaseScore(PlayersModel(2,p2,50))
        db.increaseScore(PlayersModel(3,p3,-55))
        logPlayerScores("STG2")

    }

    fun logPlayerScores(tagSuffix: String) {
        val allPlayers = arrayOf(p1,p2,p3)
        for (p in allPlayers) {
            Log.d("DBINFO_$tagSuffix", "Score for $p= ${db.getPlayerScoreByName(p)}")
        }
    }
}

i.e the code:-

1. Inserts the three players
2. Logs the scores of the 3 players
3. adjusts the scores:-
   1. adding 100 to the first player, then
   2. adding 50 to the second player, then
   3. adding -55 to the third player (i.e. subtracting 55)
4. Logs the scores (now adjusted)

Thus the output to the Log is:-

2023-07-12 19:54:12.866 D/DBINFO_STG1: Score for Ahmad= 100
2023-07-12 19:54:12.867 D/DBINFO_STG1: Score for Sara= 10
2023-07-12 19:54:12.867 D/DBINFO_STG1: Score for Mohammad= 100


2023-07-12 19:54:12.869 D/DBINFO_STG2: Score for Ahmad= 200
2023-07-12 19:54:12.870 D/DBINFO_STG2: Score for Sara= 60
2023-07-12 19:54:12.870 D/DBINFO_STG2: Score for Mohammad= 45

• i.e. the expected results