getting day value of last 7day from sqllite even if there is no record in database

i need some help in getting the day value of last 7 days from SQLite.

i currently able to get day value from the SQLite if there is a records.

wat i need is to show the last 7 days if the guy did something.

example, if the guys only drink a beer today, it will show

drink - thur
no - wed
no - tue
no - mon
no - sun
no - sat
no - sat

example if the guys did not drink anything, it will show

no - thur
no - wed
no - tue
no - mon
no - sun
no - sat
no - sat

example if the guys drink anything during the last 7 days, it will show

no - thur
drink - wed
no - tue
no - mon
drink - sun
no - sat
no - sa开发者_JS百科t

this is the SQL code

+ (void) getInitialDataToDisplay:(NSString *)dbPath {

    DrinkTabsAndNavAppDelegate *appDelegate = (DrinkTabsAndNavAppDelegate *)[[UIApplication sharedApplication] delegate];

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

        const char *sql = "SELECT DATE(datetime) FROM consumed GROUP BY DATE(datetime) ORDER BY datetime DESC";
        sqlite3_stmt *selectstmt;
        if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

            while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                NSString *dateDrunk = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 0)];
                NSDate *theDate = [NSDate dateFromString:dateDrunk withFormat:@"yyyy-MM-dd"];
                DayOfDrinks *drinkDayObj = [[DayOfDrinks alloc] initWithDateConsumed:theDate];
                [drinkDayObj hydrateDetailViewData];
                //NSLog([NSDate stringFromDate:drinkDayObj.dateConsumed withFormat:@"yyyy-MM-dd"]);
                [appDelegate.drinksOnDayArray addObject:drinkDayObj];
                [drinkDayObj release];
            }
        }
    }
    else
        sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
}

DrinkHistoryTableViewController.m

if (drunked<7) {
    for (int i=drunked; i<7; i++) {


        NSString * dayString= [NSString stringWithFormat:@"Nil"];/

        [dayArray addObject:dayString];

        }
    }

    for(int i=drunked; i>0; i--) 
    {
        DayOfDrinks *drinksOnDay = [appDelegate.drinksOnDayArray objectAtIndex:i-1];

        NSString * dayString= [NSDate stringForDisplayFromDateForChart:drinksOnDay.dateConsumed];

        [dayArray addObject:dayString];//X label for graph the day of drink.

        drinksOnDay.isDetailViewHydrated = NO;

        [drinksOnDay hydrateDetailViewData];

        NSNumber *sdNumber =  drinksOnDay.standardDrinks; // pass value over to Standard Drink Numbers

        //[sdArray addObject: sdNumber]; 

        float floatNum = [sdNumber floatValue]; // convert sdNumber to foat

        [sdArray addObject:[NSNumber numberWithFloat:floatNum]];//add float Value to sdArray
}

can anybody help to answer my question thanks a lot.

Des

Update your query with this and try out :

"SELECT DATE(datetime) FROM consumed GROUP BY DATE(datetime) ORDER BY datetime DESC Limit 7"

You will get last 7 days information.

Is that Objective C? Yuk. Too much code for a 'sqlite' question :]

When working with reporting databases, datamining etc. it's not ridiculous to have a table with just dates in it that you can join with. You could have a table with just dates that are added to daily or whatever that you join across to and that would easily allow you to get YES or NO on whether they drank that day. (This would be a pretty lazy solution, but would work)

The other option is to just query out the days they drank in the last 7 days... which may be 1 or 2 days. Then in code loop through today to today - 7 ... and then an inner loop to loop through your returned records to see if there is a match for that day. If there is, then you know they drank or did not drink.

Nested loop would work, and should perform fine just doing it over 7 days.

Those are 2 of plenty of options to solve it. I however loathe Objective C and have on interest in writing any tonight :]