Arduino uno + ethernet shield + IR obstacle send data to MySQL

hello, i need help with my project, any help would be appreciated.
i’m trying to send sensor data such as (0 and 1/ there is an obstacle or not) from my IR obstacle sensor to mySQL database.
here is my reference here

and below is my arduino code:

#include <Ethernet.h>

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

int IR1 = 6;
int IR2 = 7;
int value = 0; //no obstacle
int value2 = 0;


IPAddress server(192,168,0,17); 

// Initialize the Ethernet server library
EthernetClient client;

void setup() {
 // Serial.begin starts the serial connection between computer and Arduino
  Serial.begin(9600);
 
 pinMode(IR1, INPUT);
 pinMode(IR2, INPUT);

 // start the Ethernet connection
  Ethernet.begin(mac);
  
}

void loop() {
 
  value = analogRead(IR1); // Fill the sensorReading with the information from sensor
  value2 = analogRead(IR2);

  // Connect to the server (your computer or web page)  
  if (client.connect(server, 8081)) {

  
  
    client.print("GET /write_data.php?"); // This
    client.print("value=");
    client.print(value); 
    client.print("&&value2=");
   // client.print("value2 =");
    client.print(value2);
    client.println(" HTTP/1.1"); 
    client.println("Host: 192.168.0.17"); 
    client.println("Connection: close"); // Part of the GET request telling the server that we are over transmitting the message
    client.println(); // Empty line
    client.println(); // Empty line
    client.stop();    // Closing connection to server

  }

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

  value = digitalRead(IR1);
  value2 = digitalRead(IR2);
  if (value == LOW)
  {
    Serial.println("IR1 detected!!");
      delay(6000);
  }
  else
  {
    Serial.println("IR1 clear");
    delay(6000);
  }


  value = digitalRead(IR1);
  value2 = digitalRead(IR2);
  if (value2 ==LOW)
  {
    Serial.println("IR2 detected!!");
    delay(6000);
  }
  else
  {
    Serial.println("IR2 clear");
    delay(6000);//6sec
  }
 
  // 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);
}

when i opened my serial monitor it keeps giving me “connection failed” message.
i already tried to change the ip address to the different one (not the one from the IPv4) but it still not let the arduino to connect to the server.

i don’t know what is wrong with my code or with my arduino , because it’s run successfully in that reference project.

sorry for my bad english.

does any one have done this kind of project before? any opinions would be appreciated.

thanks

You are using the same IP address (192,168,0,17) for both the Arduino Ethernet Card and for your Web server (running PHP/SQL). That can’t be correct.

Here, you require only one &:

client.print("&&value2=");

6v6gt: You are using the same IP address (192,168,0,17) for both the Arduino Ethernet Card and for your Web server (running PHP/SQL). That can't be correct.

Here, you require only one &:

client.print("&&value2=");

hi . thanks for you reply

but even when i'm using different IP address it still doesn't allow me to connect to the server. the connection is still failed on the serial monitor

here is my full code.

write_data.php:

<?php
    //step 3 create files that will capture data sent from arduino and write it to database
    // Prepare variables for database connection
   
    $dbusername = "icecream"; 
    $dbpassword = "creamcream"; 
    $server = "localhost"; 
   
    $dbconnect = mysql_connect($server, $dbusername, $dbpassword);
    $dbselect = mysql_select_db("test",$dbconnect);

  

    $sql = "INSERT INTO test.sensor (value,value2) VALUES ('".$_GET["value"]."' , '".$_GET["value2"]."')";    

    // Execute SQL statement

    mysql_query($sql);

?>

get_data.php:

<?php
$url=$_SERVER['REQUEST_URI'];
header("Refresh: 60; URL=$url");  // Refresh the webpage every 5 seconds
?>
<html>
<head>
    <title>IR sensor</title>
</head>
    <body>
        <h1>IR obstacle sensor</h1>
    <table border="0" cellspacing="0" cellpadding="4">
      <tr>
            <td>ID</td>
            <td>Timestamp</td>
            <td>Value</td>
			<td>Value2</td>
      </tr>
      
