Retrieving data from multiple specific columns from a table SQLite

Question

I'm trying to retrieve data from multiple specific columns of a table in SQLite but with the code below, I'm not sure why but instead of getting data from two different columns it just gets the data from the very first column which I put in my method, which is the "budget" column of the "spendings" table :-

        String temp_category = "budget";
        Cursor latestamount = myDb.getLatestAmount(id, temp_category);
        if (latestamount.getCount() == 0) {
            Toast.makeText(AddExpenses.this, "No amount found !", Toast.LENGTH_LONG).show();
        } else {
            latestamount.moveToFirst();
            amountfromdb_budget = latestamount.getDouble(0);
        }

        temp_category = "foodndrinks";
        myDb.getLatestAmount(id, temp_category);
        if (latestamount.getCount() == 0) {
            Toast.makeText(AddExpenses.this, "No amount found !", Toast.LENGTH_LONG).show();
        } else {
            latestamount.moveToFirst();
            amountfromdb_foodndrinks = latestamount.getDouble(0);
        }

Using the code above, both my "amountfromdb_budget" and "amountfromdb_foodndrinks" will be inserted with the value in the column "budget" of my SQLite. My goal is to retrieve the value in the "budget" column into "amountfromdb_budget" and value in "foodndrinks" column into "amountfromdb_foodndrinks".

Below is my "getLatestAmount" method in my DatabaseHelper class :-

    public Cursor getLatestAmount(String id, String category) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor latestamount = db.rawQuery("SELECT "+category+" FROM spendings WHERE id =" +id , null);
        return latestamount;
    }

And the structure of my "spendings" table is below :-

    private static final String SPENDING_TABLE = "spendings";
    private static final String ID_SPENDING = "id";
    private static final String BUDGET = "budget";
    private static final String TOTAL_EXPENSES = "total_expenses";
    private static final String FOODNDRINKS = "foodndrinks";
    private static final String TRAVEL = "travel";
    private static final String SHOPPING = "shopping";
    private static final String ENTERTAINMENT = "entertainment";
    private static final String OTHERS = "others";
    private static final String BALANCE = "balance";

So if i have understood correctly. It gets the budget column data twice — Mohammad C
– Mohammad C, Commented Oct 25, 2018 at 11:36
Yes it gets the budget column data twice but I want to get budget column data and put it into “amountfromdb_budget and the second time I called “myDb.getLatestAmount(id, temp_category)” where I have changed the “temp_category” to “foodndrinks” to get the amount from “foodndrinks” column and insert that value into “amountfromdb_foodndrinks” but have no idea how to or the right way to do it — shz1998
– shz1998, Commented Oct 25, 2018 at 11:39
How to i overwrite the cursor so I can get the amount for food n drinks after getting for budget? — shz1998
– shz1998, Commented Oct 25, 2018 at 11:40
You need to save the result from the method to variable latestamount — Mohammad C
– Mohammad C, Commented Oct 25, 2018 at 11:41

Mohammad C · Accepted Answer · 2018-10-25 11:59:14Z

1

You did not overwrite the cursor from when you got the amount for food n drinks. So you are still working with the budgets cursor. So save the result from getLatestAmount to latestamount.

change

myDb.getLatestAmount(id, temp_category);

to

latestamount = myDb.getLatestAmount(id, temp_category);

So your code should look like this

String temp_category = "budget";
Cursor latestamount = myDb.getLatestAmount(id, temp_category); 
if (latestamount.getCount() == 0) { 
    Toast.makeText(AddExpenses.this, "No amount found !", Toast.LENGTH_LONG).show(); 
} else {
    latestamount.moveToFirst();
    amountfromdb_budget = latestamount.getDouble(0);
} 

temp_category = "foodndrinks";
// the line below is what you needed to change
latestamount = myDb.getLatestAmount(id, temp_category); 
if (latestamount.getCount() == 0) { 
    Toast.makeText(AddExpenses.this, "No amount found !", Toast.LENGTH_LONG).show(); 
} else { 
    latestamount.moveToFirst();
    amountfromdb_foodndrinks = latestamount.getDouble(0);
}

edited Oct 25, 2018 at 11:59

answered Oct 25, 2018 at 11:38

Mohammad C

1,3411 gold badge8 silver badges12 bronze badges

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

7 Comments

shz1998 Over a year ago

How do i overwrite the cursor so I can get the amount for foodndrinks after getting it for budget?

Mohammad C Over a year ago

You call the method and save the result to variable latestamount. Like i have shown above.

shz1998 Over a year ago

So after getting it for budget. For foodndrinks I use this instead? "amountfromdb_foondrinks = myDb.getLatestAmount(id, temp_category);"?

Mohammad C Over a year ago

No. You see the line below myDb.getLatestAmount(id, temp_category); change it to this line here latestamount = myDb.getLatestAmount(id, temp_category); then your problem is solved.

Mohammad C Over a year ago

Change the line so that it is equal to latestamount and you can leave everything else same as what you posted in your question

|

Collectives™ on Stack Overflow

Retrieving data from multiple specific columns from a table SQLite

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related