How to separate result values in different rows using Mysql query

Question

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)

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

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.

Jimbo · Accepted Answer · 2013-06-05 12:24:43Z

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

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() 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... *.
    }
}

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

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

Hope this helps :)

Collectives™ on Stack Overflow

How to separate result values in different rows using Mysql query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related