[PHP]: Connection variable in a class generates error while closing connection.

Question

Below is my DB connection class. The problem is that when I try to access the CloseConnection function from my code, it gives error: "Unknown MySQL localhost". I use "dbconnection::CloseConnection" through my other code files. It is successfully opening the connection, but giving error in "$conn".

final class dbconnection 
{

    private $conn;
     //Opens connection for a MySQL DB
     public static function OpenConnection()
     {
      require("../Config/dbconfig.php");
      $conn = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);

      mysql_select_db('MyDB');  

     }

     //Closes connection for a MySQL DB
     public static function CloseConnection()
     {
      mysql_close($conn);

     }
}

Below is a method in another PHP file from where I access the above functions:

public static function InsertRecord($inQuery)
{
     dbconnection::OpenConnection();
     $resultSet = mysql_query($inQuery);
     dbconnection::CloseConnection();
     return $resultSet;  
}

When I remove the line "dbconnection::CloseConnection()", it works fine. I also want to know whether it is a good practice to immediately close the connection as the DB task is finished, or should I keep it open till the use closes the browser?

Answer 1

Try:

class dbconnection  {
  private static $conn;

  public static function OpenConnection() {
    require("../Config/dbconfig.php");
    self::$conn = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);
    mysql_select_db('MyDB');  
  }

  public static function CloseConnection() {
    mysql_close(self::$conn);
  }
}

What you're doing is trying to access the member variable $conn but you're doing it incorrectly so you're simply creating a new (local) variable to use. That doesn't matter on open (the reference is simply discarded) but on close you're not dealing with a resource reference anymore.

Secondly, your functions are static but your member variable isn't. These are the two methods you use for accessing member variables:

class A {
  private $a;
  private static $b;

  public static function foo() {
    $this->a = 3; // instance member
    self::$b = 4; // static member
  }
}

Edit: regarding the last function, it works when you remove the call to CloseConnection() because it is no longer trying to access a database connection via the local variable $conn (which will cause an error).

No it isn't good practice to close a database connection immediately. The most common practice in PHP is simply to open a connection at the top of your script and then do nothing more. When the script exits it will close. Some people open connections on demand, meaning it won't try to create one until it needs one. This is an acceptable optimization (from the point of view of not adding unnecessary complexity). Generally the connection will never be explicitly closed. It will be closed implicitly when the script ends.

Long-lived scripts may want to take a different approach just so they don't unnecessarily hold open connections for extended periods.

You may at some point want to use persistent connections instead.

The above method is simple enough and sufficient enough for some pretty large sites. As a general rule, don't try to optimize this stuff and introduce unneeded complexity until you actually have a problem that you need to do something about.