Problem Connecting Arduino Ethernet Shield to MySQL Database

Hi everyone,

I am trying to upload pH value from analog ph meter kit(ph sensor) to a sql database.
I am using analog ph meter kit, arduino uno, arduino ethernet shield and RJ 45 cable.
I am using Xampp(https://http://www.localhost:8080/phpmyadmin/) database running on my PC. I have created a table(phvalue) in this database(phvalue). I have saved php files in my computer at C:xampp/htdocs/fyp. I have hooked up Ethernet Shield to one of internet ports. After all this, i don’t find table getting updated by pH values. Is there any mistake? Please guide me.
Here is the source code for Arduino:

/*
 # This sample code is used to test the pH meter V1.0.
 # Editor : YouYou
 # Ver    : 1.0
 # Product: analog pH meter
 # SKU    : SEN0161
*/
#define SensorPin A0            //pH meter Analog output to Arduino Analog Input 0
#define Offset 0.00            //deviation compensate
#define LED 13
#define samplingInterval 20
#define printInterval 800
#define ArrayLenth  40    //times of collection
#include <LiquidCrystal.h>
#include <SPI.h>
#include <Ethernet.h>
LiquidCrystal lcd(2,3,4,5,6,7);
int pHArray[ArrayLenth];   //Store the average value of the sensor feedback
int pHArrayIndex=0;   
byte mac[] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED}; 
IPAddress ip (10,6,2,25);
EthernetClient client;
IPAddress server (12,34,56,78);
int  interval = 5000;
void setup()
{
  pinMode(LED,OUTPUT);  
  Serial.begin(9600); 
  while (!Serial) {; }
  Ethernet.begin(mac, ip);
  

  Serial.print("LAN IP       : ");
  Serial.println(Ethernet.localIP());
  Serial.print("Subnet Mask       : ");
  Serial.println(Ethernet.subnetMask());
  Serial.print("Default Gateway IP: ");
  Serial.println(Ethernet.gatewayIP());
  Serial.print("DNS Server IP     : ");
  Serial.println(Ethernet.dnsServerIP());

  Serial.println("pH meter experiment!");  //Test the serial monitor
  lcd.begin(16, 2);
  lcd.print("pH value: ");
}
void loop()
{
  static unsigned long samplingTime = millis();
  static unsigned long printTime = millis();
  static float pHValue,voltage;
  if(millis()-samplingTime > samplingInterval)
  {
      pHArray[pHArrayIndex++]=analogRead(SensorPin);
      if(pHArrayIndex==ArrayLenth)pHArrayIndex=0;
      voltage = avergearray(pHArray, ArrayLenth)*5.0/1024;
      pHValue = 3.5*voltage+Offset;
      samplingTime=millis();
      if(millis() - printTime > printInterval)   //Every 800 milliseconds, print a numerical, convert the state of the LED indicator
  {
    Serial.print("Voltage:");
        Serial.print(voltage,2);
        Serial.print("    pH value: ");
    Serial.println(pHValue,2);
        digitalWrite(LED,digitalRead(LED)^1);
        printTime=millis();
  } 
 
  }
  
  if (client.connect(server, 8080)) {
    Serial.println("-> Connected");
    client.print( "GET c:/xampp/htdocs/fyp/add_data.php?"); 
    client.print("pHValue=");
    client.print( "pHValue" );
    client.println( "HTTP/1.1" );
    client.print( "Host: " );
    client.println( server );
    client.println( "Connection: close" );
    client.println();
    client.println();
    client.stop();
   }
  else {
    // you didn't get a connection to the server:
    Serial.println("--> connection failed");
  }
  delay(interval);
   {
  lcd.setCursor(7,1);
  lcd.print(pHValue);
  delay (100);
  }
}
double avergearray(int* arr, int number){
  int i;
  int max,min;
  double avg;
  long amount=0;
  if(number<=0){
    Serial.println("Error number for the array to avraging!/n");
    return 0;
  }
  if(number<5){   //less than 5, calculated directly statistics
    for(i=0;i<number;i++){
      amount+=arr[i];
    }
    avg = amount/number;
    return avg;
  }else{
    if(arr[0]<arr[1]){
      min = arr[0];max=arr[1];
    }
    else{
      min=arr[1];max=arr[0];
    }
    for(i=2;i<number;i++){
      if(arr[i]<min){
        amount+=min;        //arr<min
        min=arr[i];
      }else {
        if(arr[i]>max){
          amount+=max;    //arr>max
          max=arr[i];
        }else{
          amount+=arr[i]; //min<=arr<=max
        }
      }//if
    }//for
    avg = (double)amount/(number-2);
  }//if
  return avg;
}

