PHP question - sending to mysql

I have a project going on in which I want to send arduino data to my mysql on a webserver. I have been succesfull in sending one value to the database, but my goal is to send four different values.

Here are the codes I’ve been using:

Arduino:

#include <SPI.h>
#include <WiFi.h>

// EDIT: Change the 'ssid' and 'password' to match your network
char ssid[] = "Eli.........";  // wireless network name
char password[] = "76....................."; // wireless password
int status = WL_IDLE_STATUS;
WiFiClient client;

// EDIT: 'Server' address to match your domain
char server[] = "www.xhost..........org"; // This could also be 192.168.1.18/~me if you are running a server on your computer on a local network.

// This is the data that will be passed into your POST and matches your mysql column
int yourarduinodata = 123;
String yourdatacolumn = "yourdata=";
String yourdata;

void setup() {
  Serial.begin(9600);
  
  connectWifi();

  // You're connected now, so print out the status
  printWifiStatus();
  
  postData();
}

void loop() {

}

void connectWifi() {
  // Attempt to connect to wifi network
  while ( status != WL_CONNECTED) {
    Serial.print("Attempting to connect to SSID: ");
    Serial.println(ssid);
    status = WiFi.begin(ssid, password);
    // Wait 10 seconds for connection
    delay(10000);
  }
}

void printWifiStatus() {
  // Print the SSID of the network you're attached to
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // Print your WiFi shield's IP address
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  // Print the received signal strength
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}

// This method makes a HTTP connection to the server and POSTs data
void postData() {
  // Combine yourdatacolumn header (yourdata=) with the data recorded from your arduino
  // (yourarduinodata) and package them into the String yourdata which is what will be
  // sent in your POST request
  yourdata = yourdatacolumn + yourarduinodata;

  // If there's a successful connection, send the HTTP POST request
  if (client.connect(server, 80)) {
    Serial.println("connecting...");

    // EDIT: The POST 'URL' to the location of your insert_mysql.php on your web-host
    client.println("POST /insert_mysql.php HTTP/1.1");

    // EDIT: 'Host' to match your domain
    client.println("Host: xhost..........org");
    client.println("User-Agent: Arduino/1.0");
    client.println("Connection: close");
    client.println("Content-Type: application/x-www-form-urlencoded;");
    client.print("Content-Length: ");
    client.println(yourdata.length());
    client.println();
    client.println(yourdata); 
  } 
  else {
    // If you couldn't make a connection:
    Serial.println("Connection failed");
    Serial.println("Disconnecting.");
    client.stop();
  }
}

read PHP:

<?php

foreach ($_REQUEST as $key => $value)
{
	if ($key == "yourdata") {
		$yourdata = $value;
	}
}

// EDIT: Your mysql database account information
$username = "myusername";
$password = "passpass";
$database = "mydatabase";
$tablename = "mytable";
$localhost = "localhost";

// Check Connection to Database
if (mysql_connect($localhost, $username, $password))
  {
  	@mysql_select_db($database) or die ("Unable to select database");

    // Next two lines will write into your table 'test_table_name_here' with 'yourdata' value from the arduino and will timestamp that data using 'now()'
    $query = "INSERT INTO $tablename VALUES ($yourdata,now())";
  	$result = mysql_query($query);
  } else {
  	echo('Unable to connect to database.');
  }

?>

and I also have a display-PHP for displaying my data table in a browser:

<html><head><title>MySQL Table Viewer</title></head><body>
<?php
$db_host = 'localhost';
$db_user = 'myuser';
$db_pwd = 'passpass';
$database = 'mydatabase';
$table = 'mytable';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table}");
if (!$result) {
    die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>
</body></html>

<!-- http://www.anyexample.com/programming/php/php_mysql_example__display_table_as_html.xml -->

So these codes work and I’m able to send one value to the server. But I would want to send four diffferent values…maybe in one string like this: value1,value2,value3,value4. Would that be a good way to send them?
However the bigger question is how to read them in PHP to my database. I’ve tried to modify the “foreach”-function in many ways.
I also think that the row $query = “INSERT INTO $tablename VALUES ($yourdata,now())”; would be a key thing to modify.

Now I’m really stuck and would appreciate some help from anyone with experience with these kind of things…this might be a easy beginner question but for me it is too difficult.

but my goal is to send four different values.

And the problem is?

Would that be a good way to send them?

First, I don't understand why you are using a POST command. I'd use a GET command, and supply all the values in the GET statement. But, whatever you put in the yourarduinodata String will be associated with the name in the yourcolumnname String (yourdata).

The name of the variable should provide you with a clue. The data that you want to end up with in yourdata should look like "columnNameOne=columnValueOne&columnNameTwo=columnValueTwo&columnNameThree=columnValueThree&columnNameFour=columnValueFour".

Then, in the PHP script, the foreach loop needs to be scrapped in favor of looking for named values in the $_REQUEST dictionary (that contains name = value pairs). The names are whatever you use for columnNameOne, columnNameTwo, columnNameThree, and columnNameFour.

Then, the INSERT statement needs to be changed to insert the data in the correct columns, by listing the column names to insert data into, and the VALUES section needs to be changed to list all 4 values.

mytable is pretty lousy name for your table. The table name should reflect the kind of data you actually store in it.

First of all thank you for your very quick response!

I use the POST command because I found an example code somewhere. I would like to use the GET-command instead if it is easier or better.

I modified my arduino-code so, that my output in total (client.print) is:

Host: www.xhost......com User-Agent: Arduino/1.0 Connection: close Content-Type: application/x-www-form-urlencoded; Content-Length: 23

column1=333&column2=444

I also modified my PHP-code so, that the writing part is now:

$query = "INSERT INTO $tablename (column1,column2) VALUES ($value1,$value2)";

which is correct, I suppose.

I modified the reading-part like this:

foreach ($_REQUEST as $key => $value) { if ($key == "value1") { $value1 = $value; }

if ($key == "value2") { $value2 = $value; }

}

..but i quess it is not correct. I would still need some help..the tablename is so lousy, because I changed it so that anyone could understand..the real tablename is in finnish and i quess not everyone could understand it :)

