When to close Prepared Statement

php prepared-statement
Tarik picture Tarik · Jul 9, 2011 · Viewed 8.8k times · Source

When to close prepared statements in PHP?

Example:

    $query = "insert into web_reviews (title,added_date,reviewer_home_url,read_more_link,summary) values(?,?,?,?,?)";
    $stmt = $this->db->prepare($query);
    $stmt->bind_params($this->title,$this->added_date,$this->reviewer_home_url,$this->read_more,$this->summary);
    $stmt->execute() or die("Cannot add the date to the database, please try again.");
    $stmt->close();

    $stmt = $this->db->prepare("select id from web_reviews where title = ? and read_more = ?");
    $stmt->bind_params($this->title,$this->read_more);
    $stmt->execute();
    $stmt->bind_results($web_review_id);
    $stmt->close();

Should I use $stmt->close(); here?

Edit:

What is written on the PHP Manual and also one comment from the manual says:

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.

Comment:

if you are repeating an statement in an loop using bind_param and so on inside it for a larger operation. i thougt id would be good to clean it with stmt->close. but it broke always with an error after aprox. 250 operations . As i tried it with stmt->reset it worked for me.

Answer

cwallenpoole picture cwallenpoole · Jul 9, 2011

That is a good use of close, especially since you are planning on making another query. With both PDO statements and MySQLi statements, I find that erring on the side of cleanliness is almost always for the best -- it removes potential bugs down the line.

As to the gentlemen with 250 operations... I don't see what the real use case is. Why does he need to query the database 250 different times? Why can't he query the database once with 250 records? Or, more likely, why can't he query the database 25 times with 10 records?

Related questions

Can I bind an array to an IN() condition?

I'm curious to know if it's possible to bind an array of values to a placeholder using PDO. The use case here is attempting to pass an array of values for use with an IN() condition. I'd like to be …

Read More
PDO Prepared Inserts multiple rows in single query

I am currently using this type of SQL on MySQL to insert multiple rows of values in one single query: INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),... On the …

Read More
MySQLi prepared statements error reporting

I'm trying to get my head around MySQli and I'm confused by the error reporting. I am using the return value of the MySQLi 'prepare' statement to detect errors when executing SQL, like this: $stmt_test = $mysqliDatabaseConnection->stmt_init(); …

Read More