PHP: Close mysql connection when using external connection file

0
votes

So most basic php/mysql examples show something like this (taken from W3 Schools as a laymans example):

<?php
$con=mysqli_connect("example.com","peter","abc123","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM Persons");

while($row = mysqli_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysqli_close($con);
?>

When using external connection files, what is the correct way to close the connection (if needed at all).

Eg. We separate the above into a connection.php file which contains

  <?php
    $con=mysqli_connect("example.com","peter","abc123","my_db");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }

?>

And then have a Query.php file that contains:

<?php
require('connection.php');
$result = mysqli_query($con,"SELECT * FROM Persons");

    while($row = mysqli_fetch_array($result))
      {
      echo $row['FirstName'] . " " . $row['LastName'];
      echo "<br />";
      }
 ?>

Is it necessary to close the connection. Should there be a mysqli_close($con); at the end of the query.php file, or does it go at the end of the connection.php file and theres some sort of way to link that the query.php file will run before the connection is closed?

And/or is closing the connection even needed? Does it just close automatically after the script is complete?

2
phpmysqldatabase-connection

2 Answers

2
votes

Open connections (and similar resources) are automatically destroyed at the end of script execution. However, you should still close or free all connections, result sets and statement handles as soon as they are no longer required. This will help return resources to PHP and MySQL faster.

Still, if your PHP script takes lots of time to execute, it's a good idea to close the connection when you don't have to do any request to the database anymore -- at least, if the long calculations are done after the queries.

This is especially true if your application is deployed on a shared hosting : your user account can generally only have a few connections opened at the same time. (That number of simultaneous opened connections can be pretty small on shared hosting ; it's generally bigger on private servers).

The reason we often don't close connections ourselfves is :

  • we generally don't really know when we have done all our queries -- this is especially true with pages that are made of lots of small "blocks" ; each one of those is independant from the others, and can do queries on its own ; so, when can we close the connection ?
  • web pages are generally quite fast to generate, so we don't really bother about closing the connection to DB.

I think this may help you to resolve your problem.

0
votes

It's better to use a class which perhaps extends Mysqli, for ie:

<?php

    // Database class
    class Database extends \mysqli
    {
        public function __construct($host, $user, $pass, $db)
        {
            //create connection
        }

        public function __destruct()
        {
            //close connection
            //will call this function when class closes or PHP stops
        }
    }

    //using the database
    $db = new Database('localhost', 'user', 'pass', 'db');
    $db->query("SELECT ....");