How do I upload the sensor data from arduino into a database?

Question

I have just connected a Libelium shield to connect many sensors to Arduino UNO, that measure water ph level, temperature,...etc. I really couldn't figure out how do I upload the sensor data into a database(MySQL). I have the code for database connectivity, but still couldn't figure out how(Querying) to upload the sensor data into the database.

The main problem here is how do I make the arduino recognize the sensor data to upload it into database or send to to another Arduino or Raspberry pi through XBee.

Code for database connectivity:

void sendData(){
 Serial.println("Connecting...");
 if (my_conn.mysql_connect(server_addr, 3306, user, password)){
 delay(500);
 Serial.println("Starting SQL!");
 Serial.println(INSERT_SQL);
 my_conn.cmd_query(INSERT_SQL);
 Serial.println("Query Success!");
 my_conn.disconnect();
 Serial.println("\n");
 } 
 else {
 Serial.println("Connection failed!");
 }
}

• Andrew Allbright: You might be mixing concerns and you may benefit from decomposing your mental model of the situation a bit. You should have sensor reading components, a component that consumes the sensor data (does something with it, e.g. drop into DB), and your network layer. Ideal architecture (1) Arduino connected to sensor. Read in measurements of environment and transmit data via ZigBee (2) Arduino or, ideally, Raspberry Pi listens for messages on ZigBee mesh network. On message, sanitize data and insert into DB. Even better there is a queue process between "sanitize data" and "insert into DB"

Answer 1

First you have to create the table in your MySQL database, like:

CREATE TABLE IF NOT EXISTS `table_log` (
 `log_id` int(11) NOT NULL AUTO_INCREMENT,
 `log_date` date NOT NULL,
 `log_time` time NOT NULL,
 `log_t1` float NOT NULL,
 `log_t2` float NOT NULL,
 `log_t3` float NOT NULL,
 `log_t4` float NOT NULL,
 `log_vbatt` float NOT NULL,
 PRIMARY KEY (`log_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1; 

You can execute it in your phpmyadmin page (or create the table manually there). Above we have columns for auto-increment id ('log_id' - necessary), date & time, 4 temperature values and voltage value (modify these according your requirements).

Than the query to create a new record would be like this:

INSERT INTO `table_log` (`log_id`, `log_date`, `log_time`, `log_t1`, `log_t2`, `log_t3`, `log_t4`, `log_vbatt`) VALUES
 (0, '2016-03-04', '01:53:30', 1.0, 2.0, 3.0, 4.0, 3.0);

You need to create this string dynamically (with real values) and use instead of INSERT_SQL.