FMDB Result of table to JSON object respecting the order of the columns.

Question

EDIT: NEW QUESTION AT BOTTOM It was getting all the rows I was wrong I am trying to get the results of an SQL query and place them into a json object to send to my server to do more work with the data. Right now my code is only returning 1 row of data. Can anyone see any glaring mistakes? IOS SIDE

- (void)sendLogin
{
NSError *jsonError;
NSData *requestdata;
//get login
NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [docPaths objectAtIndex:0];
NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"tar.sqlite"];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
FMResultSet *Loginresults = [database executeQuery:@"SELECT * FROM surveys"];
NSMutableArray *results = [NSMutableArray array];
while ([Loginresults next]) {
    [results addObject:[Loginresults resultDictionary]];

    requestdata = [NSJSONSerialization dataWithJSONObject:results options:0 error:&jsonError];
}

[database close];




NSURL *url = [NSURL URLWithString:@"http://server/insert.php"];

NSMutableURLRequest *request;
request = [NSMutableURLRequest requestWithURL:url];
[request setHTTPMethod:@"POST"];
[request setValue:[NSString stringWithFormat:@"%d", [requestdata length]] forHTTPHeaderField:@"Content-Length"];
[request setValue:@"application/json" forHTTPHeaderField:@"Accept"];
[request setValue:@"application/x-www-form-urlencoded" forHTTPHeaderField:@"Content-Type"];
[request setHTTPBody:requestdata];

//this kicks off the request asynchronously
NSURLConnection *connection = [[NSURLConnection alloc] initWithRequest:request delegate:self];
}

PHP SIDE

<?php
$foo = file_get_contents("php://input");

var_dump(json_decode($foo, true));

?>

Results from this

finish requesting: array(1) {
[0]=>
array(7) {
["desc"]=>
string(13) "matt the best"
["creator"]=>
string(7) "eric jr"
["synch"]=>
NULL
["name"]=>
string(4) "matt"
["sid"]=>
string(1) "1"
["datetime"]=>
string(3) "now"
["pid"]=>
string(1) "1"
}
}

Also I noticed my results for that table are not in the correct order. Is that working as intended? Is there any way to order this so it matches the order of the columns?

Answer 1

I'm not familiarized with FMDatabase. But I think that your problem is in the while statement:

FMResultSet *Loginresults = [database executeQuery:@"SELECT * FROM surveys"];
NSMutableArray *results = [NSMutableArray array];
while ([Loginresults next]) {
    [results addObject:[Loginresults resultDictionary]];

    requestdata = [NSJSONSerialization dataWithJSONObject:results options:0 error:&jsonError];
}

You are overwriting the variable: requestdata with each result.

Maybe if you must change the code for this:

FMResultSet *Loginresults = [database executeQuery:@"SELECT * FROM surveys"];
NSMutableArray *results = [NSMutableArray array];
while ([Loginresults next]) 
{
    [results addObject:[Loginresults resultDictionary]];
}
requestdata = [NSJSONSerialization dataWithJSONObject:results options:0 error:&jsonError];

Answer 2

Ok. Let me explain the solution. As you said in the comment, you are using the library throughout the app.

So, I think the best solution to your problem is create the OrderedDictionary from CocoaWithLove. The OrderedDictionary contains an NSMutableDictionary and an NSMutableArray for the keys inside it, to keep track of the order of the keys.

The you can create a Category of FMResultSet:

FMResultSet+OrderedDict.h

#import <Foundation/Foundation.h>
#import "FMResultSet.h"
#import "OrderedDictionary.h"
@interface FMResultSet(OrderedDict)
- (OrderedDictionary*)resultOrderDictionary;
@end

FMResultSet+OrderedDict.m

#import "FMResultSet+OrderedDict.h"
#import "OrderedDictionary.h"
#import "FMDatabase.h"

@implementation FMResultSet(OrderedDict)

- (OrderedDictionary*)resultOrderDictionary {

    NSUInteger num_cols = (NSUInteger)sqlite3_data_count([_statement statement]);

    if (num_cols > 0) {
        OrderedDictionary *dict = [OrderedDictionary dictionaryWithCapacity:num_cols];

        int columnCount = sqlite3_column_count([_statement statement]);

        int columnIdx = 0;
        for (columnIdx = 0; columnIdx < columnCount; columnIdx++) {

            NSString *columnName = [NSString stringWithUTF8String:sqlite3_column_name([_statement statement], columnIdx)];
            id objectValue = [self objectForColumnIndex:columnIdx];
            [dict setObject:objectValue forKey:columnName];
        }

        return dict;
    }
    else {
        NSLog(@"Warning: There seem to be no columns in this set.");
    }

    return nil;
}

@end

And in your code do this:

NSMutableArray *results = [NSMutableArray array];
    while ([Loginresults next]) {
        [results addObject:[Loginresults resultOrderDictionary]];
    }
    requestdata = [NSJSONSerialization dataWithJSONObject:results options:0 error:&jsonError];

If you want I can upload my Project Test to github.

EDIT: The code now is uploaded.