[RESOLVED] MySQLI freeing result in prepared statement

Myth_UK_

The following code uses a prepared statement to select some results.

$link = mysqli_connect($conString['server'], $conString['username'], $conString['password'], $databaseName);
if (!mysqli_connect_errno()) {
$query = "SELECT name, active FROM usertable WHERE name=? AND active=?";
$statement = mysqli_stmt_init($link);
if (mysqli_stmt_prepare($statement, $query)) {
mysqli_stmt_bind_param($statement, 'si', $userName, $userActive);
mysqli_stmt_execute($statement);

// mysqli_stmt_store_result($statement); Do I need this?

mysqli_stmt_bind_result($statement, $result1, $result2);
mysqli_stmt_fetch($statement);
if ((!isset($result1)) || (!isset($result2))) {	
echo 'OK';
}

// mysqli_stmt_free_result($statement); Do I need this?

mysqli_stmt_close($statement);
}
mysqli_close($link);
}

I have commented out the two lines because I am not sure if I need them?
In old MySQL I would always free the memory, in this case using mysqli_stmt_free_result($statement); But I have read you only do this if you have used mysqli_stmt_store_result($statement); Before hand.

My question is do I need to use either (best coding practice) in a prepared statement, as i haven't seen any examples that do when using mysqli_stmt_bind_result();

laserlight

My question is do I need to use either (best coding practice) in a prepared statement, as i haven't seen any examples that do when using mysqli_stmt_bind_result();

I believe that they are only for optimisation purposes, so you do not normally need them.

By the way, I suggest that you use the object oriented version and indent your code, e.g.,

$link = new mysqli($conString['server'], $conString['username'], $conString['password'], $databaseName);
if (!$link->connect_error) {
    $query = "SELECT name, active FROM usertable WHERE name=? AND active=?";
    if ($statement = $link->prepare($query)) {
        $statement->bind_param('si', $userName, $userActive);
        $statement->execute();

    $statement->bind_result($name, $active);

    if ($statement->fetch() && !empty($name) && !empty($active)) {
        echo 'OK';
    }

    $statement->close();
}
}
$link->close();

Myth_UK_

By the way, I suggest that you use the object oriented version and indent your code, e.g.,

Why? What is the benefit of using OO style over Procedural style?

laserlight

Myth(UK) wrote:
Why? What is the benefit of using OO style over Procedural style?

If you compare my example with the indented version of your example, you will see that the syntactic sugar that the OO version provides makes for cleaner code. Beyond this, there is also the possibility of inheriting from the mysqli class and making use of polymorphism, but most of the time just writing another free function will do, and this is possible in the procedural version as well.

Myth_UK_

Yeah, I was just going to re-post, saying You don't have to answer that!

And I have alter my own code to use the OO style. 😉

Myth_UK_

This is just a re-cap on my original question about using store_result and free_result.

This is what i have gathered from googling. (please correct me if I am wrong).

Prepared statements arn't buffered by default. So there's no need to call mysqli_stmt_free_result($statement); or (OO style) $statement->free_result();

If you DO want to buffer the results on the client, then use mysqli_stmt_store_result($statement); or (OO style) $statement->store_results(); Then you can free the memory using mysqli_stmt_free_result($statement); or (OO style) $statement->free_result();

To buffer or not to buffer? Well, fetching results is quicker if unbuffered, BUT that will increase load on the server. I would say that if you experience high server load then, buffer on the client.