Should I keep reconnecting to mysql in PHP?

Question

I have a pretty large site and every page is built from several included files, my site is 100% in a procedural format and I am trying to learn to use classes and a more OOP approach in PHP.

Currently my site has a header file that is included into every page, in this header is a mysql connection that is made and last the duration of the page, so if I need to run 10 different queries from different files, they all run without needing to make a new connection, so the connection is only made once.

Now that I am trying to convert to a more OO way, I am starting with writing a mysql class to connect and run queries, so I am thinking of using the classes __construct function to make a connection to mysql, I am just curious how this would work though, everytime that class gets called it would make or try to make a connection to mysql instead of just once.

Maybe I am not thinking it out clearly. Should I just initiate this class in the header 1 time and then I wont have to worry anymore?

Answer 1

You should pass a connection object (probably PDO) around, and the various places should be able to pick that up, either as a parameter, or as a property of some central object which the others have a reference to, or something.

Having multiple connections can be useful, it seems insane that mysql_connect picks up an existing connection when you might have wanted a new one - but it's insane anyway. Just use PDO.

Answer 2

You could create a single global object of your MySQL class and use that object everywhere. Then your constructor would only be called once.

Or you could create new objects of your MySQL class everywhere. mysql_connect doesn't open new connections if there already is one open:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

Answer 3

The best way I think is to use a special class to handle mysql connections and use it as a singleton. Make the constructor private and get it to return an instance of either an existing connection or a new one.

Here's my example:

class db 
{

    public $host;
    public $user;
    public $pass;
    public $database;

    private static $instance = false;

    private function __construct() 
    {

    }

    public static function getInstance()
    {
        if (self::$instance === false)
        {
            self::$instance = new db;
        }
        return self::$instance;
    }

        public function db_connect()
        {
        }

        public function db_disconnect()
        {
        }
}

This way, whenever you call: db::getInstance()->db_connect(), you are certain there's only going to be ONE instance of that connection everywhere.

Answer 4

You can use that method if you use mysql_pconnect() function, wich will search if there's already a mysql connection and in case it finds it, it wouldn't create another one.

In alternative, if you consider nor to use instances in php, you can call php database object directly, like :

class DB {}

DB::connect($host, $user, $pass);

If you use this method, you don't need to worry about multiple connections. Of course that if you need to use several connections to more than one database at a time, you can make use of object instances, or make your class so it can take several parameters and store them all at once (not very recomemded this one)

Answer 5

Yes, you shouldn't connect multiple times. The overhead of opening and closing the connection all the time is bigger than the cost of keeping it open during the relative small time your scripts run. So you should create an instance of the class at the start and keep it in a global variable.

It's certainly not a bad idea to write your own classes as a exercise, but maybe you should look into one of the existing solutions for database connection management (Zend_Db etc.).

Answer 6

You can always store the database link reference in a STATIC class variable and call it whenever needed. However, PHP by itself tries to use an existing link if it exists in the memory.

I've a sample database handler code for you, ofcourse its PHP 5 and uses PDO.

<?php
// Class providing generic data access functionality
class DatabaseHandler
{
  // Hold an instance of the PDO class
  private static $_mHandler;

  // Private constructor to prevent direct creation of object
  private function __construct()
  {
  }

  // Return an initialized database handler 
  private static function GetHandler()
  {
    // Create a database connection only if one doesn’t already exist
    if (!isset(self::$_mHandler))
    {
      // Execute code catching potential exceptions
      try
      {
        // Create a new PDO class instance
        self::$_mHandler =
          new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);

        // Configure PDO to throw exceptions
        self::$_mHandler->setAttribute(PDO::ATTR_ERRMODE,
                                       PDO::ERRMODE_EXCEPTION);
        self::$_mHandler->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
      }
      catch (PDOException $e)
      {
        // Close the database handler and trigger an error
        self::Close();
        trigger_error($e->getMessage(), E_USER_ERROR);
      }
    }

    // Return the database handler
    return self::$_mHandler;
  }
  // Clear the PDO class instance
  public static function Close()
  {
    self::$_mHandler = null;
  }
  // Wrapper method for PDO::prepare
  private static function Prepare($queryString)
  {
    // Execute code catching potential exceptions
    try
    {
      // Get the database handler and prepare the query
      $database_handler = self::GetHandler();
      $statement_handler = $database_handler->prepare($queryString);

      // Return the prepared statement
      return $statement_handler;
    }
    catch (PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }
  }

  // Wrapper method for PDOStatement::execute()
  public static function Execute($sqlQuery, $params = null)
  {
    // Try to execute an SQL query or a stored procedure
    try
    {
        $statement_handler = self::Prepare($sqlQuery);

      // Execute query
      $statement_handler->execute($params);
    }
    // Trigger an error if an exception was thrown when executing the SQL query
    catch(PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }
  }

  // Wrapper method for PDOStatement::fetchAll()
  public static function GetAll($sqlQuery, $params = null,
                                $fetchStyle = PDO::FETCH_ASSOC)
  {
    // Initialize the return value to null
    $result = null;

    // Try to execute an SQL query or a stored procedure
    try
    {
        $statement_handler = self::Prepare($sqlQuery);

      // Execute the query
      $statement_handler->execute($params);

      // Fetch result
      $result = $statement_handler->fetchAll($fetchStyle);
    }
    // Trigger an error if an exception was thrown when executing the SQL query
    catch(PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }

    // Return the query results
    return $result;
  }

  // Wrapper method for PDOStatement::fetch()
  public static function GetRow($sqlQuery, $params = null,
                                $fetchStyle = PDO::FETCH_ASSOC)
  {
    // Initialize the return value to null
    $result = null;

    // Try to execute an SQL query or a stored procedure
    try
    {

      $statement_handler = self::Prepare($sqlQuery);

      // Execute the query
      $statement_handler->execute($params);

      // Fetch result
      $result = $statement_handler->fetch($fetchStyle);
    }
    // Trigger an error if an exception was thrown when executing the SQL query
    catch(PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }

    // Return the query results
    return $result;
  }

  // Return the first column value from a row
  public static function GetOne($sqlQuery, $params = null)
  {
    // Initialize the return value to null    
    $result = null;

    // Try to execute an SQL query or a stored procedure
    try
    {
        $statement_handler = self::Prepare($sqlQuery);

      // Execute the query
      $statement_handler->execute($params);

      // Fetch result
      $result = $statement_handler->fetch(PDO::FETCH_NUM);

      /* Save the first value of the result set (first column of the first row)
         to $result */
      $result = $result[0];
    }
    // Trigger an error if an exception was thrown when executing the SQL query
    catch(PDOException $e)
    {
      // Close the database handler and trigger an error
      self::Close();
      trigger_error($e->getMessage(), E_USER_ERROR);
    }

    // Return the query results
    return $result;
  }
}
?>