/*
* Mysql的C语言接口
*实现电话本管理程序
*Lzy2011-7-17
*/
#include
#include
#include
#include
intmenu_main(void)
{
intnum;
printf("\n--------Phone book management--------\n");
printf(" 1.Show all contacts\n");
printf(" 2.Find a contact by name\n");
printf(" 3.Fuzzy search by name\n");
printf(" 4.Add new contact\n");
printf(" 0.Quit\n");
printf("\nInput > ");//打印命令提示符
fflush(stdout);
scanf("%d",&num);
returnnum;
}
intInitMysql(MYSQL*conn)
{
/*连接服务器*/
if(!(mysql_real_connect(conn,"localhost","root","","test",0,NULL,0)))
{
fprintf(stderr,"connect fail: %s\n",mysql_error(conn));
return-1;
}
if(mysql_query(conn,"use test;"))
{
fprintf(stderr,"%s\n",mysql_error(conn));
return-1;
}
if(mysql_query(conn,"create table if not exists celltable(id int primary key auto_increment, name char(10), telephone varchar(11));"))
{
fprintf(stderr,"%s\n",mysql_error(conn));
return-1;
}
return0;
}
/*
*显示所有联系人信息,返回数组地址保存手机ID号,用完之后需释放
*/
int*PlayAll(MYSQL*conn,char*arg)
{
if(mysql_query(conn,arg))
{
fprintf(stderr,"%s\n",mysql_error(conn));
returnNULL;
}
MYSQL_RES*res=mysql_store_result(conn);//查询结果
intcols=mysql_num_fields(res);//结果集中的列数
introws=mysql_num_rows(res);//结果集中的行数
MYSQL_ROW row;//声明1行数据的“类型安全”
if(rows==0)
returnNULL;
inti,flag=1,count=0;
int*num=(int*)malloc(4*rows);
printf("\n---Contact Information---\n");
printf("Name\tTell\n");
while((row=mysql_fetch_row(res))!=NULL)//输出全部信息
{
for(i=0;i
{
if(flag)
{
num[count]=atoi(row[0]);//每一条信息的编号
flag=0;
continue;
}
printf("%s\t",row[i]);
}
flag=1;
count++;
printf("\n");
}
mysql_free_result(res);//释放结果集分配的内存
returnnum;
}
/*
*修改数据库信息函数
*入口参数:conn->服务器句柄
*num ->数据库纪录编号
*/
intReInfor(MYSQL*conn,intnum)
{
charbuf[20],arg[100];
intchose;
printf("\nFunction: 1->ReName 2-> ReTelephone 3->delte: ");
getchar();
chose=getchar();
switch(chose)
{
case'1':
printf("Name: ");
getchar();
scanf("%s",buf);
sprintf(arg,"update celltable set name='%s' where id=%d;",buf,num);
if(mysql_query(conn,arg))
{
fprintf(stderr,"%s\n",mysql_error(conn));
return-1;
}
break;
case'2':
printf("Tell: ");
getchar();
scanf("%s",buf);
sprintf(arg,"update celltable set telephone='%s' where id=%d;",buf,num);
if(mysql_query(conn,arg))
{
fprintf(stderr,"%s\n",mysql_error(conn));
return-1;
}
break;
case'3':
sprintf(arg,"delete from celltablewhere id=%d;",buf,num);
if(mysql_query(conn,arg))
{
fprintf(stderr,"%s\n",mysql_error(conn));
return-1;
}
break;
default:
return0;
}
return0;
}
/*
*按名字查找联系人信息
*预处理方法进行查询修改信息
*/
intFindByName(MYSQL*conn)
{
charquery[50];
MYSQL_STMT*stmt=mysql_stmt_init(conn);//创建MYSQL_STMT句柄
strcpy(query,"select * from celltable where name=?;");
if(mysql_stmt_prepare(stmt,query,strlen(query)))
{
fprintf(stderr,"mysql_stmt_prepare: %s\n",mysql_error(conn));
return-1;
}
MYSQL_BIND ctos[1],stoc[3];
bzero(ctos,sizeof(ctos));
bzero(stoc,sizeof(stoc));
charname[20];
printf("Name: ");
scanf("%s",name);
ctos[0].buffer_type=MYSQL_TYPE_STRING;
ctos[0].buffer_length=strlen(name);
ctos[0].buffer=name;
if(mysql_stmt_bind_param(stmt,ctos))
{
fprintf(stderr,"mysql_stmt_bind_param: %s\n",mysql_error(conn));
return-1;
}
intid;
chartell[11];
stoc[0].buffer_type=MYSQL_TYPE_LONG;
stoc[0].buffer=&id;
stoc[1].buffer_type=MYSQL_TYPE_STRING;
stoc[1].buffer_length=sizeof(name);
stoc[1].buffer=name;
stoc[2].buffer_type=MYSQL_TYPE_STRING;
stoc[2].buffer_length=sizeof(tell);
stoc[2].buffer=tell;
if(mysql_stmt_bind_result(stmt,stoc))
{
fprintf(stderr,"mysql_stmt_bind_result: %s\n",mysql_error(conn));
return-1;
}
if(mysql_stmt_execute(stmt))
{
fprintf(stderr,"mysql_stmt_execute: %s\n",mysql_error(conn));
return-1;
}
if(mysql_stmt_store_result(stmt))
{
fprintf(stderr,"mysql_stmt_store_result: %s\n",mysql_error(conn));
return-1;
}
printf("\n---Contact Information---\n");
printf("Name\tTell\n");
while(mysql_stmt_fetch(stmt)==0)
printf("%s\t%s\n",name,tell);
ReInfor(conn,id);//修改信息
mysql_stmt_close(stmt);
return0;
}
/*
*按名字模糊查找 显示找到所有与关键字匹配的结果,如需修改结果中的信息,则输入Number
*/
intFindByLikeName(MYSQL*conn)
{
int*num;
charname[10];
chararg[100];
intn;
printf("Input Like: ");
getchar();
scanf("%s",name);
sprintf(arg,"select * from celltable where name like '%%%s%';",name);
num=PlayAll(conn,arg);
printf("Number: ");
getchar();
n=getchar();
if(n!='\n')
{
n=n-'0';
ReInfor(conn,num[n-1]);//修改信息
}
free(num);
return0;
}
/*
*新增联系人如果联系人存在,则修改此人信息
*/
intInputInfo(MYSQL*conn)
{
int*num;
charname[10],telephone[11];
charch;
chararg[100];
printf("Name: ");//输入姓名
scanf("%s",name);
sprintf(arg,"select * from celltable where name='%s';",name);
num=PlayAll(conn,arg);
if(num)
{
ReInfor(conn,num[0]);//修改信息
}
else
{
printf("telephone: ");//输入号码
scanf("%s",telephone);
printf("Save? y/n ");//是否保存
getchar();
scanf("%c",&ch);
if(ch=='y')
{
sprintf(arg,"insert into celltable(name, telephone) values('%s', '%s');",name,telephone);//生成命令
if(mysql_query(conn,arg))//插入数据库
{
fprintf(stderr,"%s\n",mysql_error(conn));
return-1;
}
num=PlayAll(conn,"select * from celltable;");//显示所有联系人
}
else
{
printf("save fail!\n");
return0;
}
}
free(num);
}
intmain(void)
{
int*num;
MYSQL*conn=mysql_init(NULL);//初始化服务器句柄;
InitMysql(conn);//连接服务器、创建数据库
while(1)
{
switch(menu_main())
{
case1:
num=PlayAll(conn,"select * from celltable;");//显示所有联系人
free(num);
break;
case2:
FindByName(conn);
break;
case3:
FindByLikeName(conn);
break;
case4:
InputInfo(conn);
break;
case0:
mysql_close(conn);
puts("Quit!\n");
return0;
default:
break;
}
}
return0;
}