How to return multiple entries with same value from SQLite

Question

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();

Bojan Kogoj · Accepted Answer · 2012-09-12 21:06:46Z

1

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.

edited Sep 12, 2012 at 21:06

answered Sep 12, 2012 at 21:00

Bojan Kogoj

5,7084 gold badges39 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mr. Pivirotto Over a year ago

Now how would i go about choosing one random entry from the list that getAlcohol() retrieves?

Bojan Kogoj Over a year ago

I suppose you put it into a List. And once in a list use mylist.get(Random.nextInt(mylist.size())); That's another question already

Mr. Pivirotto Over a year ago

So put it in, for example, like a 'String [] test = myDataBase.getAlcohol("Liquor");' ?

Mr. Pivirotto Over a year ago

I tried this...

myDbHelper.openDataBase();         final Cursor Test = myDbHelper.getAlcohol("Liquor");         final Cursor chosen = Test.get(Random.nextInt(Test.size()));         myDbHelper.close();

But it says "size()" is not defined for "Test", any ideas?

Bojan Kogoj Over a year ago

Iterate through cursor (Test) and put it in a List. Then once in a list use random. I can't show you how to here

|

Collectives™ on Stack Overflow

How to return multiple entries with same value from SQLite

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related