<?php
    // Connect to database

    $con=mysqli_connect("localhost","icecream","creamcream","test");
     
		if (mysqli_connect_errno())
		{
			echo "Failed to connect to MySQL: " .mysqli_connect_error();
		}
		
    // Retrieve all records and display them   
    $result = mysqli_query($con,'SELECT * FROM sensor ORDER BY id DESC');
      
    // Process every record
    
    while($row = mysqli_fetch_array($result))
    {      
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['time'] . "</td>";
        echo "<td>" . $row['value'] . "</td>";
		echo "<td>" . $row['value2'] . "</td>";
        echo "</tr>";
        
    }
        
    // Close the connection   
    mysqli_close($con);
?>
    </table>
    </body>
</html>

arduino:

#include <Ethernet.h>

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

int IR1 = 6;
int IR2 = 7;
int value = 0; //no obstacle
int value2 = 0;


IPAddress server(192,168,0,125); 

// Initialize the Ethernet server library
EthernetClient client;

void setup() {
 // Serial.begin starts the serial connection between computer and Arduino
  Serial.begin(9600);
 
 pinMode(IR1, INPUT);
 pinMode(IR2, INPUT);

 // start the Ethernet connection
  Ethernet.begin(mac);
  
}

void loop() {
 
  //value = analogRead(IR1); // Fill the sensorReading with the information from sensor
  //value2 = analogRead(IR2);
  // Connect to the server (your computer or web page)  
  if (client.connect(server, 8081)) {

  int value = analogRead(IR1); // Fill the sensorReading with the information from sensor
  int value2 = analogRead(IR2);
  
    client.print("GET /write_data.php?"); // This
    client.print("value=");
    client.print(value); 
    client.print("&value2=");
   // client.print("value2 =");
    client.print(value2);
    client.println(" HTTP/1.1"); // Part of the GET request
    client.println("Host: 192.168.0.125"); 
    client.println("Connection: close"); 
    client.println(); // Empty line
    client.println(); // Empty line
    client.stop();    // Closing connection to server

  }

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

  value = digitalRead(IR1);
  value2 = digitalRead(IR2);
  if (value == LOW)
  {
    Serial.println("IR1 detected!!");
      delay(6000);
  }
  else
  {
    Serial.println("IR1 clear");
    delay(6000);
  }


  value = digitalRead(IR1);
  value2 = digitalRead(IR2);
  if (value2 ==LOW)
  {
    Serial.println("IR2 detected!!");
    delay(6000);
  }
  else
  {
    Serial.println("IR2 clear");
    delay(6000);//6sec
  }
 
  // 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);
}

What happens when you enter the following command in a browser on say a PC or smartphone in the same network as your PHP/MySQL server:

http://192.168.0.125:8081?value=56&value2=78

Does anything get written to the database?

Does anything get written to the database?

It won't with that URL. You left out the php script.

http://192.168.0.125:808//write_data.php?value=56&value2=78

    client.stop();    // Closing connection to server

Hey, server, do something, and then go to hell. Hmmm...

I would want to know what the server had to say about my request.

when i'm entering this http://localhost:8081/write_data.php?value=100&value2=200 to my browser, it successfully sending the value to my database.

when i'm uploading my arduino code to my arduino uno+ethernet in order to update the database. it's still printed "connection failed" on my serial monitor.

whenever i change the IP address to the different one because i'm afraid the one that i'm using is not wright, but the arduino still wont connected to my server.

i wonder if there is something wrong with the arduino code or maybe the arduino.

i'm sorry because i'm truly new to arduino and coding, there are so many things i'm not understand.

when i'm entering this http://localhost:8081/write_data.php?value=100&value2=200 to my browser, it successfully sending the value to my database.

That is NOT the same as entering http://192.168.0.125:8081/write_data.php?value=56&value2=78.

Try it our way to learn anything.

when i'm uploading my arduino code to my arduino uno+ethernet in order to update the database. it's still printed "connection failed" on my serial monitor.

It does NOT print "connection failed" on upload. It prints that message only when the code actually runs. That indicates that the Arduino can not see the network you are trying to connect to. Possibly because the IP address is NOT 192.168.0.125. Try the above test to prove that the server IS at 192.168.0.125.

