Arduino to MySQL with PHP my own example and it does not work

Good evening gentlemen from the arduino forum. I have a question that I hope you can help me.

Let’s imagine I have the following code on arduino:

#include <WiFi.h> // For the Wifi Shield of Arduino

// MySQL database connection:
byte mac_addr = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; // It can be anything as long as it follows this structure
IPAddress server_addr(192,168,1,2); // IP of the MySQL server here (ipconfig in cmd and look IPV4)
char user = “dsm501a_project”; // MySQL user login username
char password = “12345”; // MySQL user login password

// Wifi connection
char ssid = “NETGEAR84”;
char pass = “coolspider508”;

WiFiClient client;

int A = 1;

void setup() {

Serial.begin(115200);
while (!Serial); // wait for serial port to connect. Needed for Leonardo only

// Begin WiFi section
int status = WiFi.begin(ssid, pass);
if ( status != WL_CONNECTED) {
Serial.println(“Couldn’t get a wifi connection”);
while(true);
}
else {
Serial.println(“Connected to network”);
IPAddress ip = WiFi.localIP();
Serial.print("My IP address is: ");
Serial.println(ip);
}
Serial.println(“Connecting…”);
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println(“Connection failed.”);

}

void loop() {

if (client.connect(server_addr, 80)) {
client.print(“GET /write_data.php?”);
client.print(“value=”);
client.print(A);
client.println(" HTTP/1.1");
client.println(“Host: 192.168.1.2”);
client.println(“Connection: close”);
client.println();
client.println();
client.stop();

}

else {

Serial.println("–> connection failed\n");
}

delay(10000);
A++;
}

and I have the following php code:

<?php // Prepare variables for database connection $dbusername = "dsm501a_project"; // enter database username, I used "arduino" in step 2.2 $dbpassword = "12345"; // enter database password, I used "arduinotest" in step 2.2 $server = "localhost"; // IMPORTANT: if you are using XAMPP enter "localhost", but if you have an online website enter its address, ie."www.yourwebsite.com" // Connect to your database $dbconnect = mysql_pconnect($server, $dbusername, $dbpassword); $dbselect = mysql_select_db("test_arduino",$dbconnect); // Prepare the SQL statement $sql = "INSERT INTO test_arduino.test (Value) VALUES ('".$_GET["value"]."')"; // Execute SQL statement mysql_query($sql); ?>

Why doesn’t it work?
I am using MySQL workbench and WAMP64, and I am placing the php file in the folder wamp64 → www and it does not work
What am I doing wrong?

Let's imagine I have the following code on Arduino:
. . .

You have such code and it compiled ?

What happens when you enter the following url in a browser on another device (smartphone etc.) connected to your network:
http://192.168.1.2/write_data.php?value=123
Do you get an entry in the database table?

Credentials to access the database appear in two places:

  1. Embedded in the php code.
  2. In the statement
    if (conn.connect(server_addr, 3306, user, password))

I don't fully understand that. You've seen a conn object used like that in an example?

All is revealed to me. The code would not have compiled.
The ‘conn’ object which you attempted to use is of class MySQL_Connection from library MySQL_Connection.h which is missing from your code.

I found an example of similar code, but for an ethernet card at MySQL_Connector_Arduino/connect.ino at master · ChuckBell/MySQL_Connector_Arduino · GitHub

Anyway, I’m not sure how relevant it is to you because you are supplying credentials to access the database in your php code.

Thanks for answering sorry for that part of conn.connect I mixed up one part from the code of the arduino connector with this connection with php files. This is the real and corrected code:

#include <WiFi.h> // For the Wifi Shield of Arduino

// MySQL database connection:
byte mac_addr = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; // It can be anything as long as it follows this structure
IPAddress server_addr(192,168,137,200); // IP of the MySQL server here (ipconfig in cmd and look IPV4)
char user = “dsm501a_project”; // MySQL user login username
char password = “12345”; // MySQL user login password

// Wifi connection
char ssid = “tuhhmechatronicssebas”; // your SSID
char pass = “12345678901”; // your SSID password

WiFiClient client;

int A = 1;

void setup() {

Serial.begin(9600);
while (!Serial); // wait for serial port to connect. Needed for Leonardo only

// Begin WiFi section
int status = WiFi.begin(ssid, pass);
if ( status != WL_CONNECTED) {
Serial.println(“Couldn’t get a wifi connection”);
while(true);
}
else {
Serial.println(“Connected to network”);
IPAddress ip = WiFi.localIP();
Serial.print("My IP address is: ");
Serial.println(ip);
}

}

void loop() {

if (client.connect(server_addr, 80)) {
client.print(“GET /write_data.php?”); // This
client.print(“value=”); // This
client.print(A); // And this is what we did in the testing section above. We are making a GET request just like we would from our browser but now with live data from the sensor
client.println(" HTTP/1.1");
client.println(“Host: 192.168.137.200”);
client.println(“Connection: close”);
client.println();
client.println();
client.stop();
Serial.println(A);
}

else {
// If Arduino can’t connect to the server (your computer or web page)
Serial.println("–> connection failed\n");
client.stop();
}

// Give the server some time to recieve the data and store it. I used 10 seconds here. Be advised when delaying. If u use a short delay, the server might not capture data because of Arduino transmitting new data too soon.
delay(10000);
A++;
}

