mysqli affected rows not working with INSERT

[pcborges]

Hi, I have an app that access a MySQL database via a php script.
I noticed that INSERT was returning error but the data was being recorded successfully.
I finally traced the problem to be related with the line 
echo $conn->affected_rows;   

For some reason when it is an SQL INSERT it returns -11 but as I said the INSERTS executes successfully.

The app requesting the service sends in sequence:

char* txtSQL[]={"INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                            "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                            "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                            "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                            "UPDATE activity set value=333 where value=130",
                            "SELECT sum(value) from activity where mac='a9c4952de6b4'",
                            "DELETE from activity WHERE value=333"};

I set a monitor to check what was being returned and got:

query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)
HttpResponse:  -   httpResponseCode: -11
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)
HttpResponse:  -   httpResponseCode: -11
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)
HttpResponse:  -   httpResponseCode: -11
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)
HttpResponse:  -   httpResponseCode: -11
query=UPDATE activity set value=333 where value=130
HttpResponse: 4 *   httpResponseCode: 201   -------------------------------------->As can be seem the INSERTS above returned error but worked OK
query=SELECT sum(value) from activity where mac='a9c4952de6b4'
HttpResponse: 1379 *   httpResponseCode: 200
query=DELETE from activity WHERE value=333
HttpResponse: 4 *   httpResponseCode: 201

The  PHP script do ing the job i s as be low :

<?php
include('connection.php');

//these are just in case setting headers forcing it to always expire 
header('Cache-Control: no-cache, must-revalidate');

error_log(print_r($_POST,TRUE));

if( isset($_POST['query']) && isset($_POST['key']) ){                                   //checks if the tag post is there and if its been a proper form post
  header('Content-type: application/x-www-form-urlencoded');
  if($_POST['key']==$SQLKEY){                                                           //validates the SQL key
    $query=urldecode($_POST['query']);
    if(get_magic_quotes_gpc()){     //check if the worthless pile of crap magic quotes is enabled and if it is, strip the slashes from the query
      $query=stripslashes($query);
    }
    $conn = new mysqli($DB_ADDRESS,$DB_USER,$DB_PASS,$DB_NAME);    //connect

    if($conn->connect_error){                                                           //checks connection
      header("HTTP/1.0 400 Bad Request");
      echo "ERROR Database Connection Failed: " . $conn->connect_error, E_USER_ERROR;   //reports a DB connection failure
    } else {

      $result=$conn->query($query);                                                     //runs the posted query                        

      if($result === false){
        header("HTTP/1.0 400 Bad Request");                                             //sends back a bad request error
        echo "Wrong SQL: " . $query . " Error: " . $conn->error, E_USER_ERROR;          //errors if the query is bad and spits the error back to the client
      } else {
        if (strlen(stristr($query,"SELECT"))>0) {                                       //tests if it's a SELECT statement
          $csv = '';                                                                                // bug fix Undefined variable: csv
          while ($fieldinfo = $result->fetch_field()) {
            $csv .= $fieldinfo->name.",";
          }
          $csv = rtrim($csv, ",")."\n";
//********************************          echo $csv;                                                                    //prints header row
          $csv = '';

          $result->data_seek(0);
          while($row = $result->fetch_assoc()){
            foreach ($row as $key => $value) {
              $csv .= $value.",";
            }
            $csv = rtrim($csv, ",");                                                 //."\n";
          }
          echo $csv;                                                                    //prints all data rows
        } else {
          header("HTTP/1.0 201 Rows");
          echo $conn->affected_rows;       //if the query is anything but a SELECT, it will return the number of affected rows (INSERT IS  RETURNING -11)
        }
      }
      $conn->close();                                          //closes the DB
    }
  } else {
     header("HTTP/1.0 400 Bad Request");
     echo "-Bad Request";                                       //reports if the secret key was bad
  }
} else {
        header("HTTP/1.0 400 Bad Request");
        echo "*Bad Request";
}
?>

Any help will be much appreciated.
Thanks

Paulo Borges

[requinix]

First thing that I see is the "httpResponseCode: -11". Why is that happening? Because there's no way the script returned -11.

[gw1500se]

Also I see the OP is not using prepared statements.

[requinix]

1 minute ago, gw1500se said:

Also I see the OP is not using prepared statements.

Which makes sense given the nature of this.

[pcborges]

Hi requinix, I do not know, it just prints out as I posted above.

gw1500se, I am not strong on PHP and just got this php script that I am using with an APP Inventor Android app and it works fine, including with the INSERTS.

Thanks

Paulo

[gw1500se]

I agree with requinix. Where is that error coming from? To what "monitor" are you referring? It looks like you may not have posted all your code.

[pcborges]

NOTE of INTEREST:

The requests are been sent by an Arduino C++ app.

Thanks

Paulo

[pcborges]

Did not post all the code because it is C++. The "monitor" is just a serial monitor provided by Arduino that prints to the screen everything the PHP script sends back.

Paulo

[pcborges]

"I agree with requinix. Where is that error coming from?"

 

I have no idea, I believe when I find the answer to that I will have the problem solved...

Or will be close to it.

Paulo

[pcborges]

Although the INSERT works

 $conn->affected_rows

returns blank...

Why is it happening?

Is it coming from the MySQL server?

Paulo

[gw1500se]

That method returns an integer. If it is returning a "blank" then something is interfering with the myslqi calls. Is there a call-back function somewhere?