Edit: ..I would also appreciate help with setting up the GET-code

..but i quess it is not correct.

What does the PHP script do? What do you get for $value1 and $value2?

Are the columns in the table named column1 and column2?

the tablename is so lousy, because I changed it so that anyone could understand..the real tablename is in finnish and i quess not everyone could understand it :)

You got that right!

As I said I’m really a beginner…especially in PHP.

My goal is really just to send a few values from the arduino to my mysql database using PHP. At this point I’m experimenting with two values only. Now I have set these values (value1 and value2) to a certain value (value1=333 and value2=444), later on I will take the values from sensors.

Yes, I changed my table so that the colum names are column1 and column2. There are no further columns in my table.

As you told me to, I modified my output so that it’s form is : “columnName1=value1&columnName2=value2”…in this particular case: “column1=333&column2=444”.

My Php-script, which I posted in whole in my first post in this topic, should just read my arduino output and save the value1 and value2 into my table in column1 and column2. I haven’t managed to get it to work yet.

I’m quite confused how to read the output and how it is possible to read the separate values out of the stream. I know it is somehow possible with the foreach-function, but I don’t know how…

but I don't know how..

I don't either, for POST variables. If you were to switch to a GET request, I know how to deal with that.

    client.print("GET /insert_mysql.php?");
    client.print(yourdata); // name=value&name=value&name=value...
    client.println(" HTTP/1.1");

On the PHP side, $_REQUEST changes to $_GET. The name equal value pairs will be the ones following the ?

Ok, thank you again for your quick response! I'm going with GET instead of POST from now on.

I did the modifications you suggested to my arduino-code and it looks like this now:

#include 
#include 

// EDIT: Change the 'ssid' and 'password' to match your network
char ssid[] = "xxxxxxxxxxxx";  // wireless network name
char password[] = "xxxxxxxxxxxx"; // wireless password
int status = WL_IDLE_STATUS;
WiFiClient client;

// EDIT: 'Server' address to match your domain
char server[] = "www.mydomain.org"; // This could also be 192.168.1.18/~me if you are running a server on your computer on a local network.

// This is the data that will be passed into your POST and matches your mysql column
int yourarduinodata1 = 333;
String yourdatacolumn1 = "column1=";
String value1;
int yourarduinodata2 = 444;
String yourdatacolumn2 = "column2=";
String value2;
String yourdata;

void setup() {
  Serial.begin(9600);
  
  connectWifi();

  // You're connected now, so print out the status
  printWifiStatus();
  
  postData();
}

void loop() {

}

void connectWifi() {
  // Attempt to connect to wifi network
  while ( status != WL_CONNECTED) {
    Serial.print("Attempting to connect to SSID: ");
    Serial.println(ssid);
    status = WiFi.begin(ssid, password);
    // Wait 10 seconds for connection
    delay(10000);
  }
}

void printWifiStatus() {
  // Print the SSID of the network you're attached to
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // Print your WiFi shield's IP address
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  // Print the received signal strength
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}

