Mysql Overview

Moving From a Physical Sensor to MySql:

this tutorial covers the basics of taking data from a sensor and logging it on the web.

The Sensor

Your sensor/micro-controller must be connected to the internet via a device like a xport/wiport or have a connection to a computer. There is a enormous amount of information on building this part of the system, though the best overall reference is Tom Igoe's book: Making Things Talk.
Other useful links can be found on the main ITP datalogging site page

The Mysql Side

You need three main components here:

1. MYSQL database
2. URL accessible script to insert data into the MYSQL database
3. URL accessible script to retrieve data from the MYSQL database.

1.Getting a MYSQL DataBase
You can find information on setting up and managing a MYSQL database on the itp.nyu.edu server here.
Use PhpMyAdmin to setup and check your database, as it is the easiest way to get started.

For this tutorial we set up a very basic datalogging table called datalog with the following fields:

1. index (int, set to autoincrement)
2. value (float)
3. time (set to timestamp)

2.Posting data to the MYSQL DataBase
You need a way to access the MYSQL database via a public URL, and the easiest way to do this is to write a php script that you can post data to and the php script will then insert the data into the mysql database. (Shawn Van Every has a good tutorial via his redial class on basic php+MYSQL here and here)

Here is a simple php script called datapost.php.

        <?php
	//datapost.php
        //Get Data
        	$id_=$_GET["id"];
        	$value_=$_GET["value"];

        //Variables for Connection
        	$hostname = "NAME OF HOST";
        	$dbname = "YOUR NETID"   
        	$username = "YOUR NETID";
        	$password = "AS ASSIGNED TO YOU";

	    // Connect to the database 
        	$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
        	mysql_select_db($dbname, $mySql) or die(mysql_error());


	        $query = "insert into datalog (id, value, T) values ('".id_."','" . $value_ . "', NOW())";	       
	       echo($query);
	       // Execute the query, actually inserts the data
	        $result = mysql_query($query, $mySql);
	        echo $result
	?>

To post a value to datalog You connect to

in your web browser or connecting via an xport or through a program written in processing or another language.

3.Retrieving data from the MYSQL DataBase
To get the data out of the Mysql to display in a browser this php script will do the very basics:

	<?php data_retrieve.php
		//function to insert data from mysql database into php variable
		//from Shawn Van Every
		function sqlQuery($query)
		{       
			global $mySql;
			$data = null;
			$result = mysql_query($query, $mySql);                      
			# This set's up an associative array (key->value pair) for all of the data returned
			if (sizeof($result) > 0){
				$num_fields = mysql_num_fields($result);
				$row_cnt = 0;
				while ($row_data = mysql_fetch_array($result)) {
					for ($cnt = 0; $cnt < $num_fields; $cnt++) {
						$field_name = mysql_field_name($result, $cnt);
						$data[$row_cnt][$field_name] = $row_data[$cnt];
					}
					$row_cnt++;
				}
			}
			return $data;        
		} 

		//Variables for connection
			$hostname = "NAME OF HOST";
			$dbname = "YOUR NETID"   
			$username = "YOUR NETID";
			$password = "AS ASSIGNED TO YOU";

		$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
		mysql_select_db($dbname, $mySql) or die(mysql_error());

		$query = "select id, value, T from datalog";
		$data = sqlQuery($query, $mySql);

		//print data
		for($i=0; $i<sizeof($data); $i++)
		{
			$d = "id: ".$data[$i]['id']." value: ".$data[$i]['value']." time: ".$data[$i]['T']."\n"
			echo($d);	
		}	
	?>