I am trying to create a database function that will help me match my student id. table_userinfo has already student id, name and course values stored in it. table_users is empty which accept email, student id and password from user. I want to create a function where only the student id stored in table_userinfo can register, otherwise show an error that student doesn't exists. The student id inputted by the user should also retrieve the corresponding course and name from table_userinfo. Also is my code for inserting values right or not. Because i tried it before and i got a run error in the first insert value line.
//Code for table creation
public static final String SQL_TABLE_USERS = " CREATE TABLE " +
TABLE_USERS
+ " ( "
+ KEY_ID + " INTEGER PRIMARY KEY, "
+ KEY_EMAIL + " TEXT, "
+ KEY_SID + " INTEGER, "
+ KEY_PASSWORD + " TEXT"
+ " ) ";
//SQL for creating user info table
public static final String SQL_TABLE_USERINFO = " CREATE TABLE "
+ TABLE_USERINFO
+ " ( "
+ KEY_ID + "INTEGER PRIMARY KEY, "
+ KEY_SID + " INTEGER, "
+ KEY_NAME + "TEXT, "
+ KEY_COURSE + " TEXT "
+ " ) ";
public SqliteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//Create Table when oncreate gets called
sqLiteDatabase.execSQL(SQL_TABLE_USERS);
sqLiteDatabase.execSQL(SQL_TABLE_USERINFO);
sqLiteDatabase.execSQL("INSERT INTO TABLE_USERINFO
VALUES('01','45207160010','Mary James','TYBSCIT')");
sqLiteDatabase.execSQL("INSERT INTO TABLE_USERINFO
VALUES('02','45207160020','Amelia John','FYBCOM')");
UPDATE:- I have now two functions for checking if email exists and if student exists in my sqlite code. But when i call the functions from the register page, the app crashes and the log says that the error is at the function when the register button is clicked. I am sharing the code of my functions and registration page. Please tell me if i am making some mistake. I have already tried different combinations of for loops o my registration page. Doesn't work. However if put only the Email exists function, it works then.
SQLITE code
public boolean ifUserInCourse(String sid) {
boolean rv = false;
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.getCount() > 0) rv = true;
csr.close();
return rv;
}
public boolean isEmailExists(String email) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_USERS,// Selecting Table
new String[]{KEY_ID, KEY_EMAIL, KEY_SID, KEY_PASSWORD},//Selecting columns want to query
KEY_EMAIL + "=?",
new String[]{email},//Where clause
null, null, null);
if (cursor != null && cursor.moveToFirst()&& cursor.getCount()>0) {
//if cursor has value then in user database there is user associated with this given email so return true
return true;
}
//if email does not exist return false
return false;
}
Registration code
buttonRegister.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
if (validate()) {
String Email = editTextEmail.getText().toString();
String SID = editTextSID.getText().toString();
String Password = editTextPassword.getText().toString();
progressBar.setVisibility(View.VISIBLE);
//Check in the database is there any user associated with this email
if (!sqliteHelper.ifUserInCourse(SID) && !sqliteHelper.isEmailExists(Email)) {
sqliteHelper.addUser(new User(null, Email, SID, Password));
Snackbar.make(buttonRegister, "User created successfully! Please Login ", Snackbar.LENGTH_LONG).show();
new Handler().postDelayed(new Runnable() {
@Override
public void run() {
finish();
}
}, Snackbar.LENGTH_LONG);
} else {
if(sqliteHelper.isEmailExists(Email)) {
Snackbar.make(buttonRegister, "Email already exists ", Snackbar.LENGTH_LONG).show();
} else {
Snackbar.make(buttonRegister, "User doesn't exists ", Snackbar.LENGTH_LONG).show();
}
}
progressBar.setVisibility(View.GONE);
}
}
});
Also is my code for inserting values right or not. Because i tried it before and i got a run error in the first insert value line.
Unless you have public static final String TABLE_USERINFO = "TABLE_USERINFO"; which considering the near duplicate of the is question, you don't then
sqLiteDatabase.execSQL("INSERT INTO TABLE_USERINFO VALUES('01','45207160010','Mary James','TYBSCIT')");
sqLiteDatabase.execSQL("INSERT INTO TABLE_USERINFO VALUES('02','45207160020','Amelia John','FYBCOM')");
Will result in an exception due to table not found.
I believe that you instead should be using :-
sqLiteDatabase.execSQL("INSERT INTO " + TABLE_USERINFO + " VALUES('01','45207160010','Mary James','TYBSCIT')");
sqLiteDatabase.execSQL("INSERT INTO " + TABLE_USERINFO + " VALUES('02','45207160020','Amelia John','FYBCOM')");
Additionally you will encounter issues as you have + KEY_NAME + "TEXT, " instead of + KEY_NAME + " TEXT, " (space omitted).
I want to create a function where only the student id stored in table_userinfo can register, otherwise show an error that student doesn't exists. The student id inputted by the user should also retrieve the corresponding course and name from table_userinfo.
The following SqliteHelper.java would facilitate the above (taking into consideration your response to the answer provided previously for a similar question)
public class SqliteHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "mydb";
public static final int DATABASE_VERSION = 1;
public static final String TABLE_USERS = "user_reg";
public static final String TABLE_USERINFO = "user_info";
public static final String KEY_ID = BaseColumns._ID;
public static final String KEY_EMAIL = "email";
public static final String KEY_SID = "sid";
public static final String KEY_PASSWORD = "password";
public static final String KEY_NAME = "name";
public static final String KEY_COURSE = "coures";
public static final String SQL_TABLE_USERS = " CREATE TABLE " +
TABLE_USERS
+ " ( "
+ KEY_ID + " INTEGER PRIMARY KEY, "
+ KEY_EMAIL + " TEXT, "
+ KEY_SID + " INTEGER, "
+ KEY_PASSWORD + " TEXT"
+ " ) ";
//SQL for creating user info table
public static final String SQL_TABLE_USERINFO = " CREATE TABLE "
+ TABLE_USERINFO
+ " ( "
+ KEY_ID + "INTEGER PRIMARY KEY, "
+ KEY_SID + " INTEGER, "
+ KEY_NAME + " TEXT, "
+ KEY_COURSE + " TEXT "
+ " ) ";
public SqliteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//Create Table when oncreate gets called
sqLiteDatabase.execSQL(SQL_TABLE_USERS);
sqLiteDatabase.execSQL(SQL_TABLE_USERINFO);
sqLiteDatabase.execSQL("INSERT INTO " + TABLE_USERINFO + " VALUES('01','45207160010','Mary James','TYBSCIT')");
sqLiteDatabase.execSQL("INSERT INTO " + TABLE_USERINFO + " VALUES('02','45207160020','Amelia John','FYBCOM')");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public long registerUser(String sid, String email, String password) {
if (email.length() < 1) return -9999;
if (password.length() < 1) return -9998;
if (sid.length() < 1) return -9997;
if (ifUserRegistered(sid)) return -99; // Check if user is already registered
if (!ifUserInCourse(sid)) return -999; // Check if user is enrolled in a course perhpas optional
ContentValues cv = new ContentValues();
cv.put(KEY_SID,sid);
cv.put(KEY_EMAIL,email);
cv.put(KEY_PASSWORD,password);
SQLiteDatabase db = this.getWritableDatabase();
return db.insert(TABLE_USERS,null,cv);
}
public boolean ifUserRegistered(String sid) {
boolean rv = false;
String whereclause = KEY_SID + "=?";
String[] whereargs = new String[]{sid};
SQLiteDatabase db = this.getWritableDatabase();
Cursor csr = db.query(TABLE_USERS,null,whereclause,whereargs,null,null,null);
if (csr.getCount() > 0) rv = true;
csr.close();
return rv;
}
public boolean ifUserInCourse(String sid) {
boolean rv = false;
String whereclause = KEY_SID + "=?";
String[] whereargs = new String[]{sid};
SQLiteDatabase db = this.getWritableDatabase();
Cursor csr = db.query(TABLE_USERINFO,null,whereclause,whereargs,null,null,null);
if (csr.getCount() > 0) rv = true;
csr.close();
return rv;
}
public String getUserNameFromSID(String sid) {
String rv = "";
String whereclause = KEY_SID + "=?";
String[] whereargs = new String[]{sid};
SQLiteDatabase db = this.getWritableDatabase();
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;
}
public String getUserCourseFromSID(String sid) {
String rv = "";
String whereclause = KEY_SID + "=?";
String[] whereargs = new String[]{sid};
SQLiteDatabase db = this.getWritableDatabase();
Cursor csr = db.query(TABLE_USERINFO,null,whereclause,whereargs,null,null,null);
if (csr.moveToFirst()) {
rv = csr.getString(csr.getColumnIndex(KEY_COURSE));
}
csr.close();
return rv;
}
}
The above was tested using the following code in an activity :-
public class MainActivity extends AppCompatActivity {
SqliteHelper mSQLHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mSQLHlpr = new SqliteHelper(this);
long result1 = mSQLHlpr.registerUser("010101010101010101","[email protected]","1234567890");
long result2 = mSQLHlpr.registerUser("45207160010","[email protected]","1234567890");
long result3 = mSQLHlpr.registerUser("45207160010","[email protected]","1234567890");
Log.d(
"RESULTS",
"\tFirst attempt returned " + String.valueOf(result1) + " " + String.valueOf((result1 > 0)) +
"\n\tSecond attempt returned " + String.valueOf(result2) + " " + String.valueOf(result2 > 0) +
"\n\tThird attempt returned " + String.valueOf(result3) + " " + String.valueOf(result3 > 0)
);
String testsid = "45207160010";
Log.d(
"USERBYSID",
"SID " + testsid + " " +
"Has a name of " + mSQLHlpr.getUserNameFromSID(testsid) +
" and a Course of " + mSQLHlpr.getUserNameFromSID(testsid)
);
}
}
The above produced the following relevant output :-
2019-02-05 12:38:08.684 21533-21533/? D/RESULTS: First attempt returned -999 false
Second attempt returned 1 true
Third attempt returned -99 false
2019-02-05 12:38:08.686 21533-21533/? D/USERBYSID: SID 45207160010 Has a name of Mary James and a Course of Mary James