i wonder if there is something wrong with the arduino code or maybe the arduino.

No. The Arduino's owner seems to have problems following directions, though.

This test: http://192.168.0.125:8081/write_data.php?value=56&value2=78 is best done anyway on a machine other that the web/SQL/PHP server itself because that also rules out any firewall problems as well as confirming that the IP address is indeed valid. If you can't do the test on a separate machine, can you temporarily switch off any firewall on the web server ?

Another thing I've just noticed is that the MAC address of your Arduino Ethernet board appears to be exactly the same as the example you based your sketch on. The MAC address must be unique in your network. Is any one else there using the same sketch ?

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

PaulS: That is NOT the same as entering http://192.168.0.125:8081/write_data.php?value=56&value2=78.

Try it our way to learn anything. It does NOT print "connection failed" on upload. It prints that message only when the code actually runs. That indicates that the Arduino can not see the network you are trying to connect to. Possibly because the IP address is NOT 192.168.0.125. Try the above test to prove that the server IS at 192.168.0.125. No. The Arduino's owner seems to have problems following directions, though.

thank you for helping, i'll try it now

6v6gt: Another thing I've just noticed is that the MAC address of your Arduino Ethernet board appears to be exactly the same as the example you based your sketch on. The MAC address must be unique in your network. Is any one else there using the same sketch ?

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

first thank you for you help. i'm not sure if anyone using the sketch or not, but i think none. i'd been using this mac address for a while when i'm testing other project "byte mac[] = {0x00, 0xAA, 0xBB, 0xCC, 0xDE, 0x04 };" i forgot to change the mac address when i'm using the base sketch.

PaulS:
That is NOT the same as entering http://192.168.0.125:8081/write_data.php?value=56&value2=78.

Try it our way to learn anything.

It does NOT print “connection failed” on upload. It prints that message only when the code actually runs. That indicates that the Arduino can not see the network you are trying to connect to. Possibly because the IP address is NOT 192.168.0.125. Try the above test to prove that the server IS at 192.168.0.125.
No. The Arduino’s owner seems to have problems following directions, though.

after testing this on my browser "http://192.168.0.125:8081/write_data.php?value=56&value2=78." it successfully updating the value and value2 data to my mysql database.

but when i try to enter "http://192.168.0.125:8081/write_data.php?value=56&value2=78." to other device (my android phone just for testing) it won’t work

after testing this on my browser "http://192.168.0.125:8081/write_data.php?value=56&value2=78." it successfully updating the value and value2 data to my mysql database.

Is the browser running on 192.168.0.125? If so, and you can’t get that URL to work from another device on the network, the problem is NOT with the Arduino.

PaulS: Is the browser running on 192.168.0.125? If so, and you can't get that URL to work from another device on the network, the problem is NOT with the Arduino.

actually the actual IP address is "192.168.0.4". i understand now about "http://192.168.0.4:8081/write_data.php?value=56&value2=78" this part.

now i think i will try to reconnect my arduino see if it will connected this time. thank you so much for helping me.

after successfully testing “http://192.168.0.4:8081/write_data.php?value=56&value2=78” to updated the value and value2 to the database.

i try to rerun the arduino code but it still not allow me to connect to the server (serial monitor still print out : → connection failed) but my IR sensors are not affected they still work fine.

#include <Ethernet.h>

byte mac[] = {0x00, 0xAA, 0xBB, 0xCC, 0xDE, 0x04 };

int IR1 = 6;
int IR2 = 7;
int value = 0; //no obstacle
int value2 = 0;



IPAddress ip(192,168,0,4);

char server[] = "192.168.0.125";

EthernetClient client;


void setup() {
 
 pinMode(IR1, INPUT);
 pinMode(IR2, INPUT);

  Serial.begin(9600);
  
  Ethernet.begin(mac,ip);
   
   
 
}

