SQL query not working for some reason

Question

i'm writing this query for a leaderboard system. It needs to return the next closest target for the person in relation to the targets score. For some reason my sql query just won't work.

This is my query:

[NSString stringWithFormat:@"SELECT * FROM Children WHERE Completed > '%d' OR ( Completed = '%d' AND 'Current Stickers' > '%d')",completecount,completecount, stickercount]; 

completecount = the amount of times this person has completed the goal stickercount = the amount of stickers this person has. both are ints So i want the query to return people who have completed it more than the person or if its the same amount of times and if they have more stickers than them.

database layout:

NSString *sql = [NSString stringWithFormat:
                 @"CREATE TABLE IF NOT EXISTS '%@' ('%@'"
                 "TEXT PRIMARY KEY, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' INTEGER, '%@' INTEGER, '%@' TEXT, '%@' INTEGER, '%@' INTEGER, '%@' INTEGER, '%@' INTEGER);", tableName, field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11];

field names:

[self createTable:@"Children" withField1:@"Name" withField2:@"Password" withField3:@"House" withField4:@"Sticker Collection" withField5:@"Tickets Gathered" withField6:@"Tickets Removed" withField7:@"Last Ticket Scanned" withField8:@"Current Tickets" withField9:@"Completed" withField10:@"Complete" withField11:@"Current Stickers"];

This is the actual query:

