iOS NSArray of NSDictionaries from SQLite in UITableView

Question

I have been struggling for the last two days with this problem and I can't quite seem to figure it out. I have a SQlite database with the following structure.

it's a one to many relationship between List and List_Items

I access the database and create an object that is then added to a NSDictionary which is added to a NSArray. I do this twice, once for the List table and once for the List_Items. Then I use the list Array to count the number of lists for my tableview rows, I then add them to the tableview.

The problem comes in when I get to the method

-(void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath 

I can't seem to figure out how to match up the records from Lists and List_Items to show the List Items that pertain to that list in a drill down tableview.

Specific code examples would be helpful as my mind is mush at this point :(

Here is the related code that I have up to my current Writersblock.

 //#******************************************************#

//               *******Start Database*******

//#******************************************************#

-(void)checkAndCreateDatabase
{
    // Check if the SQL database has already been saved to the users phone, if not then copy it over
    BOOL success;

    // Create a FileManager object, we will use this to check the status
    // of the database and to copy it over if required
    NSFileManager *fileManager = [NSFileManager defaultManager];

    // Check if the database has already been created in the users filesystem
    success = [fileManager fileExistsAtPath:databasePath];

    // If the database already exists then return without doing anything
    if(success) return;

    // If not then proceed to copy the database from the application to the users filesystem

    // Get the path to the database in the application package
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

    // Copy the database from the package to the users filesystem
    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
}

-(void)readItemsFromDatabase 
{
    // Setup the database object
    sqlite3 *database;

    // Init the Items Array
    items = [[NSMutableArray alloc] init];
    lists = [[NSMutableArray alloc] init];

    //---------------### SELECT THE LISTS #####---------------//

    // Open the database from the users filessytem
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) 
    {
        NSLog(@"SQL Opened");
        // Setup the SQL Statement and compile it for faster access
        const char *sqlStatement = "SELECT * from List";
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            // Loop through the results and add them to the array
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                // Read the data from the result row
                NSString *aListName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
                NSString *aUserID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
                NSString *aListID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];

                NSLog(@"SQL Compiled");

                // Create a new list object with the data from the database
                List *list = [[List alloc] initWithlistName:(NSString *)aListName userID:(NSString *)aUserID listID:(NSString *)aListID];

                listNames = [NSDictionary dictionaryWithObjectsAndKeys:list.listName,@"listName",list.listID,@"listID",list.listID,@"listID",nil];

                // Add the Shopping object to the list Array
                [lists addObject:listNames];

            }
        }

        else { NSLog(@"Database Not Found");}

        // Release the compiled statement from memory
        sqlite3_finalize(compiledStatement);
    }
    sqlite3_close(database);

        //---------------### SELECT THE LIST_ITEMS #####---------------//

        if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) 
        {
            NSLog(@"SQL Opened");
            // Setup the SQL Statement and compile it for faster access
            const char *sqlStatement = "SELECT * from List_Items";
            sqlite3_stmt *compiledStatement;
            if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
                // Loop through the results and add them to the array
                while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                    // Read the data from the result row

                    NSString *aBrandName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
                    NSString *aItemName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
                    NSString *aItemQuantity = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];
                    NSString *aImageUrl = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)];
                    NSString *aListID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)];

                    NSLog(@"SQL Compiled");

                    // Create a new items object with the data from the database
                    Shopping *shopping = [[Shopping alloc] initWithlistID:(NSString *)aListID brandName:(NSString *)aBrandName itemName:(NSString *)aItemName itemQuantity:(NSString *)aItemQuantity imageURL:(NSString *)aImageUrl];                    

                    itemList = [NSDictionary dictionaryWithObjectsAndKeys:shopping.listID,@"listID",shopping.brandName,@"brandName",shopping.itemName,@"itemName",shopping.itemQuantity,@"itemQuantity",shopping.imageURL,@"imageURL",nil];

                    // Add the Shopping object to the items Array
                    [items addObject:itemList];
                }
            }

            else { NSLog(@"Database Not Found");}

            // Release the compiled statement from memory
            sqlite3_finalize(compiledStatement);

        NSLog(@"%@",items);
        NSLog(@"%@",lists);

    }
    sqlite3_close(database);

}

//#******************************************************#

//                *******END Database*******

//#******************************************************#

-(NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    int rowcount;
    rowcount = [lists count];
    return rowcount;
}

-(UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

    static NSString *CellIdentifier = @"Cell";

    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
    if (cell == nil) 
    {
        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault 
                                      reuseIdentifier:CellIdentifier];    
    }

    // Set up the cell...

    NSString *cellValue = [[lists objectAtIndex:indexPath.row] objectForKey:@"listName"];

    cell.textLabel.text = cellValue;

    return cell;
}

-(void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath 
{

    if ([[lists objectAtIndex:indexPath.row] objectForKey:@"listID"] != NULL) 
    {        
        NSString *listIndex = [[lists objectAtIndex:indexPath.row] objectForKey:@"listID"];
        int i = [listIndex intValue];
        NSLog(@"indexPath: %d",i);
    }

}

EDIT**********

The single Sql statement returns more than one Listname. This is an issue because I only need one of each list name.

Answer 1

Anyone interested this is how I ened up figuring it out. I get the listID for the list then I make an array and loop through the list items an compair them against the listID. If they are the same as the listID I add them to the array and once it's finished with the for loop it adds the results to my dataobject protocol (to make it available to the next view) then I present a modal view controller and load the array from the dataobject. Once i'm done with the modal view I set the dataobject back to nil and Tada! it works.

-(void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath 
{

    if ([[lists objectAtIndex:indexPath.row] objectForKey:@"listID"] != NULL) 
    {        
        [listTableView deselectRowAtIndexPath:indexPath animated:YES];
        NSString *listIndex = [[lists objectAtIndex:indexPath.row] objectForKey:@"listID"];

        NSMutableArray *itemArray = [[NSMutableArray alloc]init];

        int i; int itemCount = [items count];

        for (i = 0; i < itemCount; i++) 
        {
            if ([[[items objectAtIndex:i] objectForKey:@"listID"] isEqual:listIndex]) 
            {
                [itemArray addObject:[items objectAtIndex:i]];
                NSLog(@"Item Array:%@",itemArray);
            }
        }

        if (i == itemCount) 
        {
            AppDataObject* theDataObject = [self theAppDataObject];
            theDataObject.itemArray = itemArray;

            ItemView *temp = [[ItemView alloc] initWithNibName:@"ItemView" bundle:nil];
            [self presentModalViewController: temp animated: YES];
        }

    }

}

Answer 2

So first of all, You're creating a List object and then creating an NSDictionary object that's pretty much the same as the List Object. Why? Why not just add the List object to the the Array. If you're not performing any functions on the properties in the List item, then don't use a List object at all, just put the fields directly in the NSDictionary.

Secondly, don't do two different SQL calls to get the info, use only one to get the List and the list_items for that list at the same time. Then if you're using your List object, add a NSMutableArray property call items, and add your listItems to that array. You can do the same thing in an NSDictionary, just add an NSMutableArray object for key items, then add the list_items to that array.

Now you'll be able to setup the tableview to do what you want.

Amended answer in response to comments below

Select * FROM List, List_Items WHERE List.list_id = List.list_id