How to reduce database connection by making one PDO instance for your entire project

Question

I have a class I have made that looks like this:

class Database 
{
    const DB_HOST = "localhost";
    const DB_NAME = "office";
    const DB_USERNAME = "root";
    const DB_PASSWORD = "root";
    public $connection;

    function Database()
    {
        $this->connection = new PDO("mysql:host=".Database::DB_HOST.";dbname=".Database::DB_NAME, Database::DB_USERNAME, Database::DB_PASSWORD);
    }
}

I wanna use a single instance for the database using $database = new Database();

My problem is functions not finding it.

I rather not use GLOBAL $database nor use $_SESSION to solve it.

Any other ways I've missed?

Do you mean a singleton class? http://stackoverflow.com/questions/8776788/best-practice-on-php-singleton-classes — xd6_, May 01 '14 at 13:20
@xd6_, Nope don't use a singleton , go for dependency injection instead. — Shankar Damodaran, May 01 '14 at 13:20
No, I'm actually looking for a way to use the instance in functions without having to globalize it in every function I make, nor making a new instance in every function. — Jacob Cohen, May 01 '14 at 13:21
@Shankar is right, but DI is a bit more involved than a singleton. There's a balance between doing it perfectly and understanding everything, of course. — halfer, May 01 '14 at 13:22
Jacob, a singleton will do that for you - a static variable in a function or static method will maintain one instance without interfering with globals. — halfer, May 01 '14 at 13:24

score 0 · Accepted Answer · edited May 23 '17 at 12:11

0

What you could do is making a Singleton class BUT Because PDO uses a connection from a pool the construction of a new PDO is very very ligthweight and i think it's just a good practice just to reconstruct a new PDO (or Database)

class Database {

    private static $instance = null;

    private __construct() {

    }

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

You can reach the object by using

$database = Database::getInstance(); //this wil not construct a new Database object

Still i really discourage the approach because constructing a new PDO object is so leightweight you won't even notice the difference

More information at http://en.wikipedia.org/wiki/Singleton_pattern

Just an example of releasing PDO See: How to close a PDO handle

for($i = 0; $i < 1000; $i++) {
   //This happens somewhere in the loop
   $pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
                     PDO::ATTR_PERSISTENT => true
                 ));
   //use $pdo
   unset($pdo); //This will release your connection and returns it to the pool
}

edited May 23 '17 at 12:11

Community

1
1

answered May 01 '14 at 13:27

Sander Visser

3,797
1
27
41

Will functions OUTSIDE this class be able to reach this instance? – Jacob Cohen May 01 '14 at 13:29
@JacobCohen yes Database::getInstance() will return the instance – Sander Visser May 01 '14 at 13:33
Even though it is lightweight, when you have 100k people entering your system simultaneously, and a page have a function that uses connection in a loop, the amount of connection will very fast be reached. It seems like singleton is a perfect solution to reduce that, even though I still need to write getInstance for each function, which I was trying to avoid... It basically just like globalizing the database instance on each function. – Jacob Cohen May 01 '14 at 13:39
@JacobCohen that's why you can construct a PDO before the looping, and even when you construct a PDO in a loop, you can release the object when you are done the connection will be returned to the pool. but you can use the singleton approach – Sander Visser May 01 '14 at 13:41
@JacobCohen I added the example for in a loop ;) bit simple but i'l think you get it – Sander Visser May 01 '14 at 13:45
That's what Ive been doing so far... Are you telling me I shouldn't change anything? :P – Jacob Cohen May 01 '14 at 13:52
With the unset? i could be wrong tho =) I'm not programming that much in php anymore – Sander Visser May 01 '14 at 13:54
http://www.php.net/manual/en/pdo.connections.php Check the persistent connections ;) – Sander Visser May 01 '14 at 13:56
1

Yeah I saw that, but this is overkill. I want a different connection everytime someone access a certain page. However I do not want it to open a new one every FUNCTION, coz you may call a function many times in one script. Still your first solution fits better to my needs. – Jacob Cohen May 01 '14 at 14:00

score -1 · Answer 2 · answered May 01 '14 at 13:25

-1

You want to use a constructor here

class Database 
    {
        public $conn;
        public function __construct()
        {
            $DB_HOST = '';
            $DB_NAME = '';
            $DB_USERNAME = '';
            $DB_PASSWORD = '';
            $this->conn= new PDO("mysql:host=".$DB_HOST.";dbname=".$DB_NAME, $DB_USERNAME, $DB_PASSWORD);
        }
    }

answered May 01 '14 at 13:25

Daan

11,291
6
26
47

@downvoter please explain? – Daan May 01 '14 at 13:26
Using the class name as a function (without public before it) is a constructor. – Jacob Cohen May 01 '14 at 13:27
@Jacob, yes, although that approach is no longer recommended. Always use `__construct()` for your constructors, and always declare their access level too. – halfer May 01 '14 at 13:28
@JacobCohen As of PHP 5.3.3, methods with the same name as the last element of a namespaced class name will no longer be treated as constructor – Daan May 01 '14 at 13:36
I dont use no namespaces in my project, is there a threat still? – Jacob Cohen May 01 '14 at 14:01
Not really but it is bad practice to use the class name as constructor like you do in your example. – Daan May 01 '14 at 14:03
I guess im too used to C#.. – Jacob Cohen May 01 '14 at 14:06
Btw Daan I wasnt the downvoter, I just assumed this is why he down voted. – Jacob Cohen May 01 '14 at 14:08

How to reduce database connection by making one PDO instance for your entire project

2 Answers2