void loop() {
 
  value = analogRead(IR1); // Fill the sensorReading with the information from sensor
  value2 = analogRead(IR2);

  if (client.connect(server, 8081)) {
    Serial.println("--> connection ok\n");
 
  
    client.print("GET /write_data.php?"); 
    client.print("value="); 
    client.print(value);  
    client.print("&value2=");
    client.print(value2);
    client.println(" HTTP/1.1"); // Part of the GET request
    client.println("Host: 192.168.0.4"); 
    client.println("Connection: close"); 
    client.println(); 
    client.println(); 

  }

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

  value = digitalRead(IR1);
  value2 = digitalRead(IR2);
  if (value == LOW)
  {
    Serial.println("IR1 detected!!");
      delay(6000);
  }
  else
  {
    Serial.println("IR1 clear");
    delay(6000);
  }


  value = digitalRead(IR1);
  value2 = digitalRead(IR2);
  if (value2 ==LOW)
  {
    Serial.println("IR2 detected!!");
    delay(6000);
  }
  else
  {
    Serial.println("IR2 clear");
    delay(6000);//6sec
  }
 
  // 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);
}

and i manually through the browser and entering this “http://192.168.0.4:8081/get_data.php” to test if the data can be printed out

and it shows this message:

Warning: mysqli_connect(): (HY000/1130): Host ‘192.168.0.4’ is not allowed to connect to this MySQL server in C:\xampp\htdocs\get_data.php on line 23
Failed to connect to MySQL: Host ‘192.168.0.4’ is not allowed to connect to this MySQL server
Warning: mysqli_query() expects parameter 1 to be mysqli, boolean given in C:\xampp\htdocs\get_data.php on line 31

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in C:\xampp\htdocs\get_data.php on line 35

Warning: mysqli_close() expects parameter 1 to be mysqli, boolean given in C:\xampp\htdocs\get_data.php on line 47

Which IP address belongs to the Arduino and which belongs to the server?

Which IP address belongs to the client that the error messages were generated on?

Why do the mysql_query() calls in the two different php scripts have different numbers of arguments?

You are talking about 2 PHP scripts. The write_data.php script now apparently works if you give the correct IP address for the server. The get_data.php script does not.

If you look at the 2 scripts which you supplied, you will notice that the working one has credentials like user name, password and does an explicit connect to the sql server. The one that does not work does not have these.

For the successful test, which browser did you use? (a) the one on the web server itself, (b) the one on another PC in the same network 192.168.0.X (c) on your smartphone (d) somewhere else ?

It is unlikely to work from your smartphone unless you have it configured to use a WLAN which is also in the 192.168.0.X network.

6v6gt:
For the successful test, which browser did you use? (a) the one on the web server itself, (b) the one on another PC in the same network 192.168.0.X (c) on your smartphone (d) somewhere else ?

It is unlikely to work from your smartphone unless you have it configured to use a WLAN which is also in the 192.168.0.X network.

for the successful test i’m using the web server on the same pc (google chrome) and on the smartphone one i connected it with the wifii from the same wlan as the pc

You have an ethernet shield on your Arduino which has a direct cable connection to the wireless router ? What IP address does your smartphone acquire when it joins the wireless lan ? What method did you use to discover the IP address of your web server pc ? Have you tried switching off the firewall / virus checker on the web server pc ? Can you log in to the wireless router and see what devices are connected to the network ? Have you another PC which has a cable connection to this network where you can try the test: "http://192.168.0.4:8081/write_data.php?value=56&value2=78"

6v6gt: You have an ethernet shield on your Arduino which has a direct cable connection to the wireless router ?

yes i have

What IP address does your smartphone acquire when it joins the wireless lan ?

the ip address is like "192.168.0.2"

What method did you use to discover the IP address of your web server pc ?

honestly i'm not sure how to check it. but i'm using the cmd/ipconfig to check the ipv4 (192.168.0.4). is it the same as the ip address of a web server?

Have you tried switching off the firewall / virus checker on the web server pc ?

yes i have

Can you log in to the wireless router and see what devices are connected to the network ?

yes i can

Have you another PC which has a cable connection to this network where you can try the test: "http://192.168.0.4:8081/write_data.php?value=56&value2=78"

for now i unfortunately i don't have another pc to try it. usually i used my friend's pc, and then connected it with my router via wifii , is it the same as using cable connection?