如何使用MySQL查询来分隔不同行的结果值 [英] How to separate result values in different rows using Mysql query

问题描述

我在SQL数据库中创建该表（成绩）（测试）

I created this table (results) in my sql database (test)

CREATE DATABASE `test`; 
USE `test`;
CREATE TABLE `results` (
`number` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`id_machine` int(10) unsigned NOT NULL,
`value` float NOT NULL,
`datetime` datetime NOT NULL,
PRIMARY KEY (`number`),
UNIQUE KEY `indice_UNIQUE` (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=100; 

我的外部设备给我的结果：

My external device gives me these results:

+DATA: 43 BYTES FROM 0000:0000 (045)
Machine_8: (T=22.22, HR=42.56, Dw=8.95, VCC=3.64V)

和用的strtok的用法，我得到这些结果的一些值将它们保存在数据库中：

and with the usage of strtok I get some values of these results to save them in the database:

Results: 8, 22.22, 42.56, 8.95, 3.64

我想在我的表保存这样我的数据：

I would like to save my data in my table in this way:

101, 8, 22.22, 2013-06-05 14:03:00
102, 8, 42.56, 2013-06-05 14:03:00
103, 8, 8.95, 2013-06-05 14:03:00
104, 8, 3.64, 2013-06-05 14:03:00

这是我在我的函数code直到现在

This is my code in my function until now

int learn_port2(int fd)
{
 MYSQL *conn;
 MYSQL_RES *res;
 MYSQL_RES *res1;
 MYSQL_ROW row;
 char *server = "127.0.0.1";
 char *user = "root";
 char *password = "***";  // got tot keep my data secret
 char *database = "test";

 conn = mysql_init(NULL);
 if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
{
    fprintf(stderr, "%s\n", mysql_error(conn));
    return -1;
    //finish_with_error(conn);
}
 int n, i;
 char buff[300];
 memset(buff, 0, sizeof(buff));
 for (int x = 0; x<1; x++)
 //for (;;)
  {
   char id_machine[35] = "";
   char temp[35] = "";
   char hum[35] = "";
   char dw[35] = "";
   char vol[45] = "";
   char* ptr;
   int i,nodo,nodo1;
   float temp, hum, dw, vcc;
   n=read(fd,buff,sizeof(buff));
   sleep(1);
   printf("%s", buff);
   printf("\n");

  if (buff[37] == 'N' || buff[38] == 'N' || buff[39] == 'N' || buff[40] == 'N' )
   {
    ptr = strtok(buff, "Machine_,=T:HR:DW:Vcc()");
    i = 0;

    while (ptr != NULL)
    {
        if (i == 9)
            strcat(id_machine, ptr); // copies Nodo
        if (i == 10)
            strcat(temp, ptr); // copies T
        if (i == 11)
            strcat(hum, ptr); // copies HR
        if (i == 13)
            strcat(dw, ptr); // copies DW
        if (i == 15)
            strcat(vol, ptr); // copies Vcc
        ptr = strtok(NULL, "Machine_,=T:HR:DW:Vcc()");
        i++;
    }

    printf("Results: %s, %s, %s, %s, %s\n", id_machine, temp, hum, dw, vol);
     }


char query[]="INSERT INTO results(`id_machine`,`value`,`valor`) VALUES(id_machine,'14',value)";


if(mysql_query(conn, query))

{
    fprintf(stderr, "%s\n", mysql_error(conn));
    return -1;
}

res = mysql_use_result(conn);
}
}  

如何修改炭查询[]有我想要的结果？或者，如果有一些例子是这样的。

How can I modify the char query[] to have the results which I want? Or if there are some examples like this.

推荐答案

要进行参数化查询，你可以使用类似下面的...

To make a parameterized query you could use something like the following...

该示例使用 vsnprintf（） 的 采取类似格式字符串和后参数个数可变一个printf打印查询到一个临时的缓冲区，然后缓冲传递给MySQL查询。通过这种方式，你可以得到一个paramaterized查询...

The example uses vsnprintf() to take a printf like format string and a variable number of arguments after it to print the query into a temporary buffer and then pass the buffer to the MYSQL query. In this way you can get a paramaterized query...

vsnprintf（）接受可变参数列表的 的va_list 的 ，也是缓冲区溢出的安全。

vsnprintf() accepts a variable parameter list, va_list and also is buffer-overflow safe.

#include <stdio.h>
#include <stdarg.h>
#include <string.h>

#define BUFSTRLEN 255

/* Returns -1 if the internal buffer was not large enough to hold the formatted
 * string, or a format error occurred. If neither condition occurred then the
 * result of the MYSQL query is returned...  */
int DoMysqlQuery(MYSQL *conn, char const *printfstring, ...)
{
  int len;
  va_list args;
  char buffer[BUFSTRLEN + 1];
  memset(buffer, '\0', sizeof(buffer));

  va_start(args, printfstring);
  len = vsnprintf(buffer, BUFSTRLEN, printfstring, args);
  va_end(args);

  /* Did the buffer print work ? */
  if( len < 0 || len >= BUFSTRLEN + 1 )
      return -1;

  return mysql_query(conn, buffer);
}


int main(int argc, char const *argv[])
{
    int result;
    MYSQL mysqlConn = ...;

    /* Example stuff to send to the function.... */
    char *id_machine= "8";
    char *value= "1234";
    char *valor= "1";

    /* Send the query */
    result = DoMysqlQuery(
        &mysqlConn, 
        "INSERT INTO results(`id_machine`,`value`,`valor`) VALUES(%s,'%s',%s);",
        id_machine, value, valor);

    if( result )
    {
        /* handle error etc... *.
    }
}

在这个例子中，将被发送到的mysql_query查询字符串（）是：

In this example, the query string that will be sent to mysql_query() is:

INSERT INTO的结果（ id_machine ，值，英勇）VALUES（8，'1234'，1）;

INSERT INTO results(id_machine,value,valor) VALUES(8,'1234',1);

希望这有助于：）

这篇关于如何使用MySQL查询来分隔不同行的结果值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！