// This method makes a HTTP connection to the server and POSTs data
void postData() {
  // Combine yourdatacolumn header (yourdata=) with the data recorded from your arduino
  // (yourarduinodata) and package them into the String yourdata which is what will be
  // sent in your POST request
  value1 = yourdatacolumn1 + yourarduinodata1;
  value2 = yourdatacolumn2 + yourarduinodata2;
  yourdata = value1 + "&" + value2;


  if (client.connect(server, 80)) {
    Serial.println("connecting...");
    client.println("GET /insert_mysql.php?");
    client.print(yourdata); // name=value&name=value&name=value...
    client.println(" HTTP/1.1");    
    client.println("Host: www.mydomain.org");
    client.println("User-Agent: Arduino/1.0");
    client.println("Connection: close");
    client.println("Content-Type: application/x-www-form-urlencoded;");
    client.print("Content-Length: ");
    client.println(yourdata.length());
    client.println();
    Serial.println(yourdata);
  
} 
  else {
    // If you couldn't make a connection:
    Serial.println("Connection failed");
    Serial.println("Disconnecting.");
    client.stop();
  }
}

Which lines in void Postdata are necessary after client.println(" HTTP/1.1") ? Does my code look correct?

I modified also my insert.php:

 $value)
{
    if ($key == "yourdata") {
        $yourdata = $value;
    }
}

// EDIT: Your mysql database account information
$username = "myusername";
$password = "xxxxxxxx";
$database = "database";
$tablename = "finnishtable";
$localhost = "localhost";

// Check Connection to Database
if (mysql_connect($localhost, $username, $password))
  {
   @mysql_select_db($database) or die ("Unable to select database");

    // Next two lines will write into your table 'test_table_name_here' with 'yourdata' value from the arduino and will timestamp that data using 'now()'
    $query = "INSERT INTO $tablename (column1, column2) VALUES (value1,value2)";
   $result = mysql_query($query);
  } else {
   echo('Unable to connect to database.');
  }

?>

So in this code I'm trying to read the output from arduino and to save the values into the table. I used GET instead of REQUEST. The two columns in my table are called "column1" and "column2".

I still don't understand, how the reading of the separated values from the read string is done.. I'm really thankful for the help so far, and I wish I could get this thing to work :)

    client.println("Content-Type: application/x-www-form-urlencoded;");
    client.print("Content-Length: ");
    client.println(yourdata.length());

Not necessary.

    if ($key == "yourdata") {
        $yourdata = $value;
    }

The names in get $_GET dictionary are the names that YOU set. yourdata is not one of those names. Your names were column1 and column2.

    $query = "INSERT INTO $tablename (column1, column2) VALUES (value1,value2)";

value1 and value2 are ?

When I use an INSERT statement, I use something like "INSERT INTO $tablename (column1, column2) VALUES('$clientID', '$enteredDate')";

Notice the single quotes around the variable names. The variables hold the values to be inserted into the table.

Hmm..I did all the modifications, but it still doesn't work..

My php now:

 $value)
{
    if ($key == "column1") {
        $column1 = $value1;
    }
    if ($key == "column2") {
        $column2 = $value2;
    }

}

// EDIT: Your mysql database account information
$username = "xxxxx";
$password = "xxxxxxxxx";
$database = "xxxxxxxx";
$tablename = "xxxxxxxxx";
$localhost = "localhost";

// Check Connection to Database
if (mysql_connect($localhost, $username, $password))
  {
   @mysql_select_db($database) or die ("Unable to select database");

    // Next two lines will write into your table 'test_table_name_here' with 'yourdata' value from the arduino and will timestamp that data using 'now()'
    $query = "INSERT INTO $tablename (column1, column2) VALUES ('$value1' , '$value2')";
   $result = mysql_query($query);
  } else {
   echo('Unable to connect to database.');
  }

Does it look correct now?

I have some code I use. Add these to the top of your php script (or in another file that you include in your php script):

function PrintPostVars($caller)
{
    $received = "Post vars\r\n\r\n";

    foreach($_POST as $key=>$value)
    {
        $received .= "$key = [$value]\r\n";
    }

    $printout = fopen("C:\\tmp\\post_variables.txt",'a');
    fwrite($printout, "Number of post variables in $caller: ");
    fwrite($printout, count($_POST));
    fwrite($printout, "\r\n");
    fwrite($printout, $received);
    fwrite($printout, "\r\n");
    fclose($printout);
}   

function PrintGetVars($caller)
{
    $received = "Get vars\r\n\r\n";

    foreach($_GET as $key=>$value)
    {
        $received .= "$key = [$value]\r\n";
    }

    $printout = fopen("C:\\tmp\\get_variables.txt",'a');
    fwrite($printout, "Number of get variables in $caller: ");
    fwrite($printout, count($_GET));
    fwrite($printout, "\r\n");
    fwrite($printout, $received);
    fwrite($printout, "\r\n");
    fclose($printout);
}   

function PrintSessionVars($caller)
{
    $received = "Session vars\r\n\r\n";

    foreach($_SESSION as $key=>$value)
    {
        $received .= "$key = [$value]\r\n";
    }

    $printout = fopen("C:\\tmp\\session_variables.txt",'a');
    fwrite($printout, "Number of session variables in $caller: ");
    fwrite($printout, count($_SESSION));
    fwrite($printout, "\r\n");
    fwrite($printout, $received);
    fwrite($printout, "\r\n");
    fclose($printout);
}