sqlite3_stmt *statement;
if (sqlite3_prepare_v2(Childdb, [sql UTF8String], -1, &statement, nil)==SQLITE_OK) {
    while (sqlite3_step(statement)==SQLITE_ROW) {
          char *field1 = (char *) sqlite3_column_text(statement, 0);
          NSString *field1Str = [[NSString alloc]initWithUTF8String:field1];
        char *field2 = (char *) sqlite3_column_text(statement, 8);
        NSString *field2Str = [[NSString alloc]initWithUTF8String:field2];
        char *field3 = (char *) sqlite3_column_text(statement, 10);
        NSString *field3Str = [[NSString alloc]initWithUTF8String:field3];
          NSLog(@"Name:%@",field1Str);
        NSLog(@"this is completecount: %@", field2Str);
        NSLog(@"this is stickcount: %@",field3Str);

So the real problem is that despite the query set up its still returning invalid records. Looks at this example:

This persons name is stuart:

2013-02-24 16:35:55.409 TableViewStory[15672:907] stickercount = 3
2013-02-24 16:35:55.410 TableViewStory[15672:907] completecount = 0
2013-02-24 16:35:55.412 TableViewStory[15672:907] Name:Oliver
2013-02-24 16:35:55.413 TableViewStory[15672:907] this is completecount: 1
2013-02-24 16:35:55.414 TableViewStory[15672:907] this is stickcount: 0
2013-02-24 16:35:55.415 TableViewStory[15672:907] Name:Rupert
2013-02-24 16:35:55.416 TableViewStory[15672:907] this is completecount: 2
2013-02-24 16:35:55.417 TableViewStory[15672:907] this is stickcount: 0
2013-02-24 16:35:55.418 TableViewStory[15672:907] Name:Emily
2013-02-24 16:35:55.419 TableViewStory[15672:907] this is completecount: 0
2013-02-24 16:35:55.420 TableViewStory[15672:907] this is stickcount: 0
2013-02-24 16:35:55.421 TableViewStory[15672:907] Name:Stuart
2013-02-24 16:35:55.423 TableViewStory[15672:907] this is completecount: 0
2013-02-24 16:35:55.424 TableViewStory[15672:907] this is stickcount: 3

Check this out, its even showing himself in the results ^ here. not only that its even showing Emily which has the same complete count but not more stickers than Stuart, yet Emily is still retrieved. Whats going on here?

Request by Ege Akpinar to remove part of the where clause:

  NSString *sql = [NSString stringWithFormat:@"SELECT * FROM Children WHERE Completed > %d",completecount];

results:

2013-02-24 17:37:22.626 TableViewStory[15814:907] This is the sticker count: 4
2013-02-24 17:37:22.627 TableViewStory[15814:907] This is the complete count: 0
2013-02-24 17:37:22.629 TableViewStory[15814:907] Name:Oliver
2013-02-24 17:37:22.629 TableViewStory[15814:907] this is completecount: 1
2013-02-24 17:37:22.630 TableViewStory[15814:907] this is stickcount: 0
2013-02-24 17:37:22.631 TableViewStory[15814:907] Name:Rupert
2013-02-24 17:37:22.632 TableViewStory[15814:907] this is completecount: 2
2013-02-24 17:37:22.633 TableViewStory[15814:907] this is stickcount: 0

Removing the where clause completely:

NSString *sql = [NSString stringWithFormat:@"SELECT * FROM Children"];

outcome:

2013-02-24 17:38:42.698 TableViewStory[15829:907] This is the sticker count: 4
2013-02-24 17:38:42.699 TableViewStory[15829:907] This is the complete count: 0
2013-02-24 17:38:42.700 TableViewStory[15829:907] Name:Oliver
2013-02-24 17:38:42.701 TableViewStory[15829:907] this is completecount: 1
2013-02-24 17:38:42.702 TableViewStory[15829:907] this is stickcount: 0
2013-02-24 17:38:42.703 TableViewStory[15829:907] Name:Rupert
2013-02-24 17:38:42.704 TableViewStory[15829:907] this is completecount: 2
2013-02-24 17:38:42.705 TableViewStory[15829:907] this is stickcount: 0
2013-02-24 17:38:42.707 TableViewStory[15829:907] Name:Emily
2013-02-24 17:38:42.708 TableViewStory[15829:907] this is completecount: 0
2013-02-24 17:38:42.709 TableViewStory[15829:907] this is stickcount: 0
2013-02-24 17:38:42.710 TableViewStory[15829:907] Name:Stuart
2013-02-24 17:38:42.711 TableViewStory[15829:907] this is completecount: 0
2013-02-24 17:38:42.712 TableViewStory[15829:907] this is stickcount: 4

Putting brackets on statements:

  NSString *sql = [NSString stringWithFormat:@"SELECT * FROM Children WHERE (Completed > %d) OR (Completed = %d AND 'Current Stickers' > %d)",completecount,completecount, stickercount];

This is outcome:

2013-02-24 17:59:33.987 TableViewStory[15898:907] This is the sticker count: 4
2013-02-24 17:59:33.988 TableViewStory[15898:907] This is the complete count: 0
2013-02-24 17:59:33.990 TableViewStory[15898:907] Name:Oliver
2013-02-24 17:59:33.991 TableViewStory[15898:907] this is completecount: 1
2013-02-24 17:59:33.992 TableViewStory[15898:907] this is stickcount: 0
2013-02-24 17:59:33.993 TableViewStory[15898:907] Name:Rupert
2013-02-24 17:59:33.994 TableViewStory[15898:907] this is completecount: 2
2013-02-24 17:59:33.995 TableViewStory[15898:907] this is stickcount: 0
2013-02-24 17:59:33.996 TableViewStory[15898:907] Name:Emily
2013-02-24 17:59:33.997 TableViewStory[15898:907] this is completecount: 0
2013-02-24 17:59:33.998 TableViewStory[15898:907] this is stickcount: 0
2013-02-24 17:59:33.999 TableViewStory[15898:907] Name:Stuart
2013-02-24 17:59:34.000 TableViewStory[15898:907] this is completecount: 0
2013-02-24 17:59:34.002 TableViewStory[15898:907] this is stickcount: 4

now trying current stickers >:

NSString *sql = [NSString stringWithFormat:@"SELECT * FROM Children WHERE 'Current Stickers' > %d",stickercount];

result with stuart:

2013-02-24 18:05:27.855 TableViewStory[15942:907] This is the sticker count: 4
2013-02-24 18:05:27.856 TableViewStory[15942:907] This is the complete count: 0
2013-02-24 18:05:27.857 TableViewStory[15942:907] Name:Oliver
2013-02-24 18:05:27.858 TableViewStory[15942:907] this is completecount: 1
2013-02-24 18:05:27.860 TableViewStory[15942:907] this is stickcount: 0
2013-02-24 18:05:27.861 TableViewStory[15942:907] Name:Rupert
2013-02-24 18:05:27.862 TableViewStory[15942:907] this is completecount: 2
2013-02-24 18:05:27.863 TableViewStory[15942:907] this is stickcount: 0
2013-02-24 18:05:27.864 TableViewStory[15942:907] Name:Emily
2013-02-24 18:05:27.865 TableViewStory[15942:907] this is completecount: 0
2013-02-24 18:05:27.866 TableViewStory[15942:907] this is stickcount: 0
2013-02-24 18:05:27.867 TableViewStory[15942:907] Name:Stuart
2013-02-24 18:05:27.868 TableViewStory[15942:907] this is completecount: 0
2013-02-24 18:05:27.870 TableViewStory[15942:907] this is stickcount: 4

This makes me think that its something wrong with stickcount

Answer 1

I think your error might be that you're using quotations for numbers. Remove quotation marks around your parameters.

Thus instead of Completed > '%d' use Completed > %d (and of course the other params too)

When you use quotation marks, it most probably converts your string to a number which would explain why you have unexpected results.