//mysql_select
//gcc mysql_select.c -o test -I /usr/include/mysql -L /usr/lib -lmysqlclient

#include <stdio.h>
#include <string.h>
#include <mysql/mysql.h>

#define HOST "localhost"
#define USERNAME "SERVER"
#define PASSWORD "SERVER_PASSWORD"
#define DATABASE "remote_control"

int main()
{
//char *sql = "select * from devices";
char *sql = "select * from users";
int res;
int i, j;
int row, column;/*查询返回的行数和列数*/
MYSQL my_connection;
MYSQL_RES *res_ptr;/*指向查询结果的指针*/
MYSQL_FIELD *field;/*字段结构指针*/
MYSQL_ROW result_row;/*按行返回的查询信息*/

mysql_init(&my_connection);/*初始化mysql连接my_connection*/

if (!mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE, 3306, NULL, CLIENT_FOUND_ROWS))
{/*连接失败*/
printf("数据库连接失败: %sn", mysql_error(&my_connection));
return 0;
}

mysql_query(&my_connection, "set names utf8");/*设置编码为utf8*/

res = mysql_real_query(&my_connection, sql, (unsigned int)strlen(sql));
if (res)
{/*执行失败*/
printf("sql語句执行失败: %sn", mysql_error(&my_connection));
mysql_close(&my_connection);
return 0;
}

res_ptr = mysql_store_result(&my_connection);/*将查询的結果给res_ptr*/
if (!res_ptr)
{
printf("查询结果为空n");
mysql_close(&my_connection);
return 0;
}

column = mysql_num_fields(res_ptr);/*取得結果的列数和*/
row = mysql_num_rows(res_ptr);/*取得結果的行数和*/
printf("查询到 %d 行n", row);

for (i = 0; field = mysql_fetch_field(res_ptr); i++)/*输出結果的字段名*/
printf("%s ", field->name);
printf("n");

for (i = 0; i < row; i++)/*按行输出結果*/
{
result_row = mysql_fetch_row(res_ptr);
for (j = 0; j < column; j++)
printf("%s ", result_row[j]);
printf("n");
}

mysql_free_result(res_ptr);

mysql_close(&my_connection);

return 0;
}