Jump to content

mysqli affected rows not working with INSERT


pcborges

Recommended Posts

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

Link to comment
Share on other sites

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 
}

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

"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)

Link to comment
Share on other sites

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 by gw1500se
Link to comment
Share on other sites

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 by pcborges
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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 by pcborges
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.