How to fetch user details from a SQLite database having two tables?

Question

I am trying to fetch the user details in my Navigation drawer. I have two different tables, one has username and course already stored and the other has id and email of the user taken during registration. I want to display the user name, id, course and email. I do have a user class with id, email and password for registering but not name and course. How should i fetch data from this class?

The below method is for fetching the username from table having same id. I want to display this on navigation drawer. How should i call the function and display the result?

public String getUserNameFromSID(String sid) {
    String rv = "";
    String whereclause = KEY_SID + "=?";
    String[] whereargs = new String[]{sid};
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor csr = db.query(TABLE_USERINFO,null,whereclause,whereargs,null,null,null);
    if (csr.moveToFirst()) {
        rv = csr.getString(csr.getColumnIndex(KEY_NAME));
    }
    csr.close();
    return rv;

Answer 1

You can use a JOIN, (I believe from previous questions that SID is in both tables so this is what you would use as the basis for the JOIN).

So you could use something like SELECT * FROM userinfo JOIN userreg ON userinfo.sid = userreg.sid

• Note that just using the column name sid would be ambiguous and hence the need to prefix the column with the table name. As it stands you will have to sid columns in the result (this shouldn't be an issue as they will both be the same value but not specifying an alias using AS cab be problematic for columns with the same name).

To utilise the query convenience method the JOIN needs to be part of the 1st (table) parameter.

So the above would be coded as :-

public String getUserNameFromSID(String sid) {
    String rv = "";
    String joinclause = " JOIN " + TABLE_USERREG + " ON " + TABLE_USERINFO + "." + KEY_SID + " = " + TABLE_USERREG + "." + KEY_SID; //<<<<<<<<<< ADDED
    String whereclause = KEY_SID + "=?";
    String[] whereargs = new String[]{sid};
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor csr = db.query(TABLE_USERINFO + joinclause,null,whereclause,whereargs,null,null,null); //<<<<<<<<<< CHANGED
    if (csr.moveToFirst()) {
        rv = csr.getString(csr.getColumnIndex(KEY_NAME));
    }
    csr.close();
    return rv;
}

• Note the above is in-principle code as such
  • variable names and identifiers may not reflect the actual code and thus may need to be changed.
  • the code has not been run or tested and may therefore contain some minor errors.
  • The above only returns the username, you cannot return multiple distinct values, you would therefore need to return an object e.g. a User object to encompass the multiple values. You would get the values to set the object in a similar way as rv is set above.

Additional re comment :-

The thing is, that i would need to pass the Sid parameter to the function which i can only do in the register/login page. How then can i pass this value to the navigation page?

Instead of pages the term used for Android is Activities. You can pass data from one activity to another called/started/invoked activity using Intent Extras after instantiating an Intent in preparation for the call. That is if the activity is to be directly called from the parent activity (registration activity in your case).

If for example the registration activity were called from an initial activity and returns from that activity then you still use an intent to return the values, but the registration activity should be called/started/invoked using startActivityForResult. The activity starting the registration activity should override the onActivityResult method. The registration activity sets intent extras and uses setResult to indicate the result code and then intent to be returned.

There's plenty of examples on Stack overflow e.g. Sending data back to the Main Activity in Android