Pages: [1] 2 3 ... 7   Go Down
Author Topic: Connect Arduino and phpMyadmin  (Read 12513 times)
0 Members and 1 Guest are viewing this topic.
Greece
Offline Offline
Newbie
*
Karma: 0
Posts: 24
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset


Hi guys. I am new to arduino and i want to make a project for my university, using Temperature Sensors and uploading the results to phpMyadmin. I am checking everywhere to find a solution but i have totally "crushed"!!!
The problem is that while it seems to be a connection between arduino sketch and phpMyadmin in my database fields, the sketch returns only 0.00 and i can't find the problem. I thank you all in advance. Every possible answer is thankfully accepted.

Arduino Sketch Code:

Code:
#include <SPI.h>

#include <Ethernet.h>           //library for ethernet functions
#include <HTTPClient.h>             //library for client functions
#include <DallasTemperature.h>  //library for temperature sensors

#include <OneWire.h>            //library for the onewire bus

#define ONE_WIRE_BUS     2      //the onewire bus is connected to pin 2 on arduino
#define TEMPERATURE_PRECISION  10 //resolution of the sensors is set to 10bit

// Ethernet settings
uint8_t hwaddr[6] = {XXXX, XXXX, XXXX, XXXX, XXXX, XXXX}; // mac-adress of arduino
uint8_t ipaddr[4] = {X, X, X, X };                    // IP-adress of arduino
uint8_t gwaddr[4] = {X, X, X, X};                     // IP-adress of gateway ( for later DNS implementation)
uint8_t subnet[4] = {255, 255, 255, 0};                   // subnetmask          
uint8_t serverip[4] = {X, X, X, X};                   // IP-adress of server arduino sends data to

uint8_t serverport = 80;                                  // the port the arduino talks to

EthernetClient client;                      

OneWire oneWire(ONE_WIRE_BUS);                            // setup a oneWire instance to communicate with any OneWire devices
                                                          // (not just Maxim/Dallas temperature ICs)
DallasTemperature sensors(&oneWire);                      // Pass our oneWire reference to Dallas Temperature

int numSensors;                                           // variable to store the number of sensors

bool connected = false;                                   // yes-no variable (boolean) to store if the arduino is connected to the server
int i = 0;                                                // variable to count the sendings to the server

void setup(void)                                          // setup-function runs at the startup of the arduino
{

Serial.begin(9600);                                       // start the serial port
Serial.println("I2C-to-Ethernet Bridge.");
Serial.println("Initializing Ethernet.");

Ethernet. begin(hwaddr, ipaddr);                          // start up ethernet
sensors.begin();                                          // start up the library

Serial.println("Enumerating and scanning for I2C sensors.");

numSensors = sensors.getDeviceCount();             // store the number of sensors to the variable      //"sensors.getDeviceCount" is a function in the library

if(numSensors > 0)                                        // if there is at least one sensor:
{
Serial.print("Enumerated ");                              //print the number of sensors to serial port
Serial.print(numSensors);
Serial.println( " sensors.");

}
else                                                      //if there is no sensor:
{
Serial.println("No sensors enumerated.");                 // tell the serial port
}

}

void loop(void)                                           // loop function runs over and over again
{
if(!connected)   {                                        // if "not" connected print: not connected ;)
//Serial.println("Not connected");

if (client.connect(serverip,80)){                                    // if connected, set variable connected to "true" and
connected = true;
sensors.requestTemperatures();                            // send the request for temperature to sensors (all sensors)

for(i=0; i<numSensors; i++)                               // as long as "i" ( chosen number, starts at 0) is smaller than
                                                          //"numSensors" (number of sensors) do the "for" cycle
{
float temp1 = sensors.getTempCByIndex(0);                    // take temperature reading from sensor "i" and store it to the variable "temp"
Serial.print("Temp is ");                                 // just to see if the reading was succesful in serial console
Serial.println(temp1);
Serial.println("Sending to Server: ");                    // all the "Serial.print" is for debugging and to show other people what arduino does
client.print("GET http://localhost/mysql_connect.php?temp1=");   // send this to apache on server to call "
Serial.print("GET http://localhost/mysql_connect.php?temp1=");            //
client.print(temp1);
Serial.print(temp1);
client.println(" HTTP/1.1");                  //
Serial.println(" HTTP/1.1");                  //
client.println("Host: x.x.x.x");    //
Serial.println("Host: x.x.x.x");    //
client.println("User-Agent: arduino-ethernet");        // ethernet related stuff
Serial.println("User-Agent: arduino-ethernet");        //
client.println("Accept: text/html");          //
Serial.println("Accept: text/html");          //
client.println("Connection: close");        //
//Serial.println("Connection: close");        //
client.println();                             //
Serial.println();
delay(20000);                                            // send the temperature every 20 sec
}
}
else{
Serial.println("Cannot connect to Server");               //  else block if the server connection fails (debugging)

}
}
else {

delay(500);                                               //
while (client.connected() && client.available()) {        // when connected and availabe:
char c = client.read();                                   // read the answer of the server and
Serial.print(c);                                          // print it to serial port
}                                                         //
Serial.println();                                         //
client.stop();                                            //  stop the connection and set
connected = false;                                        //  "connected" to false

}
}


