How can I get the DB to stop overwriting previous records?

Question

So in the picture, the entire score column gets overwritten everytime a user finishes the quiz. I'm not quite sure which method I'm supposed to use to get it to save scores individually. I've tried to update instead of insert as well, and had no luck with it. I was using shared preferences to save the highest score and continue displaying in. But I want to implement that into the database as well.

MyDataBaseHelper.java

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Debug;
import android.util.Log;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

// class to provide operations with database

public class MyDataBaseHelper extends SQLiteOpenHelper {

    // Database name
    public static String DATABASE_QUESTION = "QUIZ.db";
    // Current version of database
    private static final int DATABASE_VERSION = 1;
    // Database table name
    private static final String TABLE_QUESTION = "QuestionBank";
    private static final String TABLE_USER = "Users";
    // All fields used in question table
    private static final String KEY_ID_QUESTION = "id";
    private static final String QUESTION = "question";
    private static final String CHOICE1 = "choice1";
    private static final String CHOICE2 = "choice2";
    private static final String CHOICE3 = "choice3";
    private static final String CHOICE4 = "choice4";
    private static final String ANSWER = "answer";

    // All fields used in user table
    private static final String KEY_ID_USERS = "id";
    private static final String USER_NAME = "name";
    private static final String USER_SCORE = "score";

    // Question Table Create Query in this string
    private static final String CREATE_TABLE_QUESTION = "CREATE TABLE "
            + TABLE_QUESTION + "(" + KEY_ID_QUESTION
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + QUESTION + " TEXT,"
            + CHOICE1 + " TEXT, " + CHOICE2 + " TEXT, " + CHOICE3 + " TEXT, "
            + CHOICE4 + " TEXT, " + ANSWER + " TEXT);";

    // User Table Query
    private static final String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + "(" + KEY_ID_USERS
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + USER_NAME + " TEXT, " + USER_SCORE + " INTEGER);";

    public MyDataBaseHelper(Context context) {
        super(context, DATABASE_QUESTION, null, DATABASE_VERSION);
    }


    /**
     * This method is called by system if the database is accessed but not yet
     * created.
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d("DBHelper", "Created db");
        Log.d("DBHelper", CREATE_TABLE_QUESTION);
        db.execSQL(CREATE_TABLE_QUESTION); // create question table
        db.execSQL(CREATE_TABLE_USER);
    }

    /**
     * This method is called when any modifications in database are done like
     * version is updated or database schema is changed
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldvers, int newvers) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUESTION);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
        onCreate(db);
    }

    /**
     * This method is used to add question detail in question Table
     */
    public long addInitialQuestion (Question question) {
        SQLiteDatabase db = this.getWritableDatabase();
        // Creating content values
        ContentValues values = new ContentValues();
        values.put(QUESTION, question.getQuestion());
        values.put(CHOICE1, question.getChoice(0));
        values.put(CHOICE2, question.getChoice(1));
        values.put(CHOICE3,  question.getChoice(2));
        values.put(CHOICE4,  question.getChoice(3));
        values.put(ANSWER, question.getAnswer());
        // insert row in question table
        long insert = db.insert(TABLE_QUESTION, null, values);
        return insert;
    }

    /**
     * To extract data from database and save it Arraylist of data type
     * Question
     */
    public List<Question> getAllQuestionsList() {
        List<Question> questionArrayList = new ArrayList<>();
        String selectQuery = "SELECT  * FROM " + TABLE_QUESTION;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);

