分类： Mysql/postgreSQL
2011-07-17 15:27:34
/*
* Mysql的C语言接口
* 实现电话本管理程序
* Lzy 2011-7-17
*/
#include
#include
#include
#include
int menu_main(void)
{
int num;
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);
return num;
}
int InitMysql(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;
}
return 0;
}
/*
* 显示所有联系人信息，返回数组地址保存手机ID号，用完之后需释放
*/
int *PlayAll(MYSQL *conn, char *arg)
{
if(mysql_query(conn, arg))
{
fprintf(stderr, "%s\n",mysql_error(conn));
return NULL;
}
MYSQL_RES *res = mysql_store_result(conn); //查询结果
int cols = mysql_num_fields(res); //结果集中的列数
int rows = mysql_num_rows(res); //结果集中的行数
MYSQL_ROW row; //声明1行数据的“类型安全”
if(rows == 0)
return NULL;
int i, 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 < cols; 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); //释放结果集分配的内存
return num;
}
/*
* 修改数据库信息函数
* 入口参数：conn->服务器句柄
* num ->数据库纪录编号
*/
int ReInfor(MYSQL *conn, int num)
{
char buf[20], arg[100];
int chose;
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 celltable where id=%d;",buf, num);
if(mysql_query(conn, arg))
{
fprintf(stderr, "%s\n",mysql_error(conn));
return -1;
}
break;
default:
return 0;
}
return 0;
}
/*
* 按名字查找联系人信息
* 预处理方法进行查询修改信息
*/
int FindByName(MYSQL *conn)
{
char query[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));
char name[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;
}
int id;
char tell[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);
return 0;
}
/*
* 按名字模糊查找 显示找到所有与关键字匹配的结果，如需修改结果中的信息，则输入Number
*/
int FindByLikeName(MYSQL *conn)
{
int *num;
char name[10];
char arg[100];
int n;
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);
return 0;
}
/*
* 新增联系人如果联系人存在，则修改此人信息
*/
int InputInfo(MYSQL *conn)
{
int *num;
char name[10],telephone[11];
char ch;
char arg[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");
return 0;
}
}
free(num);
}
int main(void)
{
int *num;
MYSQL *conn = mysql_init(NULL); //初始化服务器句柄;
InitMysql(conn); //连接服务器、创建数据库
while(1)
{
switch(menu_main())
{
case 1:
num = PlayAll(conn, "select * from celltable;"); //显示所有联系人
free(num);
break;
case 2:
FindByName(conn);
break;
case 3:
FindByLikeName(conn);
break;
case 4:
InputInfo(conn);
break;
case 0:
mysql_close(conn);
puts("Quit!\n");
return 0;
default:
break;
}
}
return 0;
}
