用数据库写通讯录项目

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>

void create_table(sqlite3 *db)
{
char * sql;
char * errmsg;
int rec;
sql = “create table if not exists mytable (id integer primary key,name text,address text,tel text,homephone text);”;
rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
if(rec != SQLITE_OK)
{
// printf(“create error!%s\n”,errmsg);
exit(-1);
}
}

void insert_record(sqlite3 *db)
{

char sql[100];
char * errmsg;
int rec;
char name[20],address[50],tel[20],homephone[20];

printf("Please input name:\n");
scanf("%s",name);

printf("Please input address:\n");
scanf("%s",address);

printf("Please input tel:\n");
scanf("%s",tel);

printf("Please input homephone:\n");
scanf("%s",homephone);


sprintf(sql,"insert into mytable (id,name,address,tel,homephone) values(NULL,'%s','%s','%s','%s');"
        ,name,address,tel,homephone);
    
rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

if(rec != SQLITE_OK)
{
    //printf("insert error!%s\n",errmsg);
    exit(-1);
}

}

int display_record(void * para,int n_col,char ** val_col,char ** name_col)
{
int i;
//printf("column count is %d\n ",n_col);

for(i = 0; i < n_col;i++)
{
    printf("%10s",name_col[i]);
}
printf("\n");
for(i = 0; i < n_col;i++)
{
    printf("%10s",val_col[i]);
}
printf("\n");
return SQLITE_OK;

}

void inquire_usedb(sqlite3 *db)
{
char * sql;
char * errmsg;
int rec;
sql = “select * from mytable;”;
rec = sqlite3_exec(db,sql,display_record,NULL,&errmsg);
if(rec != SQLITE_OK)
{
// printf(“select error!%s\n”,errmsg);
exit(-1);
}
}

/*void inquire_nocb(sqlite3 * db)
{
char * sql;
char ** azresult;
int n_row;
int n_col;
char *errmsg;
int i;
int rec;

sql = "select * from mytable;";

rec = sqlite3_get_table(db,sql,&azresult,&n_row,&n_col,&errmsg);

if(rec != SQLITE_OK)
{
   // printf("get table error!%s\n",errmsg);
    exit(-1);
}
printf("n_row = %d n_col = %d\n",n_row,n_col);
printf("result is:\n ");
for(i = 0;i < n_col;i++)
{
    printf("%10s",azresult[i]);
    if((i + 1) % n_col == 0)
    {
        printf("\n");
    }
}

sqlite3_free_table(azresult);

}
*/
void del(sqlite3 *db)
{
int rec;
char sql[100];
int id;
char * errmsg;
printf(“Please input the id:\n”);
scanf("%d",&id);

sprintf(sql,"delete from mytable where id = %d;",id);

rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

if(rec != SQLITE_OK)
{
   // printf("create error!%s\n",errmsg);
    exit(-1);
}

}
void remake(sqlite3 *db)
{
int rec;
char sql[100];
int id,n,i;
char * errmsg;
char a[100],b[100];

printf("Please input the id you want to remake:\n");
scanf("%d",&id);

printf("Please input how number things you want to remake:\n");
scanf("%d",&n);

for(i = 0;i < n;i++)
{
    printf("Please input what you want to remake(from id,name,address,tel,homephone):\n");
    scanf("%s",a);

    printf("Please input you want to remake to:\n");
    scanf("%s",b);

    sprintf(sql,"update mytable set %s = '%s' where id = %d;",a,b,id);
    
    rec = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
}    
    if(rec != SQLITE_OK)
    {
   // printf("create error!%s\n",errmsg);  
        exit(-1);
    }

}

void search(sqlite3 *db)
{
int rec;
char sql[100];
int id;
char * errmsg;
char a[100],b[100];

printf("Please input the id you want to research:\n");
scanf("%d",&id);

sprintf(sql,"select id,name,address,tel,homephone from mytable where id = %d;",id);

rec = sqlite3_exec(db,sql,display_record,NULL,&errmsg);

if(rec != SQLITE_OK)
    {
   // printf("create error!%s\n",errmsg);  
        exit(-1);
    }

}

void menu()
{
printf("---------------------------\n");
printf("---------------------------\n");
printf("-----------通讯录----------\n");
printf("---------------------------\n");
printf("--------1.增加记录---------\n");
printf("--------2.显示记录---------\n");
printf("--------3.删除记录---------\n");
printf("--------4.修改记录---------\n");
printf("--------5.查找记录---------\n");
printf("--------6.退出-------------\n");
printf("---------------------------\n");
printf("---------------------------\n");
}

int main()
{
sqlite3 *db;
int rec;
int a;

rec = sqlite3_open("mytabledatabase.db",&db);

if(rec != SQLITE_OK)
{
    //printf("open error!%s\n",sqlite3_errmsg);
    exit(-1);
}

create_table(db);

system("clear");
menu();

while(1)
{
    printf("请选择功能：\n");
    scanf("%d",&a);
    switch(a)
    {
        case 1:
        
            insert_record(db);
            system("clear");
            menu();

            break;
        
        case 2:
            system("clear");
            menu();
            inquire_usedb(db);    
            break;

// inquire_nocb(db);

        case 3:
            del(db);
            system("clear");
            menu();

            break;

        case 4:
            remake(db);
            system("clear");
            menu();

            break;

        case 5:
            system("clear");
            menu();

            search(db);
            break;

        case 6:
            sqlite3_close(db);
            exit(-1);
        
        default:
            printf("'a' useless,please input again:\n");
            break;
    }       
    
}
return 0;

}