I am making an app which requires me to do traverse through a list of data which are stored in internal SQLite database. I am using FMDB for all database operation in my app.I want my "next" and "previous" available if the shown data has next record or previous record or else, the buttons will be disabled for respective operation. I am sending id of the data which user click on the list of data to go to the "Data View Area".
My table structure is
create table messagelist(id int primary key, title text, time text, message text)
In the "Data View Area", I fired the query
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [paths objectAtIndex:0];
NSString *path = [docsPath stringByAppendingPathComponent:@"message.sqlite"];
FMDatabase *database = [FMDatabase databaseWithPath:path];
[database open];
NSString *query = @"SELECT *,(SELECT id FROM messagelist WHERE id < ";
query = [query stringByAppendingString:[NSString stringWithFormat:@"%@",_id_row]];
query = [query stringByAppendingString:@" ORDER BY id DESC LIMIT 1) AS previous_id,(SELECT id FROM messagelist WHERE id > "];
query = [query stringByAppendingString:[NSString stringWithFormat:@"%@",_id_row]];
query = [query stringByAppendingString:@" ORDER BY id ASC LIMIT 1) AS next_id FROM messagelist WHERE id = "];
query = [query stringByAppendingString:[NSString stringWithFormat:@"%@",_id_row]];
FMResultSet *results_one = [database executeQuery:query];
if([results_one next])
{
NSLog(@"Previous id is %d and next id is %d", [results_one intForColumn:@"previous_id"],[results_one intForColumn:@"next_id"]);
}
Which is not returning anything as such however the Android version of my app is working good with this query. Where am I doing wrong?
N:B:- I just look into the database by importing into SQLite browser and I found out ids are not inserted, how come it's possible, id is declared as primary key hence it should automatically should have been inserted and increase accordingly.
