Hi everyone,
I'm currently making a project for my arduino, I'm trying to connect my arduino wifi rev 2 to the internet, then to a database hosted locally via XAMPP using PHPmyadmin.
I have managed to connect to the database absolutely fine. I even managed to retrieve information from the database in my terminal, however, my problem is writing to the database.
For testing purposes I just want to write a string "testing" in my table database 'iot' in the table 'sensors' in the column 'value'.
Here's my Arduino code:
#include <SPI.h>
#include <WiFiNINA.h>
#include "arduino_secrets.h"
char ssid[] = SECRET_SSID;
char pass[] = SECRET_PASS;
int status = WL_IDLE_STATUS; // the Wifi radio's status
char server[] = "********"; //IP address of your computer.
int interrupt=0; //Variable to control the iterations of void loop().
String mod = "testing"; //Variable to be written on the database.
String rcv=""; //Variable in which the server response is recorded.
WiFiClient client;
void setup() {
//Initialize serial and wait for port to open:
Serial.begin(9600);
while (!Serial) {
; // wait for serial port to connect. Needed for native USB port only
}
// check for the WiFi module:
if (WiFi.status() == WL_NO_MODULE) {
Serial.println("Communication with WiFi module failed!");
// don't continue
while (true);
}
String fv = WiFi.firmwareVersion();
if (fv < "1.0.0") {
Serial.println("Please upgrade the firmware");
}
// attempt to connect to Wifi network:
while (status != WL_CONNECTED) {
Serial.print("Attempting to connect to WPA SSID: ");
Serial.println(ssid);
// Connect to WPA/WPA2 network:
status = WiFi.begin(ssid, pass);
// wait 10 seconds for connection:
delay(10000);
}
// you're connected now, so print out the data:
Serial.print("You're connected to the network");
printWifiData();
Serial.println("Connected to wifi");
printWiFiStatus();
}
void printWifiData() {
// print your board's IP address:
IPAddress ip = WiFi.localIP();
Serial.print("IP Address: ");
Serial.println(ip);
Serial.println(ip);
// print your MAC address:
byte mac[6];
WiFi.macAddress(mac);
Serial.print("MAC address: ");
printMacAddress(mac);
}
void printMacAddress(byte mac[]) {
for (int i = 5; i >= 0; i--) {
if (mac[i] < 16) {
Serial.print("0");
}
Serial.print(mac[i], HEX);
if (i > 0) {
Serial.print(":");
}
}
Serial.println();
}
void httpRequest()
{
if (client.connect(server, 80))
{
Serial.println("Connection established 1");
client.print(String("GET ") + "/website.php/" + " HTTP/1.1\r\n" + "Host: " + server + "\r\n" + "Connection: close\r\n\r\n"); //GET request for server response.
unsigned long timeout = millis();
while (client.available() == 0)
{
if (millis() - timeout > 25000) //If nothing is available on server for 25 seconds, close the connection.
{
Serial.println("timed-out");
return;
}
}
while(client.available())
{
String line = client.readStringUntil('\r'); //Read the server response line by line..
rcv+=line; //And store it in rcv.
}
//client.stop(); // Close the connection.
}
else
{
Serial.println("Connection failed 1");
}
Serial.println("Received string: ");
Serial.println(rcv); //Display the server response.
}
void loop()
{
if(interrupt==0)
{
httpRequest(); //Call the function to read the response from the server.
delay(1000);
if (client.connect(server, 80))
{
Serial.println("Connection Established 2");
client.print("GET /phpmyadmin_connect.php?"); //GET request to write data to the database.
client.print("value=");
client.print("testing");
client.println(" HTTP/1.1");
client.println("*******");
client.println("Connection: close");
client.println();
client.println();
client.stop();
}
else
{
Serial.println("Connection failed 2");
}
}
interrupt++;
delay(10000);
}
void printWiFiStatus() {
// print the SSID of the network you're attached to:
Serial.print("SSID: ");
Serial.println(WiFi.SSID());
// print your board's IP address:
IPAddress ip = WiFi.localIP();
Serial.print("IP Address: ");
Serial.println(ip);
// print the received signal strength:
long rssi = WiFi.RSSI();
Serial.print("signal strength (RSSI):");
Serial.print(rssi);
Serial.println(" dBm");
}
(I've changed sensitive info to ********)
That code manages to connect to my server absolutely fine, and doesn't seem to throw up any errors.
I have 2 PHP files, both are in the 'htdocs' directory of my XAMPP folder. One is to establish connection and insert data into the specified field, and one is to display and retrieve data from the database.
phpmyadmin_connect.php
<?php
$dbusername = "locally";
//$dbpassword = "123456789";
$server = "localhost";
$dbconnect = mysqli_connect($server, $dbusername);
$dbselect = mysqli_select_db($dbconnect, "iot");
//$request= $_GET["value"];
$sql = "INSERT INTO iot.sensors (value) VALUES ('".$_GET["value"]."')";
//mysqli_query($dbconnect, $sql);
mysql_query($sql);
?>
The code above connects to the server and it's supposed to write to database 'iot' in the table 'sensors' which has a column called 'value'
website.php
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
<title></title>
</head>
<body>
<?php
$dbusername = "locally";
//$dbpassword = "123456789";
$server = "localhost";
$dbconnect = mysqli_connect($server, $dbusername);
$dbselect = mysqli_select_db($dbconnect, "iot");
$sql="SELECT value FROM sensors";
$records=mysqli_query($dbconnect,$sql);
$json_array=array();
while($row=mysqli_fetch_assoc($records))
{
$json_array[]=$row;
}
/*echo '<pre>';
print_r($json_array);
echo '</pre>';*/
echo json_encode($json_array);
?>
</body>
</html>
The code above simply connects to the database and fetches the records held in the table 'sensors' and displays them.
The following is my output in arduino terminal
Connected to wifi
SSID: ******
IP Address: *******
signal strength (RSSI):-68 dBm
Connection established 1
Received string:
HTTP/1.1 200 OK
Date: Mon, 11 Mar 2019 00:07:58 GMT
Server: Apache/2.4.38 (Win64) OpenSSL/1.1.1a PHP/7.3.2
X-Powered-By: PHP/7.3.2
Content-Length: 156
Connection: close
Content-Type: text/html; charset=UTF-8
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
<title></title>
</head>
<body>
[{"value":"testing"},{"value":"testing"}]
</body>
</html>
Connection Established 2
In the response above you can see [{"value":"testing"},{"value":"testing"}]. These values already exist in my table under the column 'value'. If I manually add another row in this table it would also be printed.
Okay so my problem is, my Arduino fails to write a string "testing" into the table 'sensors' which belongs to the database 'iot'.
I've looked around at different approaches, but none seem to work. Maybe I'm missing something obvious.
Apologies to all if my post isn't tidy or detailed enough, this is my first post on the forum.
Thank you in advance for any help/advice/suggestions. 
P.S after this hurdle I plan to use the L35H temp sensor to store its value in real-time.