Here is mysql_connect.php

<?php

DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', '');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'phvalue');

$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error());

@mysql_select_db (DB_NAME) OR die ('Could not select the database: ' . mysql_error() );
?>

here is the pHvalue.php

<?php
    // Start MySQL Connection
    include('mysql_connect.php');
?>

<html>
<head>
    <title>Arduino pH Value Log</title>
    <style type="text/css">
        .table_titles, .table_cells_odd, .table_cells_even {
                padding-right: 20px;
                padding-left: 20px;
                color: #000;
        }
        .table_titles {
            color: #FFF;
            background-color: #666;
        }
        .table_cells_odd {
            background-color: #CCC;
        }
        .table_cells_even {
            background-color: #FAFAFA;
        }
        table {
            border: 2px solid #333;
        }
        body { font-family: "Trebuchet MS", Arial; }
    </style>
</head>

    <body>
        <h1>Arduino pHvalue Log</h1>
    <table border="0" cellspacing="0" cellpadding="4">
      <tr>
            <td class="table_titles">ID</td>
            <td class="table_titles">pH Value</td>
            <td class="table_titles">Date and Time</td>
          </tr>
<?php
    // Retrieve all records and display them
   $query = "SELECT pHid,pHvalue,date FROM phvalue";
  $result = @mysql_query ($query);
    // Used for row color toggle
    $oddrow = true;

    // process every record
    while( $row = mysql_fetch_array($result) )
    {
        if ($oddrow)
        {
            $css_class=' class="table_cells_odd"';
        }
        else
        {
            $css_class=' class="table_cells_even"';
        }

        $oddrow = !$oddrow;

        echo '<tr>';
        echo '   <td'.$css_class.'>'.$row["pHid"].'</td>';
        echo '   <td'.$css_class.'>'.$row["pHvalue"].'</td>';
        echo '   <td'.$css_class.'>'.$row["date"].'</td>';
        echo '</tr>';
    }
?>
    </table>
    </body>
</html>

and lastly add_data.php

<?php
    // Connect to MySQL
    include("mysql_connect.php");

    // Prepare the SQL statement
    $SQL = "INSERT INTO phvalue.phvalue (pHid, pHvalue, date) VALUES (pHid,pHvalue,NOW() )"; ;

    // Execute SQL statement
    mysql_query($SQL);

    // Go to the review_data.php (optional)

    header("pHvalue.php");
?>

I presume there is an error somewhere. I suggest reading and displaying the response from the server. That may help you determine what is wrong. Here is a simple version with no error checking or fault tolerance:

  if (client.connect(server, 8080)) {
    Serial.println("-> Connected");
    client.print( "GET c:/xampp/htdocs/fyp/add_data.php?");
    client.print("pHValue=");
    client.print( "pHValue" );
    client.println( "HTTP/1.1" );
    client.print( "Host: " );
    client.println( server );
    client.println( "Connection: close" );
    client.println();

// add this
    while(client.connected()) {
      while(client.available()) {
        Serial.print(client.read());
      }
    }

    client.stop();
   }

Thanks for the reply, SurferTim. I have tried it but it not works. Arduino still cannot connect to the database server

So it says "connection failed" on the serial monitor? Are you certain all the network settings and IPs are correct? Are both the devices on the same localnet?

At first, it says “connection failed” but then it works because of the problem with IPs. But unfortunately even the connection have been connected, the reading of pH value still can’t be sent to database.

Here is the coding:

/*
 # This sample code is used to test the pH meter V1.0.
 # Editor : YouYou
 # Ver    : 1.0
 # Product: analog pH meter
 # SKU    : SEN0161
*/
#define SensorPin A0            //pH meter Analog output to Arduino Analog Input 0
#define Offset 0.00            //deviation compensate
#define LED 13
#define samplingInterval 20
#define printInterval 800
#define ArrayLenth  40    //times of collection
#include <LiquidCrystal.h>
#include <SPI.h>
#include <Ethernet.h>
LiquidCrystal lcd(2,3,4,5,6,7);
int pHArray[ArrayLenth];   //Store the average value of the sensor feedback
int pHArrayIndex=0;   
byte mac[] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED}; 

