Multiple PHP mysqli prepared statements (INSERT UPDATE SELECT) weird behavior

Question

I have a problem using following PHP function:

function saveCommentOnDB($arg_textComment, $arg_score, $arg_userEmail)
{
    $result_tmp = null;

    $this->conn->autocommit(false);

    echo "saving\n";
    echo "text comment: \n";    
    var_dump($arg_textComment); // OKAY
    echo "comment score: \n";
    var_dump($arg_score);       // OKAY
    echo "user mail: \n";
    var_dump($arg_userEmail);   // OKAY

    try {

        //[tag1] $query_1 = "INSERT INTO commenti (userFirstname, userEmail, textComment, score, feedback) VALUES ( (SELECT firstname FROM utente u WHERE u.userEmail = 'asd@asd.asd') ,'asd@asd.asd', 'This is an example comment.', 5, 0);";
        $query_1 = "INSERT INTO commenti (userFirstname, userEmail, textComment, score, feedback) VALUES ( (SELECT firstname FROM utente u WHERE u.userEmail = ?) ,?,?, ?, 0);";
        $query_2 = "UPDATE utente SET commentID=(SELECT c.commentID FROM commenti c WHERE c.userEmail = ?) WHERE userEmail = ?;";
        $query_3 = "SELECT commentID, textComment FROM commenti WHERE userEmail = ?;";

        $stmt1 = $this->conn->prepare($query_1);
        $stmt2 = $this->conn->prepare($query_2);
        $stmt3 = $this->conn->prepare($query_3);

        $stmt1->bind_param("sssd", $arg_userEmail, $arg_userEmail, $arg_textComment, $arg_score);
        $stmt2->bind_param("ss", $arg_userEmail, $arg_userEmail);
        $stmt3->bind_param("s", $arg_userEmail);

        $stmt1->execute();
        $stmt2->execute();
        $stmt3->execute();

        $stmt3->bind_result($col1, $col2);
        $stmt3->fetch();

        echo "result:\n";
        var_dump($col1);    // OKAY
        var_dump($col2);    // OKAY

        $result_tmp = array(
            'commentID' => $col1,
            'textComment' => $col2
        );

        $this->conn->commit();
    } catch (Exception $e) {
        $this->conn->rollback();
    }

    return $result_tmp;
}

Please, ignore the echo and var_dump, I put them only for debugging.

The problem is that in this function these three prepared statement seems to work not correctly. In particular the statement $stmt1: the result of $stmt3 is correct (as if $stmt1 and $stmt2 are executed correctly), but I don't see anything on my Database. In other words: the statements works correctly 'temporarily' during the execution, but in MyPHP Admin there's nothing on the table commenti.

For example, we assume having this on the DB:

Now I launch the function with following parameters:

$arg_textComment = 'This is an example comment'
$arg_score = '5'
$arg_userEmail = 'asd@asd.asd'

and we have on my browser console:

ie: the commentID (28) is right and the comment text (commentcomment) was "saved", then I recheck the DB but I have still this:

and var_dump($stmt1) after the execution is:

stmt1:
object(mysqli_stmt)#4 (10) {
  ["affected_rows"]=>
  int(1)
  ["insert_id"]=>
  int(41)
  ["num_rows"]=>
  int(0)
  ["param_count"]=>
  int(4)
  ["field_count"]=>
  int(0)
  ["errno"]=>
  int(0)
  ["error"]=>
  string(0) ""
  ["error_list"]=>
  array(0) {
  }
  ["sqlstate"]=>
  string(5) "00000"
  ["id"]=>
  int(4)
}

The var_dump seems to be ok, but DB nope.

So I try to execute the query 'manually' by this (it will be executed only the code into the green lined box):

and I have what I expected:

sql> INSERT INTO commenti (userFirstname, userEmail, textComment, score, feedback) VALUES ( (SELECT firstname FROM utente u WHERE u.userEmail = 'asd@asd.asd') ,'asd@asd.asd', 'commentcomment', '5', 0) [2017-01-21 17:38:28] 1 row affected in 11ms

Keep in mind score value is store on DB as float.

The SQL query of the $stmt1 is the same I inserted manually (INSERT INTO... via PHPStorm).

Why the first doesn't works and instead the second yes?

Hope this screencast may help:

https://youtu.be/UsYK93jYVqA

if your first query doesn't work then there was an error during its execution — Your Common Sense, Jan 21 '17 at 18:36
I tried to comment `$stmt2` and `$stmt3`, the `$stmt1` works perfectly and I can see the new row on the DB. I tried also to coalesce the three statements into one, but this not helped. Have no idea. — shogitai, Jan 21 '17 at 18:44
Still, the answer is always the same: if some data is not in the DB, then some query failed. If some query failed then there was an error. If there was an error you have to read it and fix the issue. As simple as that — Your Common Sense, Jan 21 '17 at 18:48
Using `$stmt->execute()` solved the problem. All queries were correct. — shogitai, Jan 21 '17 at 18:54
@YourCommonSense as you can see: I tried to report all details. There were no errors and since there are no errors, what I should read? :( — shogitai, Jan 21 '17 at 19:00
I had abbreviated `$stmt` as not to repeat `$stmt1`, `$stmt2` and `$stmt3`. — shogitai, Jan 21 '17 at 19:01
Never in my life I've seen such inconsistent course of actions — Your Common Sense, Jan 22 '17 at 04:54

shogitai · Answer 1 · 2017-01-21T18:53:24.687

0

Problem solved, change from this:

        $stmt1->execute();
        $stmt2->execute();
        $stmt3->execute();

to this:

        $stmt1->execute();
        $this->conn->commit();
        $stmt2->execute();
        $this->conn->commit();
        $stmt3->execute();
        $this->conn->commit();

Have no idea of why... but it works after many tests.

edited Jan 21 '17 at 18:53

answered Jan 21 '17 at 17:44

shogitai

1,823
1
23
50

your "solution" makes absolutely no sense – Your Common Sense Jan 22 '17 at 04:53

Multiple PHP mysqli prepared statements (INSERT UPDATE SELECT) weird behavior

1 Answers1