Sending data from phpMyAdmin database to Arduino [Part 2]

Hi there. I’ve been struggling with codes to access data from my phpMyAdmin database through Arduino. Basically, a string needs to be read from the database which consists of a table of one column named request. It contains multiple entries of the same string “Testing” in seven rows. I need to read the most recently updated one.

I’ve reached up till here. The below code is for reading a value from a database, modifying it and uploading back on the database. However, it is only capable of uploading the value on the database and instead of reading a value from the database, it is reading a value from any webpage(on changing the URL in the GET request from

client.println("GET /extract.php? HTTP/1.1");

to

client.println("GET /http://textfiles.com/100/ HTTP/1.1");

.
Thank God it is reading SOMETHING. You may refer to part 1 of this topic here: http://forum.arduino.cc/index.php?topic=528488.0 But please reply on this post from now on… Please help me…

Code for Arduino:

#include <SPI.h>
#include <Ethernet.h>

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
 
IPAddress ip(192,168,137,200);

char server[] = "192.168.137.1"; 

int interrupt=0;
String mod="Testing";


EthernetClient client;

void setup()
{
  
  Serial.begin(9600);
  
  Ethernet.begin(mac, ip);
  delay(5000);

}

void httpRequest()
{
  char c;
  int i=0;
  if (client.connect(server, 81)) 
  {
    Serial.println("Connection established 1");
    client.println("GET /http://textfiles.com/100/ HTTP/1.1");
    client.println("Host: 192.168.137.1"); 
    client.println("User-Agent: Arduino");
    client.println("Accept: text/html");
    client.println("Connection: close"); 
    client.println(); 
  }
  else 
  {
    Serial.println("Connection failed 1");
  }
    i=0;
    if(client.available() || client.connected())
    {
      while (client.available() || client.connected())
      {
        c = client.read();
        mod+=c;
        Serial.print(c);
      }
      Serial.println();
    }
    else
    {
      Serial.println("Server not responding");
    }
    client.stop();
    Serial.println(mod);
  }

void loop() 
{
   
  if(interrupt==0)
  {
      httpRequest();
      if (client.connect(server, 81)) 
      {
      Serial.println("Connection Established 2");
      client.print("GET /info.php?");
      client.print("request=");
      client.print(mod);
      client.println(" HTTP/1.1"); 
      client.println("Host: 192.168.137.1"); 
      client.println("Connection: close"); 
      client.println(); 
      client.println(); 
      client.stop();
      }
      else
      {
        Serial.println("Connection failed 2");
      }
  }
  interrupt++;
  delay(10000);
}

Output from the serial monitor:

Connection established 1
⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮HTTP/1.1 403 Forbidden
Date: Sun, 11 Feb 2018 22:38:27 GMT
Server: Apache/2.4.29 (Win32) OpenSSL/1.1.0g PHP/7.2.1
Vary: accept-language,accept-charset
Accept-Ranges: bytes
Connection: close
Transfer-Encoding: chunked
Content-Type: text/html; charset=utf-8
Content-Language: en

cb
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="
e
en" xml:lang="
15
en">
<head>
<title>
39
Access forbidden!</title>
<link rev="made" href="mailto:
117
postmaster@localhost" />
<style type="text/css"><!--/*--><![CDATA[/*><!--*/ 
    body { color: #000000; background-color: #FFFFFF; }
    a:link { color: #0000CC; }
    p, address {margin-left: 3em;}
    span {font-size: smaller;}
/*]]>*/--></style>
</head>

<body>
<h1>
1d
Access forbidden!</h1>
<p>

6


  
95


    You don't have permission to access the requested directory.
    There is either no index document or the directory is read-protected.

  
4



b
</p>
<p>

49
If you think this is a server error, please contact
the <a href="mailto:
26
postmaster@localhost">webmaster</a>.

14

</p>

<h2>Error 
23
403</h2>
<address>
  <a href="/">
21
192.168.137.1</a>

  <span>
55
Apache/2.4.29 (Win32) OpenSSL/1.1.0g PHP/7.2.1</span>
</address>
</body>
</html>

2


0


Testing⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮HTTP/1.1 403 Forbidden
Date: Sun, 11 Feb 2018 22:38:27 GMT
Server: Apache/2.4.29 (Win32) OpenSSL/1.1.0g PHP/7.2.1
Vary: accept-language,accept-charset
Accept-Ranges: bytes
Connection: close
Transfer-Encoding: chunked
Content-Type: text/html; charset=utf-8
Content-Language: en

cb
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="
e
en" xml:lang="
15
en">
<head>
<title>
39
Access forbidden!</title>
<link rev="made" href="mailto:
117
postmaster@localhost" />
<style type="text/css"><!--/*--><![CDATA[/*><!--*/ 
    body { color: #000000; background-color: #FFFFFF; }
    a:link { color: #0000CC; }
    p, address {margin-left: 3em;}
    span {font-size: smaller;}
/*]]>*/--></style>
</head>

<body>
<h1>
1d
Access forbidden!</h1>
<p>

6


  
95


    You don't have permission to access the requested directory.
    There is either no index document or the directory is
Connection Established 2

PHP Code for retrieving a value from database :

<?php

    $dbusername = "request";  
    $dbpassword = "request"; 
    $server = "localhost"; 

    $dbconnect = mysqli_connect($server, $dbusername, $dbpassword);
    $dbselect = mysqli_select_db($dbconnect, "request");

 $sql="SELECT * FROM request";

 $records=mysqli_query($dbconnect,$sql);
 
 $request=mysqli_fetch_assoc($records);
 
 return $request; 
 
?>

PHP code for uploading a value on the database:

<?php
   
    $dbusername = "request";  
    $dbpassword = "request";  
    $server = "localhost"; 
  
    $dbconnect = mysqli_connect($server, $dbusername, $dbpassword);
    $dbselect = mysqli_select_db($dbconnect, "request");
   
 $request= $_GET['request'];

    $sql = "INSERT INTO request.request (request) VALUES ('$request')";    
 
    mysqli_query($dbconnect, $sql);

?>

Please help. A value needs to be read, modified and then uploaded back to the database. The problem in reading the value surely lies in the PHP code for retrieving.

This is your problem.

  client.println("GET /http://textfiles.com/100/ HTTP/1.1");

No protocol or server in this. Just the folder and page,

  client.println("GET /100/ HTTP/1.1");

I’m not sure about the page. You might want to specify it in this GET line.

Edit: …and this is causing the backwards ‘?’ in the response.

      while (client.available() || client.connected())
      {
        c = client.read();
        mod+=c;
        Serial.print(c);
      }

I changed it from:

http://textfiles.com/100/

to:

client.println("GET /htdocs/extract.php? HTTP/1.1");

Is it right? It's still not working though.

Edit: ...and this is causing the backwards '?' in the response.

Actually this where the retrieved string is being modified which is necessary for the logic.. I'll find another way though.

That is not the correct way to retrieve the response. That zoomkat's way, and it doesn't work. I showed you the correct way in your other thread in Networking.

Are you certain this is the server?
192.168.137.1

Can you access this with a web browser?
http://192.168.137.1/htdocs/extract.php?

I showed you the correct way in your other thread in Networking.

Yes and I'm following that by giving the server some time to execute the GET request before closing the connection.

Are you certain this is the server? 192.168.137.1

Yes, I'm sure. It's the address that is displayed in front of the IPv4 connectivity in the details of the ethernet status.

I'm also sure bcoz it was while using this address that I saw all that crap displayed on my serial monitor... Which means that it was reading from the URL entered in the GET request.

Can you access this with a web browser? http://192.168.137.1/htdocs/extract.php?

Nope... It gives 404. I'm using this instead: http://localhost:81/extract.php