How can I sort by first and last name in Android SQLite Database?

Question

I'm having trouble sorting the displays I have in my local database. I want to sort by first and last name, as well as select displays by school name and interest. I tried a query in one of my methods but it didn't work. Here's what I have:

public class StudentRecruit {
public static final String MYDATABASE_NAME = "MY_DATABASE";
public static final String MYDATABASE_TABLE = "MY_TABLE";
public static final int MYDATABASE_VERSION = 1;
public static final String KEY_CONTENT = "Content";
public static final String lastName = "lastName";
public static final String firstName = "firstName";
public static final String school = "school";
public static final String email = "email";
public static final String intrest = "intrest";

private SQLiteHelper sqLiteHelper;
private SQLiteDatabase sqLiteDatabase;
private static final String SCRIPT_CREATE_DATABASE =
"create table " + MYDATABASE_TABLE + " ("+firstName +" text not null, "+lastName+" text not null, "+school+" text not null, "+email+" text not null, "+intrest+" text not null, );";




private Context context;

public StudentRecruit(Context c){
context = c;
}

public StudentRecruit openToRead() throws android.database.SQLException {
sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
sqLiteDatabase = sqLiteHelper.getReadableDatabase();
return this;
}

public StudentRecruit openToWrite() throws android.database.SQLException {
sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
sqLiteDatabase = sqLiteHelper.getWritableDatabase();
return this;
}

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

public double insert(String content, String content2, String content3,String content4,String content5){

ContentValues contentValues = new ContentValues();

contentValues.put(firstName, content);
contentValues.put(lastName, content2);
contentValues.put(school, content3);
contentValues.put(email, content4);
contentValues.put(intrest, content5);


return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues);

}

public int deleteAll(){
return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null);
}

/* public Cursor searchAll() {
return sqLiteDatabase.query(MYDATABASE_TABLE, null, intrest, null, lastName, null, school);
}*/


public String queueAll(){
String[] columns = new String[]{firstName};
Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns,
null, null, null, null, null);
String result = "";

int index_CONTENT = cursor.getColumnIndex(firstName);
for(cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()){
result = result + cursor.getString(index_CONTENT) + "\n";

}


return result;

}

public class SQLiteHelper extends SQLiteOpenHelper {

public SQLiteHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {

// TODO Auto-generated method stub
db.execSQL(SCRIPT_CREATE_DATABASE);
Cursor d = sqLiteDatabase.rawQuery("SELECT * from " + MYDATABASE_TABLE + " ORDER BY " + lastName + " ASC" , null); // here I have edited space before ORDER BY word starts. Please note this
Log.d("query",">>>>"+ "SELECT * from " + MYDATABASE_TABLE + " ORDER BY " + lastName + " ASC"); // check this query is going to right way or not using local database
if(d != null){
if(d.getCount() > 0){ // to check you get one or more data
d.moveToFirst();
do{
int sort = d.getColumnIndex(lastName);
int sort2 = d.getColumnIndex(firstName);
String lastName = d.getString(sort);
String firstName = d.getString(sort2);
//System.out.println("GOT STUDENT " + lastName + " LIKES " + intrest);
} while (d.moveToNext());
}
}


}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub


}



}
}

Answer 1

This is my suggestion:



Cursor d = sqLiteDatabase.rawQuery("SELECT * from " + MYDATABASE_TABLE + " ORDER BY lastName ASC, firstName ASC" , null);

Answer 2

Here are a few methods I use. 1. buy a baby names book. I recommend Bruce Lansky's books. There are meanings, origins, and lists grouping by type and how they sound to people. 2. Flip through a dictionary. Random words may pop out and sound right. 3. Think of words that describe your character, and play with the spelling. Change like-sounding letters (b or f for v, y for j, z for s, etc), spell the word backwards and then re-spell it how it sounds, add two words and drop bits from the beginning middle or end, or play with pronounciation and re-spell. Sometimes it works sometimes it doesn't (example: take funny. spelled backwards is Ynnuf. Re-spell to Yanhoff. Start replacing letters you get Janhoff. Chanev. Chaney. See how one word can evolve into four acceptable last names) 4. Look at lists of names on the backs of DVD cases, bibliography pages in textbooks, sports card list-books, anywhere with a long list of names. Sound your first name out against some of the last names and they might fit.

Answer 3

Sqlite Order By