How to receive data from my server database to a variable in my Arduino?

Question

I am learning to work with the client - server communication. I am able to communicate with my server and able to store values in the table. But I want to receive one of the specific data from the database.

Here I want to get the 'abc' value from the 'Current' column to store in a variable in my Arduino.

Below is the code that I have done until now. I would be really happy if you could solve the problem.

Here is my database structure

--------------------------------------------------- 
| Device |  Previous |  Next |  Distance |  Current| 
---------------------------------------------------
|katup123| xyz       | abc   | 2.600     | abc     |    
---------------------------------------------------

Here is my PHP code:

locator.php

<?php
$con=mysqli_connect("your_domain.com","peter","abc123","locate");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT Current FROM locate WHERE Device='katup123'");

while($row = mysqli_fetch_array($result)) {
  echo $row['Current'];
  echo "<br>"; 
}
?> 

Here is my Arduino code:

// Include the GSM library
#include <GSM.h>
#define PINNUMBER ""
// APN data
#define GPRS_APN       "GPRS_APN" // replace your GPRS APNgpsll
#define GPRS_LOGIN     "login"    // replace with your GPRS login
#define GPRS_PASSWORD  "password" // replace with your GPRS password
// initialize the library instance
GSM gsmAccess;
GSMClient client;
GPRS gprs;
// URL, path & port (for example: arduino.cc)
char server[] = "your_domain.com";
char path[] = "/locater.php";
int port = 80; // port 80 is the default for HTTP

void setup() {
  Serial.begin(9600);
  Serial.println("Starting Arduino web client.");
  // connection state
  boolean notConnected = true;
  // Start GSM shield
  // If your SIM has PIN, pass it as a parameter of begin() in quotes
  while(notConnected) {
    if((gsmAccess.begin(PINNUMBER)==GSM_READY) & (gprs.attachGPRS(GPRS_APN, GPRS_LOGIN, GPRS_PASSWORD)==GPRS_READY))
      notConnected = false;
    else {
      Serial.println("Not connected");
      delay(1000);
    }
  }
  Serial.println("GSM initialized");
  Serial.println("connecting...");
}

void loop() {
  char result [20];
  Serial.print(" Connecting to server Database ");
  if (client.connect(server, port)) {
    client.print("GET /locator.php?");
    Serial.print("GET /locator.php?");
    client.println(" HTTP/1.1");
    Serial.println(" HTTP/1.1");
    client.println("Host: www.your_domain.com");
    Serial.println("Host: www.your_domain.com");
    client.println("User-Agent: Arduino");
    Serial.println("User-Agent: Arduino");
    client.println("Accept: text/html");
    Serial.println("Accept: text/html");
    client.println("Connection: close");
    Serial.println("Connection: close");
    client.println();
    Serial.println();
    Serial.println("\nCOMPLETE!\n");
    client.stop();
  } else {
    Serial.println("connection failed");
    Serial.println("\n FAILED!\n");
  }
  delay(5000);
}

Here I want to store the 'abc' value to my 'result' variable. I don't know how to do it.

Answer 1

After you send the GET request, you read the response to fill the char result [20] variable you declared:

int i=0;
...
if (client.connect(server, port))
{
  client.print("GET /locator.php?");
  ...
  Serial.println("\nCOMPLETE!\n");

  // Read response from server
  i=0;
  while (client.available())
  {
    result[i] = client.read();
    Serial.print(result[i]);
    i++;
  }

  client.stop(); // disconnect
}

This is an example, result will contain the text output by locator.php. You will need to tweak this code to apply to your case. The above code has no error checking of any kind. Also, you should perform a check and only make the web call one time.

In locator.php you should replace echo "<br/>"; with echo "\n";. And for good measure, make sure there is no white space before <?, and remove the trailing ?>.

Answer 2

If you have your Arduino on tcp/ip your answer can be found in O'Reilly's Arduino Cookbook: Recipe 15-4. As Sourcery mentioned you need to make a new page on the PHP side that spits out just the data you are looking for and then use a web client on the Arduino side to download that data.

If you don't have your Arduino on the network then you have to stick the data over the serial line. One way would be to bake it into your source and recompile/reburn the Arduino. A smaller hammer would be to build-in something to your Arduino sketch that can deal with getting the data via USB serial and have a shim on the computer side to pull the data from PHP and send it to the Arduino. Serial communication with Arduino and Processing shows something similar where he is running Processing on the computer which is passing data to the Arduino.