Closing queries and connection

Question

I am learning MySQL and PHP and I want to dispel this doubt. Maybe is stupid, but I want to learn in the good way and I didn´t find the answer.

So, I have several QUERIES and my doubt is: Do I have to close with $result->close(); EACH ONE or JUST ONE at the final with $conn->close();

Example 1: Closing EACH ONE

$sql = "CREATE TABLE atable (
idatable INT(5) NOT NULL AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
lastname VARCHAR(10) NOT NULL,
PRIMARY KEY(idatable))";

$result = $conn->query($sql);
if(!$result) {
    die($conn->error);
}


$result->close();


$sql = "DESCRIBE atable";
$result = $conn->query($sql);
if(!$result) {
    die($conn->error);
}

// Here more code...
$result->close();


$sql = "SELECT * FROM atable";
$result = $conn->query($sql);
if(!$result) {
    die($conn->error);
}


// Here more code...
$result->close();


$conn->close();

Example 2: closing ONE time at the END

$sql = "CREATE TABLE atable (
idatable INT(5) NOT NULL AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
lastname VARCHAR(10) NOT NULL,
PRIMARY KEY(idatable))";

$result = $conn->query($sql);
if(!$result) {
    die($conn->error);
}


$sql = "DESCRIBE atable";
$result = $conn->query($sql);
if(!$result) {
    die($conn->error);
}


// Here more code...


$sql = "SELECT * FROM atable";
$result = $conn->query($sql);
if(!$result) {
    die($conn->error);
}


// Here more code...


$result->close();
$conn->close();

Remember that I am learning! Thanks!

[This](http://stackoverflow.com/questions/2417834/mysqli-query-results-best-approach-do-you-close-free-both) may help, you could if using a class have something like `$db->close()` in your __destruct. — Script47, Jan 07 '16 at 00:55
PHP will automatically close connections and results when the script finishes. So you really only have to do it manually if you think you are using enough of them to cause a memory/resouce issue — RiggsFolly, Jan 07 '16 at 00:56
@RiggsFolly Ok... So I could avoid them, but if I want to close... Which would be the best: example 1 or example2? And thanks all for your reviews. — Peter, Jan 07 '16 at 00:59
@Shadow we have to assume they are a connection handle and a result handle. I dont think that is the important part of this question — RiggsFolly, Jan 07 '16 at 00:59
I am note sure it actually makes a lot of difference. Definitely dont close the connection until you are sure you wont use it again as starting a new connection is slow in terms of code execution time — RiggsFolly, Jan 07 '16 at 01:01
@RiggsFolly So... You would go for example2? hahaha... I just need to clarify it in my mind to do it or not at future. It´s to have a "good" basis. Thanks! — Peter, Jan 07 '16 at 01:05
@RiggsFolly I disagree. Just because php does sg when it completes a job does not necessarily mean that it is the right thing to wait until php ultimately does it. — Shadow, Jan 07 '16 at 01:06
No problem. As php scripts, at least web site based scripts tend to be short and sweet, i would never bother closing either. If you are writing a CLI script that might run for hours, then I would consider cleaning up after myself rather than leaving it for PHP to do. — RiggsFolly, Jan 07 '16 at 01:07
Actually I will amend that. If you are using large queries ( lots of results ) and you are using a buffered query, then there is a good reason for closing/freeing the result handle, as a buffered query will load all results into memory and then let you pull them from memory rather than going back to the database to get each row as in an unbuffered query — RiggsFolly, Jan 07 '16 at 01:15
@RiggsFolly But doing it as example 1 or with a public function like Juakali92 said. Again... Remember that I am learning :D hahaha — Peter, Jan 07 '16 at 01:17
Well a `public function(Class Method)` or a simple `function` just makes it automatic by virtual of the variable going out of scope. So resources are only actually released when the Garbage collector kicks in, which only happens when PHP realises it is short of memory. So as likely as not it wont happen before your script actually finishes anyway. — RiggsFolly, Jan 07 '16 at 01:24
@RiggsFolly Thanks a lot! Now it is more clear! So: I will not close them at least for my "actual scripts". — Peter, Jan 07 '16 at 01:28

score 1 · Answer 1 · edited May 23 '17 at 11:51

As answered in a similar post as seen below, If you were to not close each result after it's been executed, it would be stored into a memory buffer. Therefore it would be effective use of result->close() if the result was using high amounts of memory (large query results).

However in most cases it's not required and results are free'd when a variable reaches the end of it's scope. I would look into putting you code into a function and then you wouldn't need to address this issue at all. E.g

public function query($sql){

$result = $conn->query($sql);
if(!$result) {
   die($conn->error);
}
   $result->close();

}

or something simililar. Hope this helped.

Why do we close result in Mysqli

Closing queries and connection

1 Answers1