xyz rety xyz rety - 5 months ago 20
SQL Question

How can I add a WHERE clause to a SELECT query?

How can I write a SQL query to select a row using an integer in the WHERE clause?

Below is the piece of code where I want to use the query, but this is not working (no results are returned).

public Cursor forEditPurpose(int pos){

db=this.getReadableDatabase();
Cursor res = db.rawQuery("SELECT name,phone FROM mecontact1 where _id = " + pos + "", null);

return res;

}


This is my entire database code where I perform all operations which I have required:

public class ContactDatabase extends SQLiteOpenHelper {
SQLiteDatabase db;
public static final String DATABASE_NAME="totalContact1.db";
public static final String TABLE_NAME="mecontact1";
public static final String NAME="name";
public static final String PHONE="phone";
public static final String UID="_id";


public ContactDatabase(Context context) {
super(context, DATABASE_NAME, null, 1);

}

@Override
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL("create table mecontact1" +
"(_id integer primary key , name text, phone text)");
}catch(android.database.SQLException e){
System.out.println("table create nhi ho rha");
}
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS mecontact1");
onCreate(db);
}

public void insertContact(String nam,String mob,int autocrement){

db=this.getWritableDatabase();
ContentValues contentValues=new ContentValues();

contentValues.put(NAME,nam);
contentValues.put(PHONE,mob);
contentValues.put(UID,autocrement);

db.insert(TABLE_NAME, null, contentValues);
db.close();
}

public Cursor showData(){

db=this.getReadableDatabase();
Cursor res = db.rawQuery("SELECT * FROM mecontact1", null);
return res;

}

public Cursor nameData(String dataName){

db=this.getReadableDatabase();
Cursor res = db.rawQuery("SELECT * FROM mecontact1 WHERE name = '"+dataName+"'", null);
return res;

}

public Cursor phoneData(String dataNumber){

db=this.getReadableDatabase();
Cursor res = db.rawQuery("SELECT * FROM mecontact1 WHERE phone = '"+dataNumber+"'", null);
return res;

}

public Cursor phoneName(){

db=this.getReadableDatabase();
Cursor res = db.rawQuery("SELECT * FROM mecontact1 ", null);
return res;

}

public void deleteContact(String d,int pos){
db=this.getWritableDatabase();

db.execSQL("DELETE FROM "+ TABLE_NAME + " WHERE " + UID + " = " + pos +"");
db.execSQL("UPDATE " + TABLE_NAME + " set " + UID + " = (" + UID + "-1) where " + UID + " > " + String.valueOf(pos));

}

public Cursor forEditPurpose(int pos){

db=this.getReadableDatabase();
Cursor res = db.rawQuery("SELECT name,phone FROM mecontact1 where _id = " + pos + "", null);

return res;

}
}

Answer

I'm assuming you want to return cursor of query result this is full example but in your code you are probably missing cursor.moveToFirst();

public Cursor getById(int _id){
    SQLiteDatabase db = this.getReadableDatabase();

    String selectQuery = "SELECT columns_to_return FROM table_name WHERE column_name = " + _id;
    Cursor c = db.rawQuery(selectQuery, null);
    c.moveToFirst();

    return c;
}

If you need further explanation or help just ask.