Also i upload the php file that (i believe!) connects arduino and phpMyadmin (file name is mysql_connect.php)
 
P.S: At start my database(named temp_database) has only one table (named temperatures) and only one column (named temp1)


PHP CODE

Code:
<?php

if (isset($_GET['temp1'])) 
{
$temp1 floatval ('temp1');
$temp1 $_GET['temp1'];



$con mysql_connect("host","username","password");
if (!
$con)
   {
   die(
'could not connect: ' .mysql_error());
   }
   
mysql_select_db("temp_database"$con);

$sql="INSERT INTO temperatures (temp1) VALUES ('$_GET[temp1]')";

echo 
"inserted correctly \n";

if (!
mysql_query($sql,$con))
   {
   die(
'Error: ' mysql_error());
   }
   
if (
mysql_query($sql,$con)){   
echo 
"1 record added";
}
mysql_close($con);
}
?>


Thank you very much again
« Last Edit: September 25, 2012, 05:35:30 am by chris87 » Logged

Miramar Beach, Florida
Offline Offline
Faraday Member
**
Karma: 148
Posts: 6112
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Your code has some errors in it that will cause problems. This code downloads Google home every 10 seconds.
http://arduino.cc/forum/index.php/topic,99629.msg754629.html#msg754629
Change the network settings to yours and the server ip to your apache server. Once you get the apache home page downloading every 10 seconds, it will be easier to modify the code to interface with your php page.
Logged

Global Moderator
Offline Offline
Brattain Member
*****
Karma: 498
Posts: 19060
Lua rocks!
View Profile
WWW
 Bigger Bigger  Smaller Smaller  Reset Reset

First things first ...

Please edit your post, select the code, and put it between [code] ... [/code] tags.

You can do that by hitting the # button above the posting area.

And please do that for future posts. Thanks!
Logged


Dubuque, Iowa, USA
Offline Offline
Faraday Member
**
Karma: 49
Posts: 2544
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Code:
client.print("GET http://localhost/mysql_connect.php?temp1=");   // send this to apache on server to call "
Should be:
Code:
client.print("GET /mysql_connect.php?temp1=");   // send this to apache on server to call "

Also suggest you use "HTTP/1.0" instead and skip the "User-Agent", "Accept", and "Connection: close:" lines; HTTP/1.0 doesn't use persistent connections so "Connection: close" is unnecessary and the User-Agent and Accept headers are always unnecessary. Also, using a "Host:" header with an IP address is goofy and you should skip that as well; the intent of the "Host:" header is to use a hostname so the web server can run name-based virtual hosting when multiple domain names resolve to a single IP, but again that doesn't make sense with an IP address.

As an added option if you skip the "HTTP/1.0" string entirely your response from the web server will have no headers with it; just content.

Code:
$sql="INSERT INTO temperatures (temp1) VALUES ('$_GET[temp1]')";
You have single quotes around your $_GET[temp1] value; are you storing the temp in a text (not numeric) field type?
Logged

Seattle, WA USA
Offline Offline
Brattain Member
*****
Karma: 634
Posts: 50243
Seattle, WA USA
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Just for the record, phpMyAdmin is an application written in php that administers a MySQL database. Your other php scripts that the Arduino is executing have nothing to do with phpMyAdmin.

With the code you have, suppose you add another sensor. The data from two sensors is going to be mixed in your database. Is that a reasonable thing to do?
Logged

Miramar Beach, Florida
Offline Offline
Faraday Member
**
Karma: 148
Posts: 6112
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

(snip) With the code you have, suppose you add another sensor. The data from two sensors is going to be mixed in your database. Is that a reasonable thing to do?
Why not? I have a radius database that has several hundred client's connection records, all mixed in one table. You can search those!  smiley-wink
Logged

Greece
Offline Offline
Newbie
*
Karma: 0
Posts: 24
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Firsty, thank you all very much for your answers.As i said, i have now started using Arduino and all the services that it provides and i just present the code and the problem that i come up against. My main problem is the use of $_GET inside arduino code and also inside php code. Should i put $_POST instead or it has no difference? I just start with data from one DS18B20 Temperature Sensor and i believe that with the same way i will add more
« Last Edit: September 25, 2012, 08:55:30 am by chris87 » Logged

Seattle, WA USA
Offline Offline
Brattain Member
*****
Karma: 634
Posts: 50243
Seattle, WA USA
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
Why not?
If one sensor is inside and the other is outside, I'd use two tables - one for inside and one for outside. Or, one table with another column to include location.

Just a bunch of temperatures doesn't seem too useful to me, if some are indoor temperatures and some are outdoor temperatures.
« Last Edit: September 25, 2012, 09:00:11 am by PaulS » Logged

