Sending data from phpMyAdmin database to Arduino

Projects General Guidance
1

Hi guys. I'm stuck again. Need your help. I need to send the last updated value of a column from a table on my database to the Arduino to remodify it and send it back to the database. Here's is a part of my arduino code..

void httpRequest()

{

  client.stop();

  char result[20];

  int i=0;

  if (client.connect(server, 81)) 

  {

    client.println("GET /extract.php? 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(); 

    i=0;

    if(client.available())

    {

      while (client.available())

      {

        result[i] = client.read();

        Serial.print(result[i]);

        i++;

      }

      client.stop();

    }

    else

    {

      Serial.println("Server not responding");

    }

  }

    else 

    {

      Serial.println("connection failed");

    }

}

Everytime I run my full code, it displays 'Server not responding' which means that the value I'm trying to fetch is not available on the server. Here's my full php code:

<?php



    // Prepare variables for database connection
   
    $dbusername = "tester";  
    $dbpassword = "tester";  
    $server = "localhost"; 


    // Connect to your database


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


    // Prepare the SQL statement

$result = mysqli_query($dbconnect,"SELECT stock FROM results WHERE pname='MacbookAir'");
while($row = mysqli_fetch_array($result))
{
  echo $row['stock'];
 echo "\n"; 
}

It has been modified a lot according to the suggestions I found on youtube and stack overflow and is still not working. I guess the problem is somewhere in the logic part of it which I can't figure out bcoz I'm totally new to it. Please help. :frowning:

The table on my database has six columns and the last one named 'stock' needs to be read off its last updated value.

2

Post the entire script. What is "server", and is the "server" listening on port 81?

3

Yes, it's listening to port 81 bcoz at 80 it gave an error that the port is already being used by another program so I migrated it and the SSL port to 81 and 444 respectively.

4

OK, and where is the rest of your sketch? What you posted won't compile.

5

Oh yes, it's here:

#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 result[20];
  int i=0;
  if (client.connect(server, 81)) 
  {
    Serial.println("Connection established 1");
    client.println("GET /extract.php? 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(); 
    i=0;
    if(client.available())
    {
      while (client.available())
      {
        result[i] = client.read();
        Serial.print(result[i]);
        i++;
      }
    }
    
    else
    {
      Serial.println("Server not responding");
    }
    client.stop();
    
    for(int j=0;j<i+1;j++)
    mod+=result[i];
    Serial.println("Data read: "+mod);
  }
  else 
  {
    Serial.println("Connection failed 1");
  }
}

void loop() 
{
  httpRequest(); 
  if(interrupt==0)
  {
      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);
}
6

Hi, I have made some changes in the above code. Earlier, it wasn't compiling. Now it is. I have debugged it and given 'mod' variable a value. If the httpRequest() method reads a value from the database, it will concatenate the value to mod and resend it back to the database.

After executing the above code, I found that it isn't reading a value from the database but uploading the value of mod to the database. Still, I think that the problem lies in the PHP file.

PHP code for uploading is:

<?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);

?>

PHP code for retrieving is:

<?php

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

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

 $result = mysqli_query($dbconnect,"SELECT request FROM request WHERE request='Testing'");
 $row = mysqli_fetch_array($result) 
 return $row;
?>

The problem lies in this code for retrieving. Please help me with it.
A value needs to be read, then modified and lastly uploaded back to the database.

Hi, the issues mentioned in this part of the topic have been resolved but some new ones have popped up. Please refer to the second part of this topic here: Sending data from phpMyAdmin database to Arduino [Part 2] - Project Guidance - Arduino Forum