CODEWITHSUNDEEP
phpmysqlmysqli
SteveC
SteveC

Reputation: 371

Does mysqli_fetch_assoc stop the next mysqli_prepare working?

I have some stored procedures which upon success end with:

select 1 as outcome;

so that I know that it succeeded.

In PHP my code goes something like this:

if ($stmt = mysqli_prepare($con, "call storedProc(?)")) {
        mysqli_stmt_bind_param($stmt, 'i', $count);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        $row = mysqli_fetch_assoc($result);
        $outcome = $row['outcome'];
        if ($outcome == 1) {
             if ($stmt = mysqli_prepare($con, "call secondStoredProc(?)")) {
                  xxx();

Now the problem is that every single time, even though $outcome is 1, xxx(); is not run. I know this because the function xxx is not a valid function and I get no error in the console.

Strangely it's only exactly the next attempt to run mysqli_prepare() that fails. Note that there's nothing fundamentally wrong with call secondStoredProc(?) and if I reconnect before the mysqli_prepare() that otherwise fails then it works fine, but:

  1. I don't think that it should fail like this.
  2. I don't want to have to reconnect to MySQL between every mysqli_prepare().
  3. I need to know the nature of the phenomenon so I can avoid other issues it may cause.

I can't find any reference (in the documentation or any other guides about mysqli) to a need to properly close a prepared statement before running another. Nor can I find any way to do so apart from reconnecting and that's even presuming that this is in any way the nature of the problem.

Please help.

Upvotes: 3

Views: 203

Answers (1)

Steve Kline
Steve Kline

Reputation: 805

check this out... Hope this helps achieve what you're trying to do.

Some DB Connect file here

define("HOST", "yo.ur.ip.addr");     // The host you want to connect to.
define("USER", "myfunctionalaccount");    // The database username. 
define("PASSWORD", "superdoopersecurepassword!");    // The database password. 
define("DATABASE", "thegoods");
$mysqli = new mysqli(HOST, USER, PASSWORD, DATABASE);
if ( $mysqli->connect_error ) {
    die('Connect Error: ' . $mysqli->connect_error);
}

Some Function PHP here

function somecoolfunction($someparameter)
    if ($stmt = $mysqli->prepare("call storedProc(?)") {
        $stmt->bind_param($someparameter);
        $stmt->execute();
        $stmt->bind_result($outcome);
        $stmt->fetch();
        if ($stmt->num_rows == 1) {
            if ($substmt = $mysqli->prepare("call secondStoredProc(?)") {
                $substmt->bind_param($outcome);
                $substmt->execute();
                $substmt->bind_result($newoutcome);
                $substmt->fetch();
                xxx();
                $mysqli->query(" "); // some other random query
                echo $substmt->affected_rows.' rows were updated based on '.$outcome.'.';
            } // $substmt closes mysqli connection here
        } else {
            //$stmt results return 0 or more than 1 result
        }
    } // $stmt closes mysqli connection here
} // end of function

//   You can use this method if you're considering on using w/o the if loops.
$stmt = $mysqli->prepare("call storedProc(?)") //preparing w/o if statement
$stmt->bind_param($someparameter);
$stmt->execute();
$stmt->bind_result($outcome);
$stmt->fetch();
$stmt->close(); //must close since it's not closed conditionally with an if statement.

$stmt = $mysqli->prepare("call secondStoredProc(?)")
$stmt->bind_param($outcome);
$stmt->execute();
$stmt->bind_result($newoutcome);
$stmt->fetch();
$stmt->close();
echo "Stored Proc Outcome: ".$outcome."\nSecond Stored Proc Outcome: ".$newoutcome;

Upvotes: 1

Related Questions