Then, call them:

    PrintPostVars("yourScriptName");
    PrintSessionVars("yourScriptName");
        PrintGetVars("yourScriptName");

You can change the path where the files get stored to match your system. The data written to the files is often helpful for debugging purposes.

OK, first... let's not be pedantic over column and table names. It's example code. Nobody should be criticizing generic names in example code. (If you put out production code with those kinds of names, you deserve to be beaten, of course. ;) Maintainability!)

Next, GET is not better than POST. OP, do you understand the difference between the two? I'm not being critical or sarcastic, there are important distinctions. Mainly it's about best security practices. Anything you put in a GET request is part of the URL:

http://www.example.com/myscript.php?var1=blah&var2=blaaaaah

This shows up in server logs, and when you go to that URL in your browser, it also shows up in the history. While that's nice for query strings that you want your user to be able to bookmark (e.g., www.mystore.com/view.php?itemid=1001234), it's not so good for private info (e.g., www.mybank.com/viewaccount.php?user=bob&pin=123456). Obviously, some of this doesn't apply to microcontroller applications, but understanding the consequences of particular methods is imperative to good programming habits.

Using POST, the content is part of the data stream and is (usually) not logged anywhere, and cannot be recalled through browser history. This is vital if security is of any concern. Also, POSTed data has no restriction on content length, except that imposed by the web server, PHP defaults, or the application script -- all tunable. GET requests are meant to be short. Plan on a maximum length of 255 characters. That's not always the limit, but it should be treated as such.

On the PHP side, the only difference to you is how you access the variable:

// Query string sent by HTTP GET
$var1 = $_GET['var1'];

// Form data sent by HTTP POST
$var2 = $_POST['var2'];

Next, you need to revise how you do SQL queries. Again, mostly because of security concerns. When you use a line like this:

$query = "SELECT `access_allowed` FROM `users` WHERE `username` = '" . $username . "' AND `password` = '" . $password . "'";

Realize that I, as a hacker, could give you input like this:

$username = "bob";
$password = "' OR 1=1 OR `password` = '";

And your SQL query just returned any row where username is bob and password = '', or 1 = 1, or password = ''. Since 1 = 1 always and forever, you just returned bob's user account without me having to know his password.

The proper solution to this is to use prepared queries with placeholders for the variables. (Also, I'm going to use the class methods rather than procedural, although you can do either...)

connect_errno) die ("Error connecting to database: (" . $db->connect_errno . ") "  . $db->connect_error());

// Do a select query
$q = $db->prepare( "SELECT * FROM `mytable` WHERE `column1` = ? AND `column2` = ?" );
if (!$q) die ("Unable to prepare query: " . $db->error);

$q->bind_param( 'si', $var1, $var2 );  // 's' stands for a string variable, 'i' would be an integer

if (!$q->execute()) die ("Failed to execute query: " . $db->error);

$q->bind_result( $column1, $column2, $column3 );

while (1) {
    $retval = $q->fetch();
    if ($retval === FALSE) die ("Failed to fetch query results: " . $db->error);
    if ($retval === NULL) break;  // No more rows
    
    echo "Column1 = " . $column1 . "; Column2 = " . $column2 . "; Column3 = " . $column3 . "\n";
}

$q->close();
$db->close();
?>

Now if I try to be sneaky and craft SQL queries into the data input, MySQL will search for those useless statements as actual data in the table. So unless your password actually IS "[u]' OR 1=1 OR password = '[/u]", bob gets nothing.

Moving on... your code here:

 $value)
{
    if ($key == "column1") {
        $column1 = $value1;
    }
    if ($key == "column2") {
        $column2 = $value2;
    }

}
?>

... will not work. You're assigning each key/value pair in the $_GET array to $key and $value, but then in your loop, you're setting $column1 = $value1. Note that $value1 didn't exist until that line. PHP lets you create variables out of nowhere (which is arguably the easiest thing a language can do to ensure there will be bugs in your code), so this is not a syntax error, but it is an error. You meant to do this:

 $value)
{
    if ($key === "column1") {
        $column1 = $value;
    }
    if ($key === "column2") {
        $column2 = $value;
    }

}
?>

Note also that you should use === here. This ensures that both $key and "column1" are both strings, and as strings, they are equal. Otherwise, you potentially run into subtle bugs (e.g.) comparing strings with integers, requiring the string to be evaluated as an integer, and becoming equal in a way you didn't intend.

It should be noted that your foreach loop here is unnecessary, as you can access the values directly (by using the query string variable name as an index to the array) instead of assigning them to scalars first. But it should work nonetheless.