[android help] SQLitedatabase every username has the same list

In my application's database I have 2 tables one called "PLAYERS" which holds the data of every user and "FRIENDSLIST" which is intended to be a personal "friends" list for every registered account.How the process goes in my app is that when a player (Joe) registers a Username his information goes into the "PLAYERS" table and now he could go into my FindFriends activty,search the app for his friend's Username (Bob) and then clicks the ADD button.

The friend (Bob) then gets added to the "FRIENDSLIST" table.When he goes into the PlayAFriend activity a list will load showing the "friends" that he added.All this works good until I realized that when I log into one of his friend's account (Bob) ,that friend has the same list that (Joe) has.

I know that the cursor I'm using to load up (Joe)'s friends list is the same cursor I'm using to load up (Bob)'s friends list so the question I'm asking is,

how would I make Joe and Bob have their own personal friends list?

I've tried searching on this website and googling to find any examples on how to accomplish this but I find it crazy that I'm not finding any tutorials or posts on this subject even though this type of practice is used in every popular app made.Sorry for the long post I know it's not a simple question so that's why I had to explain in detail on what the problem is.

I'm posting up my DBAdapter class to show if how I made it would accommodate this and my PlayAFriend class to show my Cursor and ListView in action.

DBAdapter class

package com.fullfrontalgames.numberfighter;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {
    static final String KEY_ROWID = "_id";

    static final String KEY_USERNAME = "USERNAME";

    static final String KEY_PASSWORD = "PASSWORD";

    static final String KEY_EMAIL = "EMAIL";

    static final String KEY_NUMBERINPUT = "NUMBERINPUT";

    static final String KEY_SCORE = "SCORE";

    static final String KEY_FRIENDS = "FRIENDS";

    static final String TAG = "DBAdapter";

    static final String DATABASE_NAME = "NFDatabase";

    static final String DATABASE_TABLE1 = "PLAYERS";

    static final String DATABASE_TABLE2 = "FRIENDSLIST";

    static final int DATABASE_VERSION = 4;

    static final String DATABASE_CREATE_TABLE1 = "create table PLAYERS ( _id integer primary key autoincrement, "
            + "USERNAME text not null unique,PASSWORD text not null,EMAIL text,NUMBERINPUT text,SCORE text);";

    static final String DATBASE_CREATE_TABLE2 = "create table FRIENDSLIST (_id integer primary key autoincrement,"
            + "FRIENDS text not null,USERNAME text ,NUMBERINPUT text,SCORE text);";

    final Context context;

    DatabaseHelper DBHelper;

    static SQLiteDatabase db;

    public DBAdapter(Context ctx) {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context) {
            // TODO Auto-generated constructor stub
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            try {
                db.execSQL(DATABASE_CREATE_TABLE1);
                db.execSQL(DATBASE_CREATE_TABLE2);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion
                    + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS PLAYERS");
            db.execSQL("DROP TABLE IF EXISTS FRIENDSLIST");
            onCreate(db);
        }

    }

    public DBAdapter open() throws SQLiteException {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        DBHelper.close();
    }

    // PLAYERS TABLE CRUD

    public long insertPlayer(String Username, String Password, String Email) {
        ContentValues initialValues = new ContentValues();
        initialValues.put("USERNAME", Username);
        initialValues.put("PASSWORD", Password);
        initialValues.put("EMAIL", Email);

        db.insert("PLAYERS", null, initialValues);

        return db.insertWithOnConflict("PLAYERS", null, initialValues,
                SQLiteDatabase.CONFLICT_IGNORE);
    }

    public boolean deletePlayer(long id) {
        Log.d(TAG, "delete is working");
        return db.delete("PLAYERS", KEY_ROWID + " = " + id, null) > 0;

    }

    public Cursor getAllPlayers() {
        return db.query(false, "Players", new String[] {
                "_id", "USERNAME", "PASSWORD", "EMAIL"
        }, null, null, null, null, null, null);

    }

    public String getData() {
        String[] columns = new String[] {
                "_id", "USERNAME", "PASSWORD"
        };
        Cursor mCursor = db.query("PLAYERS", columns, null, null, null, null, null);
        String result = "";
        int iRow = mCursor.getColumnIndex(KEY_ROWID);
        int iName = mCursor.getColumnIndex(KEY_USERNAME);

        for (mCursor.moveToFirst(); !mCursor.isAfterLast(); mCursor.moveToNext()) {
            result = mCursor.getString(iRow) + " " + mCursor.getString(iName) + "/n";
        }
        return result;
    }

    public Cursor getPlayer(int _id) throws SQLException {
        Cursor mCursor = db.query(true, "PLAYERS", new String[] {
                "_id", "USERNAME"
        }, KEY_ROWID + " =  " + "_id", null, null, null, null, null);
        while (mCursor.moveToNext()) {
            int id = mCursor.getInt(mCursor.getColumnIndex("_id"));
        }
        return mCursor;
    }

    public String getUsername(String Username) {
        Cursor mCursor = db.query("Players", new String[] {
            "USERNAME"
        }, "USERNAME = ?", new String[] {
            Username
        }, null, null, null);
        if (mCursor.moveToNext())
            return mCursor.getString(0);
        else
            return "";
    }

    public String getSinlgeEntry(String Username) {
        Cursor cursor = db.query("PLAYERS", null, " USERNAME=?", new String[] {
            Username
        }, null, null, null);
        if (cursor.getCount() < 1) // UserName Not Exist
        {
            cursor.close();
            return "NOT EXIST";
        }
        cursor.moveToFirst();
        String password = cursor.getString(cursor.getColumnIndex("PASSWORD"));
        cursor.close();
        return password;
    }

    public boolean updatePlayer(long _id, String Username, String Password, String Email) {
        ContentValues args = new ContentValues();
        args.put("USERNAME", Username);
        args.put("PASSWORD", Password);
        args.put("EMAIL", Email);
        return db.update("PLAYERS", args, KEY_ROWID + " = " + _id, null) > 0;
    }

    // FRIENDS TABLE CRUD

    public void insertFriend(String Friend) {
        ContentValues initialValues = new ContentValues();
        initialValues.put("FRIENDS", Friend);
        db.insert("FRIENDSLIST", null, initialValues);
    }

    public boolean deleteFriend() {
        return db.delete("FRIENDSLIST", KEY_ROWID + " = " + "_id", null) > 0;
    }

    public Cursor getAllFriends() {
        return db.query(false, "FRIENDSLIST", new String[] {
                "_id", "FRIENDS"
        }, null, null, null, null, null, null);
    }

    public Cursor getFriend(long rowid) throws SQLException {
        Cursor mCursor = db.query(true, "FRIENDSLIST", new String[] {
                "_id", "FRIENDS"
        }, KEY_ROWID + " =  " + "_id", null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public boolean updateFriend(long _id, String Friends) {
        ContentValues args = new ContentValues();
        args.put("FRIENDS", Friends);

        return db.update("FRIENDSLIST", args, KEY_ROWID + " = " + _id, null) > 0;
    }

}

PlayAFriend Class

package com.fullfrontalgames.numberfighter;

import android.app.ListActivity;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.TextView;

public class PlayAFriend extends ListActivity {

    DBAdapter DBAdapter;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.playafriend);

        final DBAdapter db = new DBAdapter(this);
        DBAdapter = db.open();

        getListView().setAdapter(new FriendsListAdapter(this, db.getAllFriends()));

    }

    private class FriendsListAdapter extends BaseAdapter implements OnClickListener, ListAdapter {
        private Cursor fFriends;

        private Context fContext;

        public FriendsListAdapter(Context context, Cursor friends) {
            // TODO Auto-generated constructor stub
            fContext = context;
            fFriends = friends;
        }

        @Override
        public int getCount() {
            // TODO Auto-generated method stub
            return fFriends.getCount();
        }

        @Override
        public FriendInfo getItem(int position) {
            // TODO Auto-generated method stub
            if (fFriends.moveToPosition(position)) {
                String name = fFriends.getString(fFriends.getColumnIndex("FRIENDS"));

                return new FriendInfo(name);
            }
            return null;
        }

        @Override
        public long getItemId(int position) {
            // TODO Auto-generated method stub
            fFriends.moveToPosition(position);
            return fFriends.getLong(fFriends.getColumnIndex("_id"));
        }

        @Override
        public View getView(int position, View convertView, ViewGroup parent) {
            // TODO Auto-generated method stub
            if (convertView == null) {
                convertView = LayoutInflater.from(fContext).inflate(R.layout.playafriendlistitems,
                        parent, false);
            }
            FriendInfo friendinfo = getItem(position);
            TextView friendTV = ViewHolder.get(convertView, R.id.textview_friends);
            Button playbutton = ViewHolder.get(convertView, R.id.playbutton, position);
            playbutton.setOnClickListener(this);

            friendTV.setText(friendinfo.getName());

            return convertView;
        }

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            DBAdapter db = new DBAdapter(fContext);
            db.open();
            int position = (Integer)v.getTag();
            fFriends.moveToPosition(position);
            long id = fFriends.getLong(fFriends.getColumnIndex("_id"));
            fFriends = db.getAllFriends();
            db.close();
        }

    }

}

.

stackoverflow.comm