And this is the php code:

<?php // Prepare variables for database connection $host = "localhost"; $port = 3306; $socket = ""; $user = "dsm501a_project"; $password = "12345"; $dbname = "test_arduino"; // Connect to your database $con = new mysqli($host, $user, $password, $dbname, $port); // Prepare the SQL statement $sql = "INSERT INTO `test_arduino`.`test` (`Value`) VALUES ('".$_GET["value"]."')"; // Execute SQL statement mysqli_query($con,$sql); ?>

the php code works, the arduino sketch runs but it does not send any data. What am I doing wrong? Thanks for your time

What am I doing wrong?

Would you point your browser at google, enter a search phrase, and then close the tab without looking at the results? Why would you do that?

The server may have something to say in response to your GET request, but you ignore what it might have to say.

The server can (and probably does) maintain log files, showing all accesses, and the status of each request. What do those log files tell you?

And you said the Arduino sketch runs. I presume you mean it runs without writing any failure messages to the serial console.
Do you see the output of the following statement on the serial console:

Serial.println(A);

?

If you post code again, please use code tags.

PaulS:
Would you point your browser at google, enter a search phrase, and then close the tab without looking at the results? Why would you do that?

The server may have something to say in response to your GET request, but you ignore what it might have to say.

The server can (and probably does) maintain log files, showing all accesses, and the status of each request. What do those log files tell you?

Hi @PaulS thanks for your response, where can I see these log files, hoe do I access to them? Can I see something in the serial monitor that tells me what the client is printing? Is there any other thing that I have to configure on the arduino in order to send data to a php file? The moment that I use client.print should it appear something in the serial monitor (because nothing appears)?

@6v6gtl how do I put the code tags (I wanted to ask that before sorry)? I don't find them. Yes I see Serial.println(A); in my serial monitor

Regards
Sebastian

where can I see these log files, hoe do I access to them?

I'm assuming that you have access to 192.168.137.200 on your network. You really need to learn how to find the log files yourself. Where they are created depends on how you set up the web server.

Can I see something in the serial monitor that tells me what the client is printing?

No. The ethernet shield knows nothing about the serial port. That is not to say that you couldn't change your code to also print to the serial port when you print to the client.

Is there any other thing that I have to configure on the arduino in order to send data to a php file?

No.

The moment that I use client.print should it appear something in the serial monitor (because nothing appears)?

Nothing appears on my monitor when you send text messages to your friends, either. The difference is that that is what I expect.

This appears when I invoke 192.168.137.200, the home page of my wamp server nothing else... I don't know what is happening I have been working in this the whole day and no data is transmitted to the mysql database.

You see see the various editing features including code tags when you click on Preview when you compose a post.

Question 1
I'll ask this question again (modified because you have changed the IP address/wireless network) because I did not get a direct answer before:

What happens when you enter the following url in a browser on another device (PC, smartphone etc.) connected to your Wireless network [SSID ="tuhhmechatronicssebas"]:
http://192.168.137.200/write_data.php?value=1
Do you get an entry in the database table?

If you don't get that far, there is no hope of getting any further with the Arduino/wireless shield.

Question 2
What IP address is returned in the serial console as a result of these statements in your code

Serial.print("My IP address is: ");
Serial.println(ip);

If that lot doesn't help, then take the standard example of a wifi web client from here: https://www.arduino.cc/en/Tutorial/WiFiWebClient and adapt it to your wlan (ssid/password) and run it. It attempts a simple connection to google.com and prints something out. I'm assuming the network you land in from your wireless lan has internet connectivity. If not, fix the URL in the sketch to point to something you can access (like the default page of your wamp server) and say how far you get.

6v6gt:
Question 1
I’ll ask this question again (modified because you have changed the IP address/wireless network) because I did not get a direct answer before:

Yes as I told you it works it writes the value 1 in my data base

6v6gt:
Question 2
What IP address is returned in the serial console as a result of these statements in your code

It changes everytime I run the program, for example the last run gave me this IP address:
Connected to network

My IP address is: 192.168.137.107

I already tried the wifi web client example and it works, but it is like for some reason arduino program does not execute the GET request I post the last version of my codes I hope it helps.

I post the arduino sketch:

#include <SPI.h>
#include <WiFi.h>                  // For the Wifi Shield of Arduino


// MySQL database connection:
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  // It can be anything as long as it follows this structure
IPAddress server_addr(192,168,137,200);  // IP of the MySQL server here (ipconfig in cmd and look IPV4)
char user[] = "dsm501a_project";        // MySQL user login username
char password[] = "12345";              // MySQL user login password

// Wifi connection
char ssid[] = "tuhhmechatronicssebas";            // your SSID
char pass[] = "12345678901";         // your SSID password

WiFiClient client;            


int A = 1;

