CODEWITHSUNDEEP
androidsql
運到 等
運到 等

Reputation: 151

Android: SQLite, How to fetch from multiple field?

Based on this tutorial, there is a function called "fetchCountriesByName" to filer the listview content by comparing "KEY_NAME" with inputted country name. Only matching "KEY_NAME" record will be displayed.

http://www.mysamplecode.com/2012/07/android-listview-cursoradapter-sqlite.html

public Cursor fetchCountriesByName(String inputText) throws SQLException {
  Log.w(TAG, inputText);
  Cursor mCursor = null;
  if (inputText == null  ||  inputText.length () == 0)  {
    mCursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID,
    KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, null, null, null, null, null);

}
else {
 mCursor = mDb.query(true, SQLITE_TABLE, new String[] {KEY_ROWID,
   KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, 
   KEY_NAME + " like '%" + inputText + "%'", null,
   null, null, null, null);
 }
 if (mCursor != null) {
  mCursor.moveToFirst();
 }
 return mCursor;

}

My question is how to fetch from multiple field which similar to inputted text? I want the inputted text not just compare with KEY_NAME, but also compare with KEY_REGION. I had added the "KEY_REGION" in the bold area, but it doesn't work. How can I fetch KEY_REGION & KEY_NAME?

 public Cursor fetchCountriesByName(String inputText) throws SQLException {
 Log.w(TAG, inputText);
 Cursor mCursor = null;
 if (inputText == null  ||  inputText.length () == 0)  {
  mCursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID,
  KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, 
  null, null, null, null, null);

 }
 else {
  mCursor = mDb.query(true, SQLITE_TABLE, new String[] {KEY_ROWID,
    KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, 
    KEY_NAME+           KEY_REGION        + " like '%" + inputText + "%'", null,
    null, null, null, null);
 }
 if (mCursor != null) {
  mCursor.moveToFirst();
 }
 return mCursor;

}

Upvotes: 1

Views: 144

Answers (2)

Jargo
Jargo

Reputation: 86

Try this: 

  mCursor = mDb.query(true, SQLITE_TABLE, new String[] {KEY_ROWID,
    KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, 
    KEY_NAME+" like '%?%' OR "+KEY_REGION+" like '%?%'", new String[]{inputText, inputText},
    null, null, null, null);

The bound variables (?) will protect you from SQL injection attacks. You will also want to consider changing KEY_ROWID to _ID, as this is what ListView expects.

Upvotes: 2

Rajesh
Rajesh

Reputation: 15774

Use the boolean logical operators AND or OR.

mCursor = mDb.query(true, SQLITE_TABLE, new String[] {KEY_ROWID,
KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, 
KEY_NAME + " like '%" + inputText + "%' AND "+ KEY_REGION + " like '%" + inputText + "%'", null,
null, null, null, null);

Upvotes: 2

Related Questions