EthernetClient client;
char server[] = "http://localhost:8080/phpmyadmin/tbl_structure.php?";
IPAddress ip (10, 6, 2, 9);
int  interval = 5000;
void setup()
{
  pinMode(LED,OUTPUT);  
  Serial.begin(9600); 
  while (!Serial) {; }
  Ethernet.begin(mac);
  

  Serial.print("LAN IP       : ");
  Serial.println(Ethernet.localIP());
  Serial.print("Subnet Mask       : ");
  Serial.println(Ethernet.subnetMask());
  Serial.print("Default Gateway IP: ");
  Serial.println(Ethernet.gatewayIP());
  Serial.print("DNS Server IP     : ");
  Serial.println(Ethernet.dnsServerIP());

  Serial.println("pH meter experiment!");  //Test the serial monitor
  lcd.begin(16, 2);
  lcd.print("pH value: ");
}
void loop()
{
  static unsigned long samplingTime = millis();
  static unsigned long printTime = millis();
  static float pHvalue,voltage;
  if(millis()-samplingTime > samplingInterval)
  {
      pHArray[pHArrayIndex++]=analogRead(SensorPin);
      if(pHArrayIndex==ArrayLenth)pHArrayIndex=0;
      voltage = avergearray(pHArray, ArrayLenth)*5.0/1024;
      pHvalue = 3.5*voltage+Offset;
      samplingTime=millis();
      if(millis() - printTime > printInterval)   //Every 800 milliseconds, print a numerical, convert the state of the LED indicator
  {
    Serial.print("Voltage:");
        Serial.print(voltage,2);
        Serial.print("    pH value: ");
    Serial.println(pHvalue,2);
        digitalWrite(LED,digitalRead(LED)^1);
        printTime=millis();
  } 
 }
  if (client.connect(server, 8080)) {
    Serial.println("-> Connected");
    if(client.connected()){
    client.print( "GET /pHvalue.php? HTTP/1.1"); 
     client.print("db=");
    client.print( "phvalue" );
    client.print("&");
     client.print("table=");
    client.print( "phvalue" );
    client.print("&");
     client.print("server=");
    client.print( "1" );
    client.print("&");
    client.print("pHvalue=");
    client.print( A0 );
    client.println( "HTTP/1.1" );
    client.print( "Host: http://localhost:8080/phpmyadmin/tbl_structure.php" );
    client.println( "Connection: close" );
    client.println();
    client.println();
    while(client.connected()) {
      while(client.available()) {
        Serial.print(client.read());
      }
    }
    client.stop();
   }
  }
  else {
    // you didn't get a connection to the server:
    Serial.println("--> connection failed");
 }
   {
  lcd.setCursor(7,1);
  lcd.print(pHvalue);
  delay (100);
  }
}
double avergearray(int* arr, int number){
  int i;
  int max,min;
  double avg;
  long amount=0;
  if(number<=0){
    Serial.println("Error number for the array to avraging!/n");
    return 0;
  }
  if(number<5){   //less than 5, calculated directly statistics
    for(i=0;i<number;i++){
      amount+=arr[i];
    }
    avg = amount/number;
    return avg;
  }else{
    if(arr[0]<arr[1]){
      min = arr[0];max=arr[1];
    }
    else{
      min=arr[1];max=arr[0];
    }
    for(i=2;i<number;i++){
      if(arr[i]<min){
        amount+=min;        //arr<min
        min=arr[i];
      }else {
        if(arr[i]>max){
          amount+=max;    //arr>max
          max=arr[i];
        }else{
          amount+=arr[i]; //min<=arr<=max
        }
      }//if
    }//for
    avg = (double)amount/(number-2);
  }//if
  return avg;
}

This is not a legitimate url. localhost is only accessible in the server, not any remote device. And the protocol and requested page added will almost certainly cause a resolution fail.

char server[] = "http://localhost:8080/phpmyadmin/tbl_structure.php?";

You are evaluating the return value from client.connect() incorrectly. This is the correct way if you use a domain name rather than an IP. If you change your code to this, you will find the resolution of the domain name above will fail.

// change this
  if (client.connect(server, 8080)) {
// to this
  if (client.connect(server, 8080) == 1) {

Yes, when I tried to change to

 if (client.connect(server, 8080) == 1) {

the connection was fail. Then we try IP 127.0.0.1 (server)and the connection still fail. I'm sorry but what exactly the IP or server name I should put ? Can you give me some example.

You can't use 127.0.0.1 either. That is the same as localnet. It is only good on the server itself, not on any remote device.

You must use the IP assigned to the server's ethernet/wifi device as an external IP on the Arduino. It normally starts with 192.168.xx.xx or 10.xx.xx.xx if on your localnet.