How to Check Whether mysqli connection is open before closing it

Question

I am going to use mysqli_close($connection) to close the $connection. But Before Closing I need to ensure that the concerned connection is open.

I tried

if($connection)
{
  mysqli_close($connection);
}

But it is not working. Any Solution?

Answer 1

This is from PHP.net.

Object oriented style

---

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* check if server is alive */
if ($mysqli->ping()) {
    printf ("Our connection is ok!\n");
} else {
    printf ("Error: %s\n", $mysqli->error);
}

/* close connection */
$mysqli->close();
?>

Procedural style

---

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* check if server is alive */
if (mysqli_ping($link)) {
    printf ("Our connection is ok!\n");
} else {
    printf ("Error: %s\n", mysqli_error($link));
}

/* close connection */
mysqli_close($link);
?>

Answer 2

While some suggest to check for $connection->ping(),$connection->stat() or mysqli_thread_id($connection), the three will throw: 'Couldn't fetch mysqli' if the connection was closed before.

The solution that worked for me was:

if(is_resource($connection) && get_resource_type($connection)==='mysql link'){
    $connection->close(); //Object oriented style
    //mysqli_close($connection); //Procedural style 
}

PS: Checking only if it's a resource could be enough in a controlled context.

Answer 3

you can check if mysqli server info is set.

$connection = new MySQLi('localhost', 'user', 'pass', 'db');
if(!isset($connection->server_info)){
    echo 'sql connection is closed';
}else{
    $connection -> close();
}

Answer 4

Just don't close it. That would be the best solution ever.

99.9% of time it's perfectly ok to leave the connection alone, PHP will close it for you.

Only if your script has to perform some heavy time consuming task that doesn't involve a database interaction, you may want to close the connection before starting this operation. But in this case you will have the connection deliberately open as there will be no random closures scattered around the code and therefore will be no need to check whether it is closed already. Hence, just close it, in this particular but extremely rare case.

All other time just leave it alone.

Answer 5

If you open a connection, it will stay open until it's explicitly closed or the script ends (unless persistent connections is on). Using the code you have should work.

One option is to extend the mysqli class and have a status property called $connected:

class CustomMysqli extends mysqli
{
    protected bool $connected;

    public function __construct($host, $username, $password, $database)
    {
        parent::__construct($host, $username, $password, $database);
        $this->connected = ($this->connect_errno === 0);
    }

    public function close(): void
    {
        if ($this->connected) {
            parent::close();
            $this->connected = false;
        }
    }

    public function isConnected(): bool
    {
        return $this->connected;
    }
}

Checking for the $connected property is a bit overkill, but will ensure the connection is still open.

Answer 6

The accepted answer didn't work for me because calling functions on the connection would throw a couldn't fetch mysqli error if it was already closed. I ended up inspecting the mysqli object before and after closing the connection:

// Before calling $connection->close()

object(mysqli)#92 (18) {
  ["affected_rows"]=>
  int(0)
  ["client_info"]=>
  string(14) "mysqlnd 7.4.23"
  ["client_version"]=>
  int(11111)
  ["connect_errno"]=>
  int(0)
  ["connect_error"]=>
  NULL
  ["errno"]=>
  int(0)
  ["error"]=>
  string(0) ""
  ["error_list"]=>
  array(0) {
  }
  ["field_count"]=>
  int(0)
  ["host_info"]=>
  string(73) "host.rds.com via TCP/IP"
  ["info"]=>
  NULL
  ["insert_id"]=>
  int(0)
  ["server_info"]=>
  string(10) "1.1.1-log"
  ["server_version"]=>
  int(11111)
  ["sqlstate"]=>
  string(5) "00000"
  ["protocol_version"]=>
  int(10)
  ["thread_id"]=>
  int(1234567)
  ["warning_count"]=>
  int(0)
}

// After calling $connection->close()

object(mysqli)#92 (3) {
  ["client_version"]=>
  int(11111)
  ["connect_errno"]=>
  int(0)
  ["connect_error"]=>
  NULL
}

Based on that info, I just wrote the following simple function:

private function isClosed() : bool
{
    return !is_int($this->connection->thread_id);
}

Answer 7

Check connection errors. mysqli_connect() always returns a MySQLi object.

use this:

$mysqli_connection = new MySQLi('localhost', 'user', 'pass', 'db');
if ($mysqli_connection->connect_error) {
   echo "Not connected, error: " . $mysqli_connection->connect_error;
}
else
{
   echo "Connected.";
}

Answer 8

Try this:

function close_connection(){
    $thread = $mysqli->thread_id;
    $mysqli->close();
    $mysqli->kill($thread);

}

That will close the connection you opened using object oriented style like this:

$mysqli = new mysqli($db_server, $db_username, $db_password, $db_name);

That's the basic idea, but if you're using the procedural style, I'm sure you'll be able to custom the code as you need.