如何遍历SQLite行?
问题描述:
我想用SQLite DB设计登录和注册表单。我做了三个字段的注册表和两个字段的登录表。如何遍历SQLite行?
Registration form:
User Name:
Password:
RetypePassword:
Login form:
User Name:
Password:
如果任何用户尝试登录的第一次,我们应该提供一个消息register.i.e,我们需要通过报名表进行迭代,并检查登录字段是否已经存在与否。
所以在这里我错过了逻辑(循环)遍历注册表的所有记录。
- (void) loginBtn:(id)sender
{
const char *dbpath = [_databasePath UTF8String];
sqlite3_stmt *statement;
NSMutableArray *arr=[[NSMutableArray alloc]init];
if (sqlite3_open(dbpath, &_registerDB) == SQLITE_OK)
{
NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM registertable;"];
const char *query_stmt = [querySQL UTF8String];
if (sqlite3_prepare_v2(_registerDB,query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
if (sqlite3_step(statement) == SQLITE_ROW)
{
NSString *regUserName = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
NSString *regPassword = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 1)];
NSString *str=[NSString stringWithFormat:@"%@%@",regUserName,regPassword];
[arr addObject:str];
//************************LOOP ?????????????
for(int i=o;i<arr.count;i++){
if([loginUserTxtFld.text isEqualToString:??????] && [loginPasswordTxtFld isEqualToString:??????]
{
NSLog(@"login successful");
}
else{
NSLog(@"You have not yet registered");
}
}
_statusLabel.text = @"Match found";
} else {
_status.text = @"Match not found";
_regUserNameTxtFld.text = @"";
_regPwdTxtFld.text = @"";
}
sqlite3_finalize(statement);
}
sqlite3_close(_registerDB);
}
}
我需要改变以通过选定的行进行循环?
答
如果你想循环,更换
if (sqlite3_step(statement) == SQLITE_ROW) {
// this only retrieves one row
}
与
while (sqlite3_step(statement) == SQLITE_ROW) {
// this will be performed for each row
}
但是,甚至更好,你为什么不具备的SQLite做这项工作的吗?
// NB: Do not use `stringWithFormat` to build this SQL, but rather use `?`
// placeholders and then call `sqlite3_bind_text`
const char *query_stmt = "SELECT * FROM registertable WHERE username = ? AND password = ?";
if (sqlite3_prepare_v2(_registerDB, query_stmt, -1, &statement, NULL) != SQLITE_OK) {
NSLog(@"prepare failed: %s", sqlite3_errmsg(_registerDB));
}
if (sqlite3_bind_text(statement, 1, [loginUserTxtFld.text UTF8String], -1, NULL) != SQLITE_OK) {
NSLog(@"bind userid failed: %s", sqlite3_errmsg(_registerDB));
}
if (sqlite3_bind_text(statement, 2, [loginPasswordTxtFld.text UTF8String], -1, NULL) != SQLITE_OK) {
NSLog(@"bind password failed: %s", sqlite3_errmsg(_registerDB));
}
int rc = sqlite3_step(statement);
if (rc == SQLITE_ROW) {
// userid/password found
} else if (rc == SQLITE_DONE) {
// userid/password not found, but no error
} else {
NSLog(@"step failed: %s", sqlite3_errmsg(_registerDB));
}
// continue with finalize, close, or whatever else
很显然,我不知道你的用户名和密码字段的名称,所以相应地改变这个SQL,但希望这说明了想法。
为什么你需要迭代循环?只是SELECT * FROM registertable WHERE用户名=%@“同时将你的userName字段定义为索引 – 2014-11-08 15:54:58
@RyanHeitner更正,使用'WHERE'子句为你完成工作,但绝不要使用'stringWithFormat'来建立SQL, 'printf-style formatters。使用'?'占位符并将值绑定到'sqlite3_bind_text'。或者使用类似[FMDB](https://github.com/ccgus/fmdb)的方式来处理这个麻烦。 – Rob 2014-11-08 16:02:55