Hi all:

I am coming out of asp classic. With asp, the record set and the db connection link can both be closed right after you declare your GetRows array. All the variables can still be used up until the end of the page. Something like:


arrSample = RS.GetRows()

RS.close:set RS = nothing
Conn.Close:set Conn=nothing

Response.write arrSample(2,i)

But with PHP using Prepared Statements, I find I have have to keep mysqli_stmt_close($stmt); open until I am done with all my variables on the page.

Am I correct?

Thanks!

    well i do not know anything about asp but for closing mysqli you can do it when ever you want with mysqli_close() but if your making alot of requests to the database it's better to keep it open and do your requests in bunches than making a new connection on every request.

      Can you show us some actual code that you tried where you lost the data when you closed the statement too early?

        The above (EDIT: due to slow typing, above menas 2 posts above) is correct insofar connection goes, and the general idea applies to statements as well. Executing the same query as a prepared statement several times using different parameters is quicker than multiple non prepared queries or creating new prepared statements from the same SQL statement several times.

        Keep the statement around for as long as you need it, then close it. If using more than one prepared statement, use $stmt->free_result() before executing/using another statement, if you want to execute the first one later on again. If not, you can just as well free it directly.

        But, you can easily do the equivalent of this

        arrSample = RS.GetRows()

        in php

        $arr = array();
# assuming bind params has been called
while ($stmtname->fetch())
{
	$arr[] = array('field1' => $field1, 'field2' => $field2);
}
printf('<pre>%s</pre>', print_r($arr,1));

        Or, if you use a database abstraction layer like PDO or MDB2, you can fetch all row at once

        $db = new PDO(...);
$stmt = $db->prepare(...);
$stmt->execute(...);

$arr = $stmt->fetchAll();

          Thanks jgetner, but I don't think I explained myself well.

          For example the following will not work in PHP:

          
$stmt = mysqli_prepare($link, "
SELECT Promoter
FROM Events
WHERE AllowFlyers = 'N'
")

mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $rPromoter);

mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);

echo $rPromoter;

mysqli_close($link);

          Meaning you cannot write mysqli_stmt_close($stmt); BEFORE all result variables are called.

          You would need to write:

          
$stmt = mysqli_prepare($link, "
SELECT Promoter
FROM Events
WHERE AllowFlyers = 'N'
")

mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $rPromoter);

mysqli_stmt_fetch($stmt);

echo $rPromoter;

mysqli_stmt_close($stmt);


mysqli_close($link);

          You can close your recordset before the results variables are used on ASP classic.

            this is direct from the manual

            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.

            for the internal reasons why i would gather you would have to ask the core team.

              Yep...thank you.

              Just trying to "forget" all my ASP stuff and remember my PHP stuff! 😉

                Write a Reply...