android searching with multiple keywords

Question

My application contain search function and it will search content inside the database. The weakness of my searching is, I can make it search using one tag only for example I only can search "cat" and it will return content in my database that contain the word 'cat' because I'm using LIKE query during select statement. How can i make my search using many tags? for example "cat with yellow ball". So that, it will return result that related to the string inserted and it will ignore if some of tags are not exist in database. To make it more clear, I had created a sample table AboutCat in my database. It contains 3 column which is id, c_question and c_keyword.

id = 1

c_question = What happen if we pull the cat's mustache?

c_keyword = cat mustache pull

If i search by using any of the keywords in c_keyword column for example "cat" or "cat mustache" or "mustache pull", it will display c_question. But if I search "cat pull" or "mustache cat", it would not display. So, how can I search it by ignoring the order of words as long as it is match and exist inside the c_keyword column?. Below is my code...ask me if my explanation was not clear..

btnWanita.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {

            results.clear();

            if(txtWnta.getText().toString().matches(""))
            {
                Toast.makeText(WanitaActivity.this, "Sila masukkan kata kunci", Toast.LENGTH_SHORT).show();

            }

            else
            {
            OpenHelper helper = new OpenHelper(getApplicationContext());
            database = helper.getWritableDatabase();

            try{                    

                String sql = "select * from " +
                            OpenHelper.DB_TABLE_WANITA + " where " + OpenHelper.KEY_WANITA +
                            " like " + "'%" + txtWnta.getText().toString() + "%';";
                Cursor c = database.rawQuery(sql, null);

                if(c!= null)
                {
                    c.moveToFirst();
                    int soalanColumn = c.getColumnIndex(OpenHelper.MASALAH_WANITA);
                    int getId = c.getColumnIndex(OpenHelper.ID_WANITA);
                    if(c.isFirst())
                    {
                        do
                        {
                            idList.add(c.getLong(getId));
                            results.add(c.getString(soalanColumn));


                        }while(c.moveToNext());
                    }

                    adapter.updateList(results);
                }

            }

                catch(SQLException e)
                {
                    Log.v("caer ", e.toString());   
                }
            }

My searching goes here...

String sql = "select * from " +
                            OpenHelper.DB_TABLE_WANITA + " where " + OpenHelper.KEY_WANITA +
                            " like " + "'%" + txtWnta.getText().toString() + "%';";

Answer 1

You need to split your string into separate words and then dynamically build WHERE clause for matching each word. Something like this:

String sql = 
"select * from " 
+ OpenHelper.DB_TABLE_WANITA 
+ " where " 
+ OpenHelper.KEY_WANITA + " like " + "'%cat%'" 
+ " or "
+ OpenHelper.KEY_WANITA + " like " + "'%mustache%'"
+ " or "
+ OpenHelper.KEY_WANITA + " like " + "'%pull%'"
+";"
;