void setup() {

  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect. Needed for Leonardo only

  // Begin WiFi section
  int status = WiFi.begin(ssid, pass);
  if ( status != WL_CONNECTED) {
    Serial.println("Couldn't get a wifi connection");
    while(true);
  }
  else {
    Serial.println("Connected to network");
    IPAddress ip = WiFi.localIP();
    Serial.print("My IP address is: ");
    Serial.println(ip);
    }
    
}

void loop() {

  
      
    if (client.connect(server_addr, 80)) {
      client.print("GET write_data.php?"); // This
      client.print("value="); // This
      client.print(A); // And this is what we did in the testing section above. We are making a GET request just like we would from our browser but now with live data from the sensor
      client.println(" HTTP/1.1");
      client.println("Host: 192.168.137.200");
      client.println("Connection: close");
      client.println();
      client.println();
      client.stop();
      Serial.println(A);
  }
    

  

  else {
    // If Arduino can't connect to the server (your computer or web page)
    Serial.println("--> connection failed\n");
    client.stop();
  }

  char c = client.read();
  Serial.write(c);
  
  // Give the server some time to recieve the data and store it. I used 10 seconds here. Be advised when delaying. If u use a short delay, the server might not capture data because of Arduino transmitting new data too soon.
  delay(10000);
  A++;
}

The php code that works is this one:

<?php

    // Prepare variables for database connection
	$host     = "localhost";
	$port     = 3306;
	$socket   = "";
	$user     = "dsm501a_project";
	$password = "12345";
	$dbname   = "test_arduino";

	// Connect to your database

	$con = new mysqli($host, $user, $password, $dbname, $port);
      
    // Prepare the SQL statement

    $sql = "INSERT INTO `test_arduino`.`test` (`Value`) VALUES ('".$_GET["value"]."')";    

    // Execute SQL statement

    mysqli_query($con,$sql);

?>

Is there something missing in the arduino sketch? Do I have to do something with the firewall? When I put ipconfig on cmd the IPv4 address is 192.168.137.200 that is what I introduce in the server address and the client.print part, and it connects my serial monitor gives me the secuence of numbers from 1 to N but it does not post it in the mysql database.
What else do I have to do?

I guess what you want is this:

client.print("GET /write_data.php?");

and not:

client.print("GET write_data.php?");

Thank you @6v6gt I already changed that but it does not work. I really don't know what else to do... could you try it in your computer and tell me if it's working? Just create a table like it says in the php code and try it.....
Best regards,
Sebastian

My wlan interface is ESP8266 based, do the test won’t be valid for you.

The test which you made from a web browser using the URL:
http://192.168.137.200/write_data.php?value=1
was NOT made from 192.168.137.200 itself. Is that true ?

I’ve modified your code so it should give you back the response that the server returns. Can you try that ?

#include <SPI.h>
#include <WiFi.h>                  // For the Wifi Shield of Arduino


// MySQL database connection:
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  // It can be anything as long as it follows this structure
IPAddress server_addr(192,168,137,200);  // IP of the MySQL server here (ipconfig in cmd and look IPV4)
char user[] = "dsm501a_project";        // MySQL user login username
char password[] = "12345";              // MySQL user login password

// Wifi connection
char ssid[] = "tuhhmechatronicssebas";            // your SSID
char pass[] = "12345678901";         // your SSID password

WiFiClient client;           


int A = 1;

void setup() {

  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect. Needed for Leonardo only

  // Begin WiFi section
  int status = WiFi.begin(ssid, pass);
  if ( status != WL_CONNECTED) {
    Serial.println("Couldn't get a wifi connection");
    while(true);
  }
  else {
    Serial.println("Connected to network");
    IPAddress ip = WiFi.localIP();
    Serial.print("My IP address is: ");
    Serial.println(ip);
    }
   
}

void loop() {

 
     
    if (client.connect(server_addr, 80)) {
      client.print("GET /write_data.php?"); // This
      client.print("value="); // This
      client.print(A); // And this is what we did in the testing section above. We are making a GET request just like we would from our browser but now with live data from the sensor
      client.println(" HTTP/1.1");
      client.println("Host: 192.168.137.200");
      client.println("Connection: close");
      client.println();
      client.println();

      // see what comes back from the server 
      while (client.available()) {
        char c = client.read();
        Serial.write(c);
      }

      client.stop();
      Serial.println(A);
  }
   

 
  else {
    // If Arduino can't connect to the server (your computer or web page)
    Serial.println("--> connection failed\n");
    client.stop();
  }


 
  // Give the server some time to receive the data and store it. I used 10 seconds here. Be advised when delaying. If u use a short delay, the server might not capture data because of Arduino transmitting new data too soon.
  delay(10000);
  A++;
}

If that doesn’t work, then we can try an even simpler test, that is simply getting the default home page of your server, which you see if you enter http://192.168.137.200, which would be very similar to the example at https://www.arduino.cc/en/Tutorial/WiFiWebClient which you have said you have already tested, except that you are not contacting google.com but http://192.168.137.200.

If that fails, then there is probably a fire wall issue.

  char c = client.read();
  Serial.write(c);

After you have stopped the client, this is pointless.

The server may be trying to tell you what the problem is, if only you’d listen to it.

The server may be keeping logs, if only you’d look at them.