how to avoid the new PDO(...) line of code to appear too often?

Question

There is something, as a newbie, that a I want to understand about About database connections.

I am starting off from a tutorial on PHP which has this structure:

Connect.php:

<?php 

$username = "dbusername"; 
$password = "dbpassword"; 
$host = "localhost"; 
$dbname = "dbname"; 
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 

try 

{ 
    $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
} 
catch(PDOException $ex) 
{ 
    die("Failed to connect to the database: " . $ex->getMessage());  
} 

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 


header('Content-Type: text/html; charset=utf-8'); 

session_start(); 
?>

Login.php:

<?php
 require("connect.php");     
 // some code not important for this question, 
 //that handles login with a session…
?>

various_file_in_the_login_system.php:

 <?php
  require("connect.php");
  //  some code that checks  if user is logged in with session_ …
  // some code that does need the database connection to work
  ?>

All other files also contain that require("connect.php"); line. It works, but I just don’t know what these connection request to the server – I may not be using the right vocabulary -- end up doing to the server. They are superfluous if the connection is not timed out, are they not?

I found a post which talked about doing a singleton for PDO, and a post which makes me feel like never using persistent connections in my life.

Does this design causes excessive connection churning?

Perhaps servers can handle very many request for connection per second, perhaps a server has its own internal persistent connection mode, or implements connection pooling… Or the PDO object handle the problem of asking connection too often for no reason…

PDO + Singleton : Why using it?

What are the disadvantages of using persistent connection in PDO

Answer 1

This is what I can recommend for your database connection: Make a class for the connection:

class Database{
private static $link = null ;

public static function getConnection ( ) {
    if (self :: $link) {
        return self :: $link;
    }

    $dsn = "mysql:dbname=social_network;host=localhost";
    $user = "user";
    $password = "pass";

    self :: $link = new PDO($dsn, $user, $password);
    return self :: $link;
}

}

Then you can get the connection like this:

Database::getConnection();

The Singleton Pattern is hard to scale - However, I think it will probably be fine for your needs. It takes a lot of load off your database.

I don't think you will be able to avoid the multiple includes.

There is a php.ini setting for prepending a file to every script -> http://www.php.net/manual/en/ini.core.php#ini.auto-prepend-file

Answer 2

How about you change to

require_once('connect.php');

in all locations?

Also you should probably remove session_start() and HTTP header logic from a section of code that has to do with establishing a DB connection. This simply does not make sense there.