SQLite Database query for multiple table

Question

I am creating an android app quiz with different topics. So i have buttons for the topics and different TABLEs in my database for Quiz1, Quiz2, Quiz3 and so on...

Here's my code for Quiz.java

public class Quiz extends Activity implements OnClickListener{


    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.quiz);

        Button quiz1 = (Button) findViewById(R.id.quiz1Btn);
        quiz1.setOnClickListener(this);
        Button quiz2 = (Button) findViewById(R.id.quiz2Btn);
        quiz2.setOnClickListener(this);

    }



    @Override
    public void onClick(View v) {
        Intent i;

        switch (v.getId()){

        case R.id.quiz1Btn :

            //Get Question set
            List<Question> questions = getQuestionSetFromDb();

            //Initialize quiz with retrieved question set ///
            CurrentQuiz c = new CurrentQuiz();
            c.setQuestions(questions);
            c.setNumRounds(getNumQuestions());
            ((MLearningApp)getApplication()).setCurrentGame(c);  


            i = new Intent(this, QuestionActivity.class);
            startActivity(i);
            break;


        }

    }


    // Method that retrieves a random set of questions from db
    private List<Question> getQuestionSetFromDb() throws Error {

        int numQuestions = getNumQuestions();
        DBHelper myDbHelper = new DBHelper(this);
        try {
            myDbHelper.createDataBase();
        } catch (IOException ioe) {
            throw new Error("Unable to create database");
        }
        try {
            myDbHelper.openDataBase();
        }catch(SQLException sqle){
            throw sqle;
        }
        List<Question> questions = myDbHelper.getQuestionSet(numQuestions);
        myDbHelper.close();
        return questions;
    }


    // Method to return the number of questions for the game
    private int getNumQuestions() {
        int numRounds = 10;
        return numRounds;
    }

And here's my query in DBHelper.java

    public List<Question> getQuestionSet(int numQ){
    List<Question> questionSet = new ArrayList<Question>();
    Cursor c = myDataBase.rawQuery("SELECT * FROM Quiz1" + " ORDER BY RANDOM() LIMIT " + numQ, null);
    while (c.moveToNext()){
        //Log.d("QUESTION", "Question Found in DB: " + c.getString(1));
        Question q = new Question();
        q.setQuestion(c.getString(1));
        q.setAnswer(c.getString(2));
        q.setOption1(c.getString(3));
        q.setOption2(c.getString(4));
        questionSet.add(q);
    }
    return questionSet;
}

My question is, how would i select Quiz2 TABLE in my database for Quiz2 button; and same as Quiz3 TABLE in db for Quiz3 button and so on.

So far, my android app is working for Quiz1 only since it only retrieves one table from my database which is for the Quiz1.

Why are you using a separate table for each quiz? You should have a single table with a quiz number column. — CL.
– CL., Commented Aug 8, 2014 at 16:30
Noted. I will add question number on my table instead of having different tables. But if that's the case, what should i do with my SELECT QUERY statement? — Kaitlin Reyes
– Kaitlin Reyes, Commented Aug 8, 2014 at 16:35

CL. · Accepted Answer · 2014-08-08 16:52:09Z

2

You have to change the table name to Quiz2, etc. This requires that the function needs to know the quiz number:

public List<Question> getQuestionSet(int quizNr, int numQ) {
    ...
    rawQuery("SELECT * FROM Quiz" + quizNr + " ORDER BY random() LIMIT " + numQ, null);
    ...
}

If you had a single table, you would have to filter on the corresponding column:

public List<Question> getQuestionSet(int quizNr, int numQ) {
    ...
    rawQuery("SELECT * FROM Quiz WHERE QuizNr = " + quizNr + " ORDER BY random() LIMIT " + numQ, null);
    ...
}

answered Aug 8, 2014 at 16:52

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kaitlin Reyes Over a year ago

Already used a single table with question number column. What should i put now on my Quiz.java - getQuizNumber method to retrieve appropriate questions to the Quiz? Thinking of a looping statement, but i dont know how would i do it

CL. Over a year ago

There is no getQuizNumber function in the code you've shown. To ask a new question, use the "Ask Question" button.

Axel Over a year ago

Thanks this worked for me. @CL. please do you think you could give me your opinion on this question stackoverflow.com/questions/25598696/…

Collectives™ on Stack Overflow

SQLite Database query for multiple table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related