[pcborges]

When I say "blank" I am referring to what is printed on the monitor. The serial monitor prints whatever is sent back from the php script.

Here is the C++ code that is sending the requests. May be it will help.

#include <HTTPClient.h>
 
const char* ssid = "Skynet";
const char* password =  "babacabaca";

char* txtSQL[]={"INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                "UPDATE activity set value=333 where value=130",
                "SELECT sum(value) from activity where mac='a9c4952de6b4'",
                "DELETE from activity WHERE value=333"};

String SQLKEY = "73163";
int sqlArray = 6;
int count=0;
 
void setup() {
 
  Serial.begin(115200);
  delay(4000);   //Delay needed before calling the WiFi.begin
 
  WiFi.begin(ssid, password); 
  Serial.print("Connecting to WiFi");
  while (WiFi.status() != WL_CONNECTED) { //Check for the connection
    delay(1000);
    Serial.print(".");
  }
  Serial.println("\nConnected to the WiFi network");
 
}
 
void loop() {
 if(WiFi.status()== WL_CONNECTED){   //Check WiFi connection status
 
   HTTPClient http;   
   http.begin("http://www.infotecservices.com.br/ESP32web/hydroflux.php"); 
   http.addHeader("Content-Type", "application/x-www-form-urlencoded");
   
   String _post  = "query=";
          _post += txtSQL[count];
          _post += "&key=";
          _post += SQLKEY;
          
   int httpResponseCode = http.POST(_post);
   //Serial.print("Count= ");
   //Serial.println(count);
    
   if (count==sqlArray){
     count=0;
   } else { 
     count+=1;
   }
   
   if(httpResponseCode>0){
 
    String response = http.getString();                       //Get the response to the request
    Serial.println(_post);
    Serial.print("HttpResponse: ");  
    Serial.print(response);
    Serial.print(" * ");
    Serial.print("  httpResponseCode: ");
    Serial.println(httpResponseCode);   //Print return code

   }else{

    String response = http.getString();                       //Get the response to the request
    Serial.println(_post);
    Serial.print("HttpResponse: ");  
    Serial.print(response);
    Serial.print(" - ");
    Serial.print("  httpResponseCode: ");
    Serial.println(httpResponseCode);   //Print return code
   }
   http.end();  //Free resources
 }else{
    Serial.println("Error in WiFi connection");   
 }
  delay(2000);  //Send a request every 10 seconds 
}

 

[requinix]

Your HttpClient library is returning -11. Not PHP. You have to find out why.

What library are you using?

[gw1500se]

My C++ is very rusty but the response codes are not coming from the PHP script per se. I think you need to investigate the C++ code. It obviously not really outputting what comes back from the PHP script. The -11 may mean it cannot properly interpret what comes back. If it is expecting HTML in return you may have some headers messed up in the PHP script. Keep in mind that nothing, not even white space can be output before headers.

[pcborges]

This is a library provided by the Arduino developers.

The -11 is something strange because when the request fails it should return -1 and for a successful request 0 correct?

 

[Barand]

An error code perhaps?

[pcborges]

"Keep in mind that nothing, not even white space can be output before headers."

That is important tip for me.

In the PHP script above, assuming INSERT is working (which it is) , I understand the execution comes the code below.

So, do you suggest the C++ side is getting it and messing something at this point?

 

        } else {
          header("HTTP/1.0 201 Rows");
          echo $conn->affected_rows;       //if the query is anything but a SELECT, it will return the number of affected rows (INSERT IS  RETURNING -11)

[gw1500se]

You need to look at the HTML source being returned by the PHP script to the C++ code. If it is blank or just -1 or 0 then the headers are not being output and that is why the C++ code cannot interpret it. Thus, perhaps, the -11.

Edited December 6, 2018 by gw1500se

[pcborges]

Look to the line: 

if (strlen(stristr($query,"SELECT"))>0) {                                       //tests if it's a SELECT statement

It does not send a header but it still works. 

One the request:

"SELECT sum(value) from activity where mac='a9c4952de6b4'",

It is the one who returns:

HttpResponse: 1379 *   httpResponseCode: 200

 

Edited December 6, 2018 by pcborges

[gw1500se]

You are missing the point. The PHP script is working with respect to MySQL. But it is not outputting a valid HTML page that the C++ code can interpret. I highly suspect that is what the -11 is trying to tell you.

[pcborges]

Yes, you may well be correct. But all the c++ code in this micro-controller does is relay whatever it gets from the php script to the serial monitor, the http driver is the one who hides the protocol.

I will probably have to dive deeper into its internals to understand what might be going wrong.

The reason I came here is that it is not working ONLY with INSERTS and when it is a POST because with GET requests it works fine.

 

[requinix]

33 minutes ago, pcborges said:

This is a library provided by the Arduino developers.

What is the name of it? Where can it be viewed online?

[pcborges]

https://github.com/espressif/arduino-esp32/blob/master/libraries/HTTPClient/src/HTTPClient.h

[requinix]

#define HTTPC_ERROR_READ_TIMEOUT        (-11)

 

[pcborges]

Dear requinix, your call to see the library provided a good tip.

I saw that line as well!

I believe the php script may be taking too long to process the INSERT and the c++ application gets tired of waiting...

I have seen that before a good time ago and that may well be my problem now.

Edited December 6, 2018 by pcborges