CODEWITHSUNDEEP
javaandroidsqlitemobile
Kaitlin Reyes
Kaitlin Reyes

Reputation: 71

SQLite Database query for multiple table

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.

Upvotes: 1

Views: 647

Answers (1)

CL.
CL.

Reputation: 180020

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);
    ...
}

Upvotes: 2

Related Questions