CODEWITHSUNDEEP
androiddatabasesqlite
Mr. Pivirotto
Mr. Pivirotto

Reputation: 281

How to return multiple entries with same value from SQLite

My database

This is what my database looks like, how would I return all of the rows that have the same string in the KEY_ALCOHOL column? After I get all of the rows, I need to randomly pick one and then display it.

I tried this: DATABASE HELPER .JAVA

public Cursor getAlcohol(String alcohol) throws SQLException 
{
    Cursor mCursor =
            myDataBase.query(true, DB_TABLE, new String[] {
                    KEY_ROWID,
                    KEY_ALCOHOL, 
                    KEY_TYPE,
                    KEY_BRAND,
                    KEY_PRICE
                    }, 
                    KEY_ALCOHOL + "=" + alcohol, 
                    null,
                    null, 
                    null, 
                    null, 
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

MAIN ACTIVITY .JAVA

myDbHelper.openDataBase();
    Cursor c = myDbHelper.getAlcohol("Liquor");
    if (c.moveToFirst())
    {
        do {          
            DisplayTitle(c);
        } while (c.moveToNext());
    }
    myDbHelper.close();

}

public void DisplayTitle(Cursor c)
{
    Toast.makeText(this, 
            "id: " + c.getString(0) + "\n" +
            "ALCOHOL: " + c.getString(1) + "\n" +
            "TYPE: " + c.getString(2) + "\n" +
            "BRAND:  " + c.getString(3) + "\n" +
            "PRICE:  " + c.getString(4),
            Toast.LENGTH_LONG).show();        
}

I was testing to see if this would simply return all of the rows with "Liquor" in the KEY_ALCOHOL column but instead it gave me a null pointer.

EDIT

Got it working!! Here is what i came up with and it works!! Let me know if anything is wrong or you see a better way of doing it! Thanks everyone!

myDbHelper.openDataBase();
     Cursor Test = myDbHelper.getAlcohol("Liquor");
     int test7 = Test.getCount();
     test9 = r.nextInt(test7);
     Cursor c = myDbHelper.getTitle(test9);
     if (c.moveToFirst())        
         DisplayTitle(c);
     else
         Toast.makeText(this, "No title found", 
                Toast.LENGTH_LONG).show();
    myDbHelper.close();

Upvotes: 0

Views: 899

Answers (1)

Bojan Kogoj
Bojan Kogoj

Reputation: 5649

I hope this works

public Cursor getAlcohol(String alcohol) throws SQLException 
{
    Cursor mCursor =
            myDataBase.query(true, DB_TABLE, new String[] {
                    KEY_ROWID,
                    KEY_ALCOHOL, 
                    KEY_TYPE,
                    KEY_BRAND,
                    KEY_PRICE
                    }, 
                    KEY_ALCOHOL + "=?", 
                    new String[] { alcohol },
                    null, 
                    null, 
                    null, 
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

Look at structure

 Cursor SQLiteDatabase.query(String table, 
 String[] columns,
 String selection,
 String[] selectionArgs,
 String groupBy,
 String having,
 String orderBy,
 String limit)

Your 'selection' was wrong. It resulted in WHERE KEY_ALCOHOL + "=" + alcohol = null since argument is separate.

Upvotes: 1

Related Questions