Fetching data rendomly from sqlite without repetition

Question

I am working with the sqlite database and everything is going well. I added 4 entries in the table, and then added textview on my xib file. Now i want that my textview should show me these entries rendomly without repeating. I mean that whenever i run my application my textview should fetch the data from the different row which was not shown before. Please help me out with my issue.

~~~~~~~~~~~~~~~~~~~~~~~~~ Fetching Data from table of MOtivational Thoughts ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    if(sqlite3_open([databasePath UTF8String], &contactDB) == SQLITE_OK) {

        const char* sql = "Select * FROM THOUGHTS";

        sqlite3_stmt *compiledStatement;

    if (sqlite3_prepare_v2(contactDB, sql, -1, &compiledStatement, nil)==SQLITE_OK) {

         NSLog(@"Ready to enter while loop");

        while(sqlite3_step(compiledStatement) == SQLITE_ROW) {

            NSString *aid = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
            textView.text = aid;
        }
    }

    sqlite3_finalize(compiledStatement);

}
sqlite3_close(contactDB);
    }

In textView.text i am getting only the first row data but i want that textview should pic data from any row of the column.

you need to research and try out various options before putting your query for help and without code how come anybody can help you. — Suhit Patil
– Suhit Patil, Commented Nov 26, 2013 at 7:22
You want to remove duplication of rows??? or you want to know how to fetch records from DB???? — Baby Groot
– Baby Groot, Commented Nov 26, 2013 at 7:30
You want to fetch at random but avoid duplication of received data? Why not just store the 'random' in a hash table of the type {random -> random} and never query for that row again? This assumes that between the first and the last random query, you never update your table. I am not sure if this is what you want. — p0lAris
– p0lAris, Commented Nov 26, 2013 at 8:14

TheEye · Accepted Answer · 2013-11-26 09:30:47Z

1

Add a column named alreadyFetched to your table, then after fetching a random row from the table set this row's alreadyFetched to true. When fetching rows again, select only rows with alreadyFetched = false

For getting a random row, select all rows with the query I just described, then select one of the received rows with arc4random%[rowcount].

answered Nov 26, 2013 at 9:30

TheEye

9,3762 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TheEye Over a year ago

That's not too hard -a select statement like this const char* sql = "Select * FROM THOUGHTS Where alreadyFetched = false"; will get you all rows that have not been used yet in an array, then you just have to get a random row with randomRow = [resultArray objectAtIndex:(arc4random()%[resultArray count]);. For that row you issue an Update statement to set alreadyFetched to true: Update thoughts set alreadyFetched = true where ROWID = randomRow.ROWID;. That's it.

sahil dhiman Over a year ago

i am done with this issue but now i stuck with the new one. That is i want to save my sqlitedatabase file into the bundle of my project and then cal that database file on the directory path. How this process is to be done?? Please help me out.

p0lAris Over a year ago

This is a much better approach I'd say. It's probably slower considering that you would need to fetch a set of rows and then fetch the row you want but it's incredibly less painful then the hash table approach. Moreover, it doesn't have any constraints to table updates.

TheEye Over a year ago

Plus, the sqlite framework does optimization and doe snot load all rows that are selected, so it should also not be too slow.

Collectives™ on Stack Overflow

Fetching data rendomly from sqlite without repetition

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related