        // looping through all records and adding to the list
        if (c.moveToFirst()) {
            do {
                Question question = new Question();

                String questText= c.getString(c.getColumnIndex(QUESTION));
                question.setQuestion(questText);

                String choice1Text= c.getString(c.getColumnIndex(CHOICE1));
                question.setChoice(0,choice1Text);

                String choice2Text= c.getString(c.getColumnIndex(CHOICE2));
                question.setChoice(1,choice2Text);

                String choice3Text= c.getString(c.getColumnIndex(CHOICE3));
                question.setChoice(2,choice3Text);

                String choice4Text= c.getString(c.getColumnIndex(CHOICE4));
                question.setChoice(3,choice4Text);

                String answerText= c.getString(c.getColumnIndex(ANSWER));
                question.setAnswer(answerText);

                // adding to Questions list
                questionArrayList.add(question);
            } while (c.moveToNext());
            Collections.shuffle(questionArrayList);
        }
        return questionArrayList;
    }

    public List<User> getAllUser(){
        String sortOrder = "SELECT * FROM " + TABLE_USER + " ORDER BY " + USER_SCORE + " DESC";
        List<User> userList = new ArrayList<User>();

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(sortOrder, null);
        if(c.moveToFirst()){
            User user = new User();
        }
        return userList;
    }

    public void addUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(USER_NAME, user.getName());
        // Inserting Row
        db.insert(TABLE_USER, USER_NAME, values);
        db.close();

    }

    public void updateUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(USER_NAME, user.getName());
        // updating row
        db.update(TABLE_USER, values, USER_NAME + " = ?",
                new String[]{String.valueOf(user.getId())});
        db.close();
    }

    public void deleteUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        // delete user record by id
        db.delete(TABLE_USER, USER_NAME + " = ?",
                new String[]{String.valueOf(user.getId())});
        db.delete(TABLE_USER, USER_SCORE + " = ?",
                new String[]{String.valueOf(user.getId())});
        db.close();
    }

    public boolean hasUser(String user){

        String searchQuery = "SELECT * FROM " + TABLE_USER + " WHERE " + USER_NAME + " = '" + user + "'";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(searchQuery, null);
        c.moveToFirst();

        if (c.getCount() > 0) {
            c.close();
            db.close();
            return true;
        } else {
            c.close();
            db.close();
            return false;
        }
    }

    public void addScore(int point){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(USER_SCORE, point);
        // Inserting Row
        db.update(TABLE_USER, values,null , null);
        db.close();
    }

}

QuizActivity.java

import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;

import java.util.List;

public class QuizActivity extends AppCompatActivity {

    private QuestionBank mQuestionLibrary = new QuestionBank();

    private TextView mScoreView;   // view for current total score
    private TextView mQuestionView;  //current question to answer
    private Button mButtonChoice1; // multiple choice 1 for mQuestionView
    private Button mButtonChoice2; // multiple choice 2 for mQuestionView
    private Button mButtonChoice3; // multiple choice 3 for mQuestionView
    private Button mButtonChoice4; // multiple choice 4 for mQuestionView

    private String mAnswer;  // correct answer for question in mQuestionView
    private int mScore = 0;  // current total score
    private int mQuestionNumber = 0; // current question number

    private String username = "";

    MyDataBaseHelper myDataBaseHelper;

