how to read data from mysql using ethernet shield arduino

Using Arduino Networking, Protocols, and Devices
1

Hello,

I want to read data from mysql using arduino uno and ethernet shield. I want to get string from mysql table selected by id and print it to serial monitor. I don't get the string I want, but the serial monitor print like that:

connecting...
connected
disconnecting.
br /

this is my arduino sketch :

#include <SPI.h>

#include <Dhcp.h>
#include <Dns.h>
#include <Ethernet.h>
#include <EthernetClient.h>
#include <EthernetServer.h>
#include <EthernetUdp.h>

byte mac[] = {
  0xDE,0xAD,0xBE,0xEF,0xFE,0xED
};

IPAddress ip(192, 168, 1, 177);
//IPAddress gateway(192, 168, 1, 10);
IPAddress server(192, 168, 1, 10);
EthernetClient client;

char inString[32]; // string for incoming serial data
int stringPos = 0; // string index counter
boolean startRead = false; // is reading?

void setup() {
  // put your setup code here, to run once:
Ethernet.begin(mac, ip);
  Serial.begin(9600);
}

void loop() {
  // put your main code here, to run repeatedly:
String pageValue = connectAndRead(); //connect to the server and read the output

  Serial.println(pageValue); //print out the findings.

  delay(5000); //wait 5 seconds before connecting again
}

String connectAndRead(){
  //connect to the server

  Serial.println("connecting...");
  delay(15000);

  //port 80 is typical of a www page
  if (client.connect(server, 80)) {
    Serial.println("connected");
    client.print("GET /GetData.php?id=1");
    client.print("HTTP/1.1\r\n");
  client.print("Host: 192.168.1.10\r\n");
    client.println();

    //Connected - Read the page
    return readPage(); //go and read the output

  }else{
    return "connection failed";
  }

}

String readPage(){
  //read the page, and capture & return everything between '<' and '>'

  stringPos = 0;
  memset( &inString, 0, 32 ); //clear inString memory

  while(true){

    if (client.available()) {
      char c = client.read();
      if (c == '<' ) { //'<' is our begining character
        startRead = true; //Ready to start reading the part 
      }else if(startRead){

        if(c != '>'){ //'>' is our ending character
          inString[stringPos] = c;
          stringPos ++;
        }else{
          //got what we need here! We can disconnect now
          startRead = false;
          client.stop();
          client.flush();
          Serial.println("disconnecting.");
          return inString;

        }

      }
    }

  }

}

And this is my php code :

<?php 

$id = $_GET['id']; 

require_once('koneksi.php'); 

$sql = "SELECT * FROM tb_room WHERE id=$id"; 

$r = mysqli_query($con,$sql); 

$row = mysqli_fetch_array($r); 
  
echo '<'.json_encode($row['status']).'>'; 
mysqli_close($con); 
?>

why serial monitor print "br /"? why serial monitor not print the string like "ON" or "OFF from mysql? Please help me. Thank you.

2 
    client.print("GET /GetData.php?id=1");
    client.print("HTTP/1.1\r\n");

ThereneedstobeaspacebetweenthescriptanddataandtheHTTPpart.

Why do you want to wrap the value fetched from the database in JSON?

Why do you need to return a global variable, of type char array, as a String, from readPage()?

3

I have changed my php code like this:

<?php 
define('HOST','localhost'); 
define('USER','root'); 
define('PASS',''); 
define('DB','db_unpri'); 
$con = mysqli_connect(HOST,USER,PASS,DB) or die('Unable to Connect');

$id = $_GET['id']; 

$sql = "SELECT status FROM tb_room WHERE id=$id"; 

$result = $con->query($sql);
$row = $result->fetch_assoc();
echo $row["status"];
$con->close();

?>

But, the result in serial monitor also same as before and about the arduino sketch, that is not my own sketch, I found it from google to test it. I'm new in using ethernet shield. So, I don't really understand about this sketch. Could you give me an example sketch to read data from mysql?

4

But, the result in serial monitor also same as before

If you haven't fixed the Arduino code, you are still not generating a valid GET request. The php script is not being called, so changing it makes no sense.

5

ThereneedstobeaspacebetweenthescriptanddataandtheHTTPpart.

which script, data and HTTPpart do you mean?

6

You are missing a space, and the server is returning a 400 error.

// change this
    client.print("HTTP/1.1\r\n");
// to this
    client.print(" HTTP/1.1\r\n");

You are also going to overflow your 32 byte array or get the wrong value.