Search code examples
androidsqlitesql-like

SQLite get ids and names WHERE name LIKE

id|  name   |...
--------------
1 |Emmi blaa|..

2 |Emmi haa |..

3 |Emmi naa |..

I have SQLite database with table named contacts that contain id, name and other information. I'm trying to get name and id with name variable that I give in EditText.

String query = "SELECT name, id FROM contacts WHERE name LIKE \"%" + name + "%\"";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursorC = db.rawQuery(query, null);
while (cursorC.moveToNext()) {
   System.out.println(cursorC.getString(0));
}

With the code above, I'm only able to get the names, but not the id so I tried GROUP_CONCAT

String query = "SELECT id, GROUP_CONCAT(name) FROM contacts WHERE name LIKE \"%" + name + "%\" GROUP BY id";

Now I get the ids only. How would I get both Id and name with name variable being "mm" for example?

Solution

  • I believe that your issue is not that the first query was not getting the id, rather that you weren't retrieving the id column from the cursor.

    The following would work :-

    String query = "SELECT name, id FROM contacts WHERE name LIKE \"%" + name + "%\"";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursorC = db.rawQuery(query, null);
while (cursorC.moveToNext()) {
   System.out.println(cursorC.getString(0) + " : " + cursorC.getString(1));
}

    However, ideally you should use the Cursor getLong method for retrieving id's as the id can be as large as a 64bit signed integer. So System.out.println(cursorC.getString(0) + " : " + String.valueOf(cursorC.getLong(1))); would be better.

    Additionally an improvement would be to use the Cursor's getColumnIndex(the_column_name) method. This is more flexible as the index of the column is determined according to the column's name. As such System.out.println(cursorC.getString(cursorC.getColumnIndex("name")) + " : " + String.valueOf(cursorC.getLong(cursorC.getColumnIndex("id")))); would be recommended (it is also recommended that table and column names are defined as constants and then that those constants are used rather than hard coding the column/table names).

    • e.g. if the query were changed to SELECT id, name FROM contacts WHERE name LIKE \"%" + name + "%\"" then using hard-coded offsets 0 and 1 would transpose the results. However the results would be unchanged if using getColumnIndex.

    • If you wanted to use the 2nd query String query = "SELECT id, GROUP_CONCAT(name) FROM contacts WHERE name LIKE \"%" + name + "%\" GROUP BY id"; then note that the column names in the Cursor are id and GROUP_CONCAT(name), generally an alias would be given to the name using the AS keyword. e.g. String query = "SELECT id, GROUP_CONCAT(name) AS all_names FROM contacts WHERE name LIKE \"%" + name + "%\" GROUP BY id"; The column name in the resultant cursor would then be all_names.