    private static final String KEY_QUESTION_NUMBER = "questionNumber";
    private static final String KEY_SCORE = "currentScore";


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_quiz);
        // setup screen for the first question with four alternative to answer
        mScoreView = (TextView)findViewById(R.id.score);
        mQuestionView = (TextView)findViewById(R.id.question);
        mButtonChoice1 = (Button)findViewById(R.id.choice1);
        mButtonChoice2 = (Button)findViewById(R.id.choice2);
        mButtonChoice3 = (Button)findViewById(R.id.choice3);
        mButtonChoice4 = (Button)findViewById(R.id.choice4);


        Intent intent = getIntent();
        username = intent.getStringExtra("username");

        myDataBaseHelper = new MyDataBaseHelper(this);

        mQuestionLibrary.initQuestions(getApplicationContext());

        if(savedInstanceState != null){
            mScore = savedInstanceState.getInt(KEY_SCORE,0);
            mQuestionNumber = savedInstanceState.getInt(KEY_QUESTION_NUMBER, 0) - 1;
        }

        updateQuestion();
        // show current total score for the user
        updateScore(mScore);
    }

    private void updateQuestion(){
        // check if we are not outside array bounds for questions
        if(mQuestionNumber<mQuestionLibrary.getLength() ){
            // set the text for new question,
            // and new 4 alternative to answer on four buttons
            mQuestionView.setText(mQuestionLibrary.getQuestion(mQuestionNumber));
            mButtonChoice1.setText(mQuestionLibrary.getChoice(mQuestionNumber, 1));
            mButtonChoice2.setText(mQuestionLibrary.getChoice(mQuestionNumber, 2));
            mButtonChoice3.setText(mQuestionLibrary.getChoice(mQuestionNumber, 3));
            mButtonChoice4.setText(mQuestionLibrary.getChoice(mQuestionNumber,4));
            mAnswer = mQuestionLibrary.getCorrectAnswer(mQuestionNumber);
            mQuestionNumber++;
        }
        else {
            Toast.makeText(QuizActivity.this, "It was the last question!", Toast.LENGTH_SHORT).show();
            Intent intent = new Intent(QuizActivity.this, HighScoreActivity.class);
            intent.putExtra("score", mScore); // pass the current score to the second screen
            intent.putExtra("username", username);
            myDataBaseHelper.addScore(mScore);
            startActivity(intent);
        }
    }

    // show current total score for the user
    private void updateScore(int point) {
        mScoreView.setText(""+mScore+"/"+mQuestionLibrary.getLength());
    }

    public void onClick(View view) {
        //all logic for all answers buttons in one method
        Button answer = (Button) view;
        // if the answer is correct, increase the score
        if (answer.getText().equals(mAnswer)){
            mScore = mScore + 1;
            Toast.makeText(QuizActivity.this, "Correct!", Toast.LENGTH_SHORT).show();
        }else
            Toast.makeText(QuizActivity.this, "Wrong!", Toast.LENGTH_SHORT).show();
        // show current total score for the user
        updateScore(mScore);
        // once user answer the question, we move on to the next one, if any
        updateQuestion();
    }

    @Override
    protected void onResume() {
        super.onResume();
        /*
        MyDataBaseHelper dataBaseHelper = new MyDataBaseHelper((this));
        List<Question> questions = dataBaseHelper.getAllQuestionsList();
        if(questions.isEmpty())
        {
            Log.d("QuizActivity", "List is empty");
        }        dataBaseHelper.addInitialQuestion(new Question("1. When did Google acquire Android ?",
                new String[]{"2001", "2003", "2004", "2005"}, "2005"));
                */
    }

    @Override
    protected void onSaveInstanceState(Bundle savedInstanceState) {
        super.onSaveInstanceState(savedInstanceState);
        savedInstanceState.putInt(KEY_SCORE, mScore);
        savedInstanceState.putInt(KEY_QUESTION_NUMBER, mQuestionNumber);
    }

    @Override
    protected void onRestoreInstanceState(Bundle savedInstanceState) {
        super.onRestoreInstanceState(savedInstanceState);
        mScore = savedInstanceState.getInt(KEY_SCORE,0);
        mQuestionNumber = savedInstanceState.getInt(KEY_QUESTION_NUMBER, 0);
    }
}

HighScoreActivity.java

import android.content.Intent;
import android.content.SharedPreferences;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

public class HighScoreActivity extends AppCompatActivity  {

    private String username;
    private TextView txtScore;
    private TextView txtHighScore;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_highest_score);

         txtScore = (TextView) findViewById(R.id.textScore);
         txtHighScore = (TextView) findViewById(R.id.textHighScore);

        Intent intent = getIntent();
        int score = intent.getIntExtra("score",0);
        username = intent.getStringExtra("username");

        txtScore.setText(username + "'s Score: " + score);
