iPhone: Repeating Rows in Each Section of Grouped UITableview

I'm trying to learn how to use the UITableView in conjunction with a SQLite back end. My issue is that I've gotten the table to populate with the records from the database, however I'm having a problem with the section titles. I am not able to figure out the proper set up for this, and I'm repeating all tasks under each section.

The table looks like this. The groups field is where I'm trying to pull the section title from.

TaskID  group开发者_StackOverflow社区s      TaskName    sched   lastCompleted   nextCompleted   success
1       Household   laundry     3       03/19/2010      03/22/2010      y 
1       Automotive  Change oil  3       03/20/2010      03/23/2010      y 

In my viewDidLoad Method, I create an array from each column in the table like below.

    //Create and initialize arrays from table columns
    //______________________________________________________________________________________

    ids =[[NSMutableArray alloc] init];
    tasks =[[NSMutableArray alloc] init];
    sched =[[NSMutableArray alloc] init];
    lastComplete =[[NSMutableArray alloc] init];
    nextComplete =[[NSMutableArray alloc] init];
    weight =[[NSMutableArray alloc] init];
    success =[[NSMutableArray alloc] init];
    group =[[NSMutableArray alloc] init];



// Bind them to the data
//______________________________________________________________________________________    
    NSString *query = [NSString stringWithFormat:@"SELECT * FROM Tasks ORDER BY nextComplete "];

    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2( database, [query UTF8String],
                           -1, &statement, nil) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            [ids addObject:[NSString stringWithFormat:@"%i",(int*) sqlite3_column_int(statement, 0)]];
            [group addObject:[NSString stringWithFormat:@"%s",(char*) sqlite3_column_text(statement, 1)]];
            [tasks addObject:[NSString stringWithFormat:@"%s",(char*) sqlite3_column_text(statement, 2)]];
            [sched addObject:[NSString stringWithFormat:@"%i",(int*) sqlite3_column_int(statement, 3)]];
            [lastComplete addObject:[NSString stringWithFormat:@"%s",(char*) sqlite3_column_text(statement, 4)]];
            [nextComplete addObject:[NSString stringWithFormat:@"%s",(char*) sqlite3_column_text(statement, 5)]];
            [success addObject:[NSString stringWithFormat:@"%s",(char*) sqlite3_column_text(statement, 6)]];
            [weight addObject:[NSString stringWithFormat:@"%i",(int*) sqlite3_column_int(statement, 7)]];




        }
        sqlite3_finalize(statement);
    }

In the table method:cellForRowAtIndexPath, I create controls on the fly and set their text properties to objects in the array. Below is a sample, I can provide more but am already working on a book here... :)

/create the task label 
NSString *tmpMessage;
tmpMessage = [NSString stringWithFormat:@"%@ every %@ days, for %@ points",[tasks objectAtIndex:indexPath.row],[sched objectAtIndex:indexPath.row],[weight objectAtIndex:indexPath.row]];
    CGRect schedLabelRect = CGRectMake(0, 0, 250, 15);
    UILabel *lblSched = [[UILabel alloc] initWithFrame:schedLabelRect];
    lblSched.textAlignment = UITextAlignmentLeft;
    lblSched.text = tmpMessage;
    lblSched.font = [UIFont boldSystemFontOfSize:10];
    [cell.contentView addSubview: lblSched];
    [lblSched release];

My numberOfSectionsInTableView method looks like this

// Figure out how many sections there are by a distinct count of the groups field
// The groups are entered by user when creating tasks   
//______________________________________________________________________________________    
    NSString *groupquery = [NSString stringWithFormat:@"SELECT COUNT(DISTINCT groups) as Sum FROM Tasks"];
    int sum;

    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2( database, [groupquery UTF8String],
                           -1, &statement, nil) == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            sum = sqlite3_column_int(statement, 0);

        }
        sqlite3_finalize(statement);
    }
    if (sum=0) {
        return 1;
    }
    return 2;
}

I know I'm going wrong here but this is all that's in my numberOfRowsInSection method

return [ids count];

This is what you want you table to look like from the perspective of the datamodel:

"Household" section
    record 1 of (all records whose "group" column=="Household")
    record 2 of (all records whose "group" column=="Household")
    record 3 of (all records whose "group" column=="Household")
"Automotive" section
    record 1 of (all records whose "group" column=="Automotive")
    record 2 of (all records whose "group" column=="Automotive")
    record 3 of (all records whose "group" column=="Automotive")

Therefore, your data model has to reflect this structure. Right now, it doesn't. You just load in each column into an array but you have no relationship between the arrays e.g. each element in the "group" array has no relationship to any element in any other array. This means you have no way to relate each "group" to the other columns in the same record.

Since SQL has no structure, you have to provide an object layer to covert the unstructured SQL returns into a structured one.

(My SQL is rusty so all the SQL below is pseudocoded.)

// for simplicity, assume that the SQL table is indexed and always returns the same order
@property (nonAtomic,retain) NSArray *group;
@sythesize group;

- (void) viewWillAppear:(BOOL)animated{
    // here groups is the column with "Household", "Automotive" etc
    // You want them unique because you want all "Household" under the same section
    NSArray *unSortedgroup=SQL(every unique name in column "group");
    self.group=[unsortedSectionName arraySortedUsing...]; //Sort the array however you wish
}//------------------------------------viewWillAppear:------------------------------------

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {
    return [self.group count];
}

- (NSString *)tableView:(UITableView *)tableView titleForHeaderInSection:(NSInteger)section{
    return [self.group objectAtIndex:section];  
}

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
    NSString *sectionName=[self.group objectAtIndex:section];
    NSUInteger *rowCount=SQL(count of all records whose "group" column == sectionName);
}

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    //... setup cell


    NSString *sectionName=[self.group objectAtIndex:section];
    NSArray *rowsForThisSection=SQL(indexs of all records whose "group" column == sectionName);
    // the data for each row cell is now in/pointed to by [rowsForThisSection objectAtIndex:indexPath.row]
    cell.idLabel.text=SQL(column "id" of record whose index is [rowsForThisSection objectAtIndex:indexPath.row])

    // ... and so on for your other columns/attributes      
}

This is why Core Data was created. SQL and other serial forms have to be shoehorned into the object graphs used by object oriented APIs and that takes a lot of glue code. With Core Data, you create an object graph and then let the back end worry about how it gets stored to disk.

With Core Data, you could do all this with about 8 lines of code plus the graphical data model.