When to close Prepared Statement

Question

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.

Im curious the same thing... Watching this thread! – Drewdin Jul 08 '11 at 23:05 — Drewdin, Jul 08 '11 at 23:05

score 11 · Accepted Answer · answered Jul 08 '11 at 23:19

11

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?

answered Jul 08 '11 at 23:19

cwallenpoole

79,954
26
128
166

1

Do you say that it is only good for cleanliness? Because I have the problem that when I use two prepare statement without closing the first, I get an error (See http://stackoverflow.com/questions/37164371/php-prepare-method-not-working-when-calling-it-twice). Not really sure why, because I have never read that this is crucial – Adam May 12 '16 at 08:34

score 0 · Answer 2 · answered Nov 11 '20 at 01:19

I am unable to comment currently, so I am just providing an answer. When you run a prepared statement that queries the database for a result, it will not execute another query unless you remove the current result it is storing. $result = $stmt->get_result(). Secondly, If you will need the result from the first query to be saved so that you use it later, then I recommend using two result sets. The first stores the result from the first execution of $stmt and the second for the second execution. This might not answer the question directly, but it may help someone.

When to close Prepared Statement

2 Answers2

Linked

Related