Selecting rows from sqlite database sometimes shows a same row

Question

I'm making multiple quiz app with sqlite database.

I randomly select 5 rows using "ORDER BY RANDOM()" in rawquery, but the same row sometimes comes up in a row.

It shows 5 different rows in some case. Random choosing also works correctly as well, so my rawquery seems to work correctly.

I just cannot find out in which case it shows the same question and choices in a row. Does anyone have the same problem as mine??

Here is my code.

public class QuizActivity extends Activity implements OnClickListener {

    int Correct = 0;
    int Cnt = 1;

    String fieldinfo;
    String question;
    String answer;
    String choice1;
    String choice2;

    private MySQLHelper mDbHelper;  
    private SQLiteDatabase db; 
    @Override  
    public void onCreate(Bundle savedInstanceState) {  
        super.onCreate(savedInstanceState);  
        setContentView(R.layout.activity_quiz);
        onQuestion();
    }

    public void onQuestion(){
        mDbHelper = new MySQLHelper(this);
        db = mDbHelper.getWritableDatabase();  
        try{           
            Cursor c = db.rawQuery("SELECT field, question, choice1, choice2, answer FROM WineQuiz ORDER BY RANDOM() LIMIT 5", null);
            if(c.moveToFirst());
            {
            while(c.moveToNext()){
                fieldinfo = c.getString(c.getColumnIndex("field"));
                question = c.getString(c.getColumnIndex("question"));
                choice1 = c.getString(c.getColumnIndex("choice1"));
                choice2 = c.getString(c.getColumnIndex("choice2"));
                answer = c.getString(c.getColumnIndex("answer"));   
            };
            }
        }catch(Exception e){
            System.out.println("");;
        }finally{
            db.close();
        }        
            ImageView image3 = (ImageView) findViewById(R.id.imageChoice1);
            ImageView image4 = (ImageView) findViewById(R.id.imageChoice2);
            TextView textChoice1 = (TextView) findViewById(R.id.textChoice1);
            textChoice1.setText(choice1);
            TextView textChoice2 = (TextView) findViewById(R.id.textChoice2);
            textChoice2.setText(choice2);
            image3.setOnClickListener(this);
            image4.setOnClickListener(this);
    }

    public void onClick(View v){

        switch(v.getId()){
        case R.id.imageChoice1:
            if(answer.equals(choice1)){
                Correct++;
                Toast.makeText(this, "Correct", Toast.LENGTH_SHORT ).show();
            }else{
                Toast.makeText(this, "Incorrect", Toast.LENGTH_SHORT ).show();
            }
            break;
        case R.id.imageChoice2:
            if(choice2.equals(answer)){
                Correct++;
                Toast.makeText(this, "Correct", Toast.LENGTH_SHORT ).show();
            }else{
                Toast.makeText(this, "Incorrect", Toast.LENGTH_SHORT ).show();
            }
            break;
         }

        if(Cnt==5){
            Intent intent = new Intent(this, ResultActivity.class);
            intent.putExtra("Correct", Correct );
            startActivity(intent);
        }else{
            Cnt++;
            onQuestion();
        }
    }

}

score 1 · Accepted Answer · answered Aug 31 '14 at 15:54

The rawQuery call returns a cursor with five questions, but the following loop overwrites the variables (fieldinfo etc.) in each loop iteration, so you end up with only the values of the last of the five rows. (And the first row is skipped because you are calling moveToNext after you have moved to the first row.)

The query is not guaranteed to return the same five rows whenever onQuestion is called, so you end up with some independently random row each time.

You have to read all five questions at the beginning (into an array or something like that) and then show them one by one without querying the database again.

Thank you for your advice. I understand calling onQustion overwrites the already picked rows each time. I'm trying to fix it by myself, but could you give me some example?? I have little experience in programming. — yd0g.sta0203, Sep 02 '14 at 12:48

Selecting rows from sqlite database sometimes shows a same row

1 Answers1