Miramar Beach, Florida
Offline Offline
Faraday Member
**
Karma: 148
Posts: 6112
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

The GET is the same format for any http request. In it's simplest form with a temp1 variable assigned 25:
Code:
client.println("GET /mysql_connect.php?temp1=25 HTTP/1.0\r\n");
// then listen for a response from the server.
while(client.connected()) {
  while(client.available()) {
    Serial.write(client.read());
  }
}
// close the connection after the server closes its end
client.stop();
Your php code should be able to retrieve that value.

@PaulS: I would put the tempX string in the table with the reading, not just the reading. In that case, you are correct. If you did not store the sensor name, the values mixed without a way to sort them would be useless.

edit: That should have been client.println, not client.print. It needs a blank line to denote end-of-header. Now changed.
« Last Edit: September 25, 2012, 09:02:54 am by SurferTim » Logged

Dubuque, Iowa, USA
Offline Offline
Faraday Member
**
Karma: 49
Posts: 2544
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Should i put $_POST instead or it has no difference?
Stick with GET, and yes there is a difference in how you transmit data to the server if you're using POST. POST is a bit more complicated and the only time it's required is if your query string is over 65K characters.
Logged

Greece
Offline Offline
Newbie
*
Karma: 0
Posts: 24
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
You have single quotes around your $_GET[temp1] value; are you storing the temp in a text (not numeric) field type?
No, i want to store the temp in a float(4,2) field type. What have i done wrong?
Logged

Miramar Beach, Florida
Offline Offline
Faraday Member
**
Karma: 148
Posts: 6112
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

No single quotes around decimal, float, or double values.
$sql="INSERT INTO temperatures (temp1) VALUES (42.59)";
or
$sql="INSERT INTO temperatures (temp1) VALUES ($temp1)";
Logged

Greece
Offline Offline
Newbie
*
Karma: 0
Posts: 24
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

This is the part of Arduino sketch that transfers data to my database

Code:
void loop(void)                                           // loop function runs over and over again
{
if(!connected)   {                                        // if "not" connected print: not connected ;)
//Serial.println("Not connected");

if (client.connect(serverip,80)){                                    // if connected, set variable connected to "true" and
connected = true;
sensors.requestTemperatures();                            // send the request for temperature to sensors (all sensors)


float temp1 = sensors.getTempCByIndex(0);     // take temp reading from sensor "i" and store it to the variable "temp1"

Serial.print("Temp is ");                                 // just to see if the reading was succesful in serial console
Serial.println(temp1);
Serial.println("Sending to Server: ");                   
client.print("GET /mysql_connect.php?temp1=");       
Serial.print("GET /mysql_connect.php?temp1=");           
client.print(temp1);
Serial.print(temp1);
client.println(" HTTP/1.0");                 
Serial.println(" HTTP/1.0");                 
client.println();                             
Serial.println();
delay(20000);                                            // send the temperature every 20 sec
}


This is my mysql_connect.php file

Code:
<?php

if (isset($_GET['temp1'])) 
{
$temp1 floatval ('temp1');
$temp1 $_GET['temp1'];



$con mysql_connect("host","username","password");
if (!
$con)
   {
   die(
'could not connect: ' .mysql_error());
   }
   
mysql_select_db("temp_database"$con);

$sql="INSERT INTO temperatures (temp1) VALUES ($temp1)";

echo 
"inserted correctly \n";

if (!
mysql_query($sql,$con))
   {
   die(
'Error: ' mysql_error());
   }
   
if (
mysql_query($sql,$con)){   
echo 
"1 record added";
}
mysql_close($con);
}
?>


In this part of my php code:
Code:
$sql="INSERT INTO temperatures (temp1) VALUES ($temp1)";

when i just put a random float (e.g. 42.05) it works perfectly, but when i leave it just like above and "expect the communication" between Arduino and php i have the problem. I type in my browser
Code:
localhost/mysql_connect.php?temp1=
and i get the following message
Quote
Error: Column count doesn't match value count at row 1
Logged

Miramar Beach, Florida
Offline Offline
Faraday Member
**
Karma: 148
Posts: 6112
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

I tried this and it displayed the value I sent. I read and display the server response on the serial monitor using the code above.
Code:
<?
$temp1 = $HTTP_GET_VARS['temp1'];
?>
<html>
<body>
test server page<br>
<? echo('temp1 = ' . $temp1 . ' '); ?>
</body>
</html>
Try it a step at a time. Once it is printing the value you expect, then try to insert it into the database.

Logged

Greece
Offline Offline
Newbie
*
Karma: 0
Posts: 24
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Should i create a new .php file and enter this code inside and connect it to my Arduino Sketch or i adjust it somehow to my existed mysql_connect.php?
Again, thank you very much for your help
Logged

Pages: [1] 2 3 ... 7   Go Up
Jump to: