Mihai Udrea
Mihai Udrea

Reputation: 23

Multiple mysqli prepared statements not working together

A lot has changed in the PHP world since the last time I was using it (few years back). Now it seems I need to learn again everything, which takes me to my current problem.

/* Create a prepared statement */
$stmt1 = $mysqli -> prepare("SELECT channel FROM channel WHERE barcode=?");
$stmt2 = $mysqli -> prepare("SELECT action FROM action WHERE barcode=?");
$stmt3 = $mysqli -> prepare("SELECT reason FROM reason WHERE barcode=?");
$stmt4 = $mysqli -> prepare("SELECT supplier_name FROM suppliers WHERE barcode=?");
/* Bind parameters */
$stmt1 -> bind_param("s", $_POST['channel']);
$stmt2 -> bind_param("s", $_POST['action']);
$stmt3 -> bind_param("s", $_POST['reason']);
$stmt4 -> bind_param("s", $_POST['supplier']);
/* Execute it */
$stmt1 -> execute();
$stmt2 -> execute();
$stmt3 -> execute();
$stmt4 -> execute();
/* Bind results */
$stmt1 -> bind_result($channel1);
$stmt2 -> bind_result($action1);
$stmt3 -> bind_result($reason1);
$stmt4 -> bind_result($supplier1);
/* Fetch the value */
$stmt1 -> fetch();
$stmt2 -> fetch();
$stmt3 -> fetch();
$stmt4 -> fetch();


echo "Channel ".$channel1."; Action: ".$action1."; Reason: ".$reason1."; Supplier: ".$supplier1;

Eeverything works just fine one by one, but when it's all combined... nothing. Only the first statement will work. What am I doing wrong???

Upvotes: 2

Views: 196

Answers (1)

This happen because:

  • mysqli_stmt::execute:

    Note: When using mysqli_stmt_execute(), the mysqli_stmt_fetch() function must be used to fetch the data prior to performing any additional queries.

  • mysqli_stmt::close:

    Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle. If the current statement has pending or unread results, this function cancels them so that the next query can be executed.

  • The binding (for params and results) can be done before execution... just excludes execute(), fetch() and close() for each statment in one separate block each.

Also take in mind the use of trigger_error($mysqli->error); to check possible errors while using prepare().

Upvotes: 1

Related Questions