//
//        SharedPreferences mypref = getPreferences(MODE_PRIVATE);
//        int highscore = mypref.getInt("highscore", 0);
//        if (highscore >= score){
//            txtHighScore.setText("High Score: " + highscore);
//        }
//        else {
//            txtHighScore.setText("New High Score: " + score);
//            SharedPreferences.Editor editor = mypref.edit();
//            editor.putInt("highscore", score);
//            editor.commit();
//        }

    }

    public void onClick(View view) {
        Intent intent = new Intent(HighScoreActivity.this, QuizActivity.class);
        intent.putExtra("username", username);
        startActivity(intent);

    }

    public void logOutOnClick(View view) {

        Intent intent = new Intent(HighScoreActivity.this, LoginActivity.class);
        startActivity(intent);
        finish();

    }
}

Answer 1

I believe the following is along the lines of what you need.

A changed signature so that you can pass the id of the user you want to update, a where clause (without the WHERE keyword) and the arguments to use in the whereclause.

That is without a where clause the update method will update ALL rows. (what I believe is your core issue)

As such your code in the database helper (sub class of SQLiteOpenHelper) could be :-

public void addScore(String username, int point){ //<<<<<<<<<< Changed signature
    SQLiteDatabase db = this.getWritableDatabase();
    String whereclause = USER_NAME + "=?"; //<<<<<<<<<< Added
    String[] whereargs = new String[]{username}; //<<<<<<<<<< Added
    ContentValues values = new ContentValues();
    values.put(USER_SCORE, point);
    // Inserting Row
    db.update(TABLE_USER, values, whereclause, whereargs);
    db.close();
}

You then need to call the addScore using myDataBaseHelper.addScore(username,mScore);

• Note this doesn't add the score but changes it to the passed value.
• The code above is in-principle code, it hasn't been tested so may have some errors.

---

Updating the score

(i.e. adding to the existing score (or subtracting if a negative value is provided))

public void addScore(String username, int points_to_add) {
    SQLiteDatabase db = this.getWritableDatabase();
    String sql = "UPDATE " + TABLE_USER + " SET " + USER_SCORE + " = " + USER_SCORE + "+? WHERE " + USER_NAME + "=?";
    String[] args = new String[]{String.valueOf(points_to_add), username};
    db.execSQL(sql,args);
    db.close();
}

• execSQL has been used as you cannot pass item names (e.g. column names via content values (a limitation of the insert method)).

Example

Considering the above addScore method and customised (for ease of testing) addUser method (just passing a string for the username and setting the score to 0) :-

    mDBHlpr = new MyDataBaseHelper(this);

    // Add some users (Score will be 0)
    mDBHlpr.addUser("Fred");
    mDBHlpr.addUser("Bert");
    mDBHlpr.addUser("Mary");
    mDBHlpr.addUser("Sue");

    mDBHlpr.addScore("Fred",1200);
    mDBHlpr.addScore("Sarah",50000); // non-existant user
    mDBHlpr.addScore("Mary",6000);
    mDBHlpr.addScore("Mary", -2000);
    mDBHlpr.addScore("Sue", -1000);
    mDBHlpr.addScore("Bert", -1700);

    SQLiteDatabase db = mDBHlpr.getWritableDatabase();
    Cursor csr = db.query("Users",null,null,null,null,null,null);
    DatabaseUtils.dumpCursor(csr);
    csr.close();
    db.close();

Then the expectations would be :-

• Fred has a score of 1200
• Bert will have a score of - 1700
• Mary has a score of 6000 which is then reduced by 2000 to 4000
• Sue will have a score of -1000
• Sarah, as the user doesn't exist results in no action nor any failure

The dumpCursor method will dump the cursor (all user rows) the result being (as expected) :-

I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534a4524
I/System.out: 0 {
I/System.out:    id=1
I/System.out:    name=Fred
I/System.out:    score=1200
I/System.out: }
I/System.out: 1 {
I/System.out:    id=2
I/System.out:    name=Bert
I/System.out:    score=-1700
I/System.out: }
I/System.out: 2 {
I/System.out:    id=3
I/System.out:    name=Mary
I/System.out:    score=4000
I/System.out: }
I/System.out: 3 {
I/System.out:    id=4
I/System.out:    name=Sue
I/System.out:    score=-1000
I/System.out: }
I/System.out: <<<<<