mysqli_affected_rows return -1 but update query is successful

Question

I have a page of php code take user's new password string to change user password and after some validation code send to process page for update record in users table. after sending new password string to process page and doing the update query, mysqli_affected_rows return -1 but update query was successful and password was changed Or If previous password string was the same previous password, update query does not make any changes but still returns -1.

I use mysqli functions in all of my codes. db_conncet_fnc(),db_query_func(),db_fetch_assoc_func(),... These functions include the same mysqli functions.

my proccess page code is this:

 $link_con=db_conncet_fnc();
 require_once("PassHash.php");
 $query = "SELECT * FROM mdr WHERE m_id='$md_id'";
 $result = db_query_func($query);
 $affecteds= mysqli_affected_rows($link_con);
 $_SESSION["affecteds"]=$affecteds; //this is for test before UPDATE query
 $_SESSION["affected-s-oop"]=$link_con->affected_rows; //this is for test before UPDATE query
 $rec = db_fetch_assoc_func($result);
 $p_hasher = new PassHsh(25, FALSE);
 $ans = "2"; //answer without error is 2
 if($rec)
 {
     if ($pass != "" && $newpsw != "" && $check = $p_hasher->checkpss($newpsw, $rec["userpass"])) {
         if ($chngpsw_err < 2) {
             $_SESSION["chngpsw_err"] = "has";
         }
         $_SESSION["pass_nwpass_is_equal"] = "yes";
         header("location:index.php?page=chngpass");
         exit();

     } elseif ($check = $p_hasher->checkpss($pass, $rec["userpass"])) {
         $hashed_psw = $p_hasher->HashPassword($newpsw);
         $query = "UPDATE `mdr` SET `userpass`='$hashed_psw' WHERE m_id='" . $md_id . "' ";
         $result = db_query_func($query);
         $affect_upd = mysqli_affected_rows($link_con);
         $_SESSION["affect_upd"] = $affect_upd; //by function
         $_SESSION["affect_upd-oop"] = $link_con->affected_rows; //by object
         if ($affect_upd == 0) {
             $_SESSION["update_result"] = "err0";

             header("location: index.php?page=chngpass");
             exit();

         }
         if ($affect_upd == -1) {
             $_SESSION["update_result"] = "err-1";

             header("location: index.php?page=chngpass");
             exit();

         }
         if ($affect_upd > 0) {
             $_SESSION["update_result"] = "ok";

             header("location: index.php?page=chngpass");
             exit();

         }

     } else {
         $ans = "1";
         header("location: index.php?page=chngpass&ans=$ans");
     }
 }

I found some questions about this in stackoverflow and google and discussed about bug in mysqli with xdebug like this https://bugs.php.net/bug.php?id=67348 but I dont use $connect->stat and some pages wrote about bug in mysqli for every where xdebug is enabled, So I disable xdebug in php.ini but mysqli_affected_rows return -1 in all states and events, and all positions yet.

I used Google Chrome for debugging with phpstorme before.

Should I disable something in Chrome?

update:(2018/08/01):

After a few days searching on various sites by Google and searching in stackoverflow and doing the steps and suggestions written, I couldnt solve it.

A person named "siddharaj solanki" wrote in this question:

mysqli_affected_rows creates new connection (read details) wrote:

Now the problem is I don't understand how to pass database link ($link) in mysqli_affected_rows() function. I tried above, but it seems to create a new database connection, so mysqli_affected_rows returns 0 instead of 1.

So I think the bug will not easily solve for this function based on my research and this link:

https://www.google.com/search?q=bug+mysqli_affected_rows

please guide me whats solution or what is good or best way instead of mysqli_affected_rows to check update query?

good time

Please help me to resolve that.

thanks.

Answer 1

The comments above do explain what's wrong, but I'll summarize here and mark this a community wiki answer.

Your code creates a connection to the database, but you said your db_query_func() function creates a separate connection to the database to run the query.

The affected-rows count can only report the rows affected by a query in the same connection. But you executed the UPDATE in a different connection.

Consider this analogy: you open two separate ssh sessions to a remote server. You run a command in one shell, and then in the other shell you use !! to repeat the command. The second shell has no idea of the command history from the first shell, so it can't repeat the same command.

You need to use the same database connection to execute the query and then report the rows-affected. Maybe the comments above did not make this clear, but you can solve this by passing the connection to your query function:

 $result = db_query_func($link_con, $query);

Within that function, do not create a new mysqli connection, but use the connection that you passed as an argument.

Then when it returns, it has the proper context so you can get the mysqli_affected_rows($link_con) from the same connection.

There are other alternative solutions possible:

• It's not clear why you need the db_query_func() at all. Perhaps you should just call mysqli_query() from your main code.

• Or else db_query_func() should call mysqli_affected_rows() itself, and return the value.