How to properly set up a PDO connection

Question

From time to time I see questions regarding connecting to database.
Most answers is not the way I do it, or I might just not get the answers correctly. Anyway; I've never thought about it because the way I do it works for me.

But here's a crazy thought; Maybe I'm doing this all wrong, and if that's the case; I would really like to know how to properly connect to a MySQL database using PHP and PDO and make it easy accessible.

Here's how I'm doing it:

First off, here's my file structure (stripped down):

public_html/

* index.php  

* initialize/  
  -- load.initialize.php  
  -- configure.php  
  -- sessions.php   

index.php
At the very top, I have require('initialize/load.initialize.php');.

load.initialize.php

#   site configurations
    require('configure.php');
#   connect to database
    require('root/somewhere/connect.php');  //  this file is placed outside of public_html for better security.
#   include classes
    foreach (glob('assets/classes/*.class.php') as $class_filename){
        include($class_filename);
    }
#   include functions
    foreach (glob('assets/functions/*.func.php') as $func_filename){
        include($func_filename);
    }
#   handle sessions
    require('sessions.php');

I know there's a better, or more correct, way to include classes, but can't remember what it was. Haven't gotten the time to look into it yet, but I think it was something with autoload. something like that...

configure.php
Here I basically just override some php.ini-properties and do some other global configuration for the site

connect.php
I've put the connection onto a class so other classes can extends this one...

class connect_pdo
{
    protected $dbh;

    public function __construct()
    {
        try {
            $db_host = '  ';  //  hostname
            $db_name = '  ';  //  databasename
            $db_user = '  ';  //  username
            $user_pw = '  ';  //  password

            $con = new PDO('mysql:host='.$db_host.'; dbname='.$db_name, $db_user, $user_pw);  
            $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $con->exec("SET CHARACTER SET utf8");  //  return all sql requests as UTF-8  
        }
        catch (PDOException $err) {  
            echo "harmless error message if the connection fails";
            $err->getMessage() . "<br/>";
            file_put_contents('PDOErrors.txt',$err, FILE_APPEND);  // write some details to an error-log outside public_html  
            die();  //  terminate connection
        }
    }

    public function dbh()
    {
        return $this->dbh;
    }
}
#   put database handler into a var for easier access
    $con = new connect_pdo();
    $con = $con->dbh();
//

Here I do believe there's room for massive improvement since I recently started learning OOP, and using PDO instead of mysql.
So I've just followed a couple of beginners tutorials and tried out different stuff...

sessions.php
Beside handling regular sessions, I also initialize some classes into a session like this:

if (!isset($_SESSION['sqlQuery'])){
    session_start();
    $_SESSION['sqlQuery'] = new sqlQuery();
}

This way this class is available all over the place. This might not be good practice(?)...
Anyway, this is what this approach allows me to do from everywhere:

echo $_SESSION['sqlQuery']->getAreaName('county',9);  // outputs: Aust-Agder (the county name with that id in the database)

Inside my sqlQuery-class, which extends my connect_pdo-class, I have a public function called getAreaName which handles the request to my database.
Pretty neat I think.

Works like a charm
So that's basically how I'm doing it.
Also, whenever I need to fetch something from my DB from not within a class, I just do something similar to this:

$id = 123;

$sql = 'SELECT whatever FROM MyTable WHERE id = :id';
$qry = $con->prepare($sql);
$qry -> bindParam(':id', $id, PDO::PARAM_INT);
$qry -> execute();
$get = $qry->fetch(PDO::FETCH_ASSOC);

Since I put the connection into a variable inside connect_pdo.php, I just have referring to it and I'm good to go. It works. I get my expected results...

But regardless of that; I would really appreciate if you guys could tell me if I'm way off here. What I should do instead, areas I could or should change for improvement etc...

I'm eager to learn...

Selfsatisfied

You should use an autoloader instead of including every single file in your application at once. – Moor

This question is probably best on Code Review – Toomey

Answer 1

The goal

As I see it, your aim in this case is twofold:

• create and maintain a single/reusable connection per database
• make sure that the connection has been set up properly

Solution

I would recommend to use both anonymous function and factory pattern for dealing with PDO connection. The use of it would looks like this :

$provider = function()
{
    $instance = new PDO('mysql:......;charset=utf8', 'username', 'password');
    $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    return $instance;
};

$factory = new StructureFactory( $provider );

Then in a different file or lower in the same file:

$something = $factory->create('Something');
$foobar = $factory->create('Foobar');

The factory itself should look something like this:

class StructureFactory
{
    protected $provider = null;
    protected $connection = null;

    public function __construct( callable $provider )
    {
        $this->provider = $provider;
    }

    public function create( $name)
    {
        if ( $this->connection === null )
        {
            $this->connection = call_user_func( $this->provider );
        }
        return new $name( $this->connection );
    }

}

This way would let you have a centralized structure, which makes sure that connection is created only when required. It also would make the process of unit-testing and maintenance much easier.

The provider in this case would be found somewhere at the bootstrap stage. This approach would also give a clear location where to define the configuration, that you use for connecting to the DB.

Keep in mind that this is an extremely simplified example. You also might benefit from watching two following videos:

• Global State and Singletons
• Don't Look For Things!

Also, I would strongly recommend reading a proper tutorial about use of PDO (there are a log of bad tutorial online).

Muslim

I'll look into this. I'm kinda new tho both OOP and PDO, so there's a lot for me to studdy on... Links like this is very useful for me... – Selfsatisfied

Since even PHP5.3 is approaching EOL. The majority of sites with outdated PHP versions are actually just cheap hosting for Wordpress. The impact of pre-5.3 environments on professional development (they kinda that would benefit from snippets like this) is negligible, in my estimate. – Codding

-1 because this answer is more or less then same as Ian's (he came up with it first) – Burgess

@Burgess I agree with you. It is more or less the same answer. That is if you do not see the fact that it is completely different. – Subarid

The 2nd point of Ian's answer "Use a singleton registry, and access that globally" is the equivalent of this answer. Sure, the poster doesn't mention the global part and he appears against it, but the fact is that the code above would be unusable in real life without some kind of globalization. What do you think would happen if I created more than one instance of StructureFactory? It needs to have a single instance. You can pass it around (as in point 3 of Ian's answer) or declared as global, but it's pretty much the same thing – Burgess

@Burgess , then you should learn what dependency injection is. Instead of continuing to embarrass yourself. Funnily enough, the second video in the post above (titled: "Don't Look For Things") actually explained what DI is and how to use it ... but of course you are too advanced for such trivial things. – Codding

In my case, my DataMapper (EntryMapper) class needs a PDO instance. Does this example provide an instance of PDO? I have tried to wrap the PDO into a class and then provide that class as an argument but the script complained about needing an instance of PDO not my Database class. This is my question: #22121015 – Golightly

This is an old answer, but a good one - and a great link to Mysql pdo at the end – Laevo

@Muslim Would you just have an include for this in whatever class needed access to StructureFactory? Can you tell me why I these kinds of classes if PDO is already an abstraction? Could I do the same thing here but instead of teh class StructureFactory, have access to an array of configurations that had the details of db1 (Sql Server), db2 (Oracle), db3 (PostgreSQL), etc.? Or would I be reapeating myself to much because I'd be typing new PDO(...) more than once? Thanks. I follow your answers here when I can find them. – Ramberg

I'd like to ask what if I need to call a static method from a class, say ClassA, that needs to perform some query. How will I have the connection instance? Should I call $factory->create("ClassA")? – Metropolitan

Gosh, it looks really nice BUT im stuck. How can you use this Database connection class to make a function with a prepared statement execute? Or cant it be done? Must other database queries also be a class ? How would that look like then? I've been using Mysql queries and now jumping into PDO..totally different :/ – Blueness

@Blueness you should start by first learning how to use PDO. I would recommend this tutorial: wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers, because it's made precisely for people who want to migrate from mysql_* to PDO. Then you can come back and look at this solutions, which is aimed at ones, who already use PDO, but need a way to share the DB connection between multiple classes. – Codding

Do you think it would be a good idea to replace utf8 in DSN with utf8mb4? A lot of people copy-paste and as we all know utf8 should not be used anymore. This could save some people a lot of trouble. – Regardant

Answer 2

I would suggest not using $_SESSION to access your DB connection globally.

You can do one of a few things (in order of worst to best practices):

• Access $dbh using global $dbh inside of your functions and classes

• Use a singleton registry, and access that globally, like so:

  $registry = MyRegistry::getInstance();
  $dbh = $registry->getDbh();
  

• Inject the database handler into the classes that need it, like so:

  class MyClass {
      public function __construct($dbh) { /* ... */ }
  }
  

I would highly recommend the last one. It is known as dependency injection (DI), inversion of control (IoC), or simply the Hollywood principle (Don't call us, we'll call you).

However, it is a little more advanced and requires more "wiring" without a framework. So, if dependency injection is too complicated for you, use a singleton registry instead of a bunch of global variables.

Acie

So I access my db connection globally when I set my sqlQuery-class into session since it extends connect_pdo? – Selfsatisfied

Answer 3

I recently came to a similar answer/question on my own. This is what I did, in case anyone is interested:

<?php
namespace Library;

// Wrapper for \PDO. It only creates the rather expensive instance when needed.
// Use it exactly as you'd use the normal PDO object, except for the creation.
// In that case simply do "new \Library\PDO($args);" with the normal args
class PDO
  {
  // The actual instance of PDO
  private $db;

  public function __construct() {
    $this->args = func_get_args();
    }

  public function __call($method, $args)
    {
    if (empty($this->db))
      {
      $Ref = new \ReflectionClass('\PDO');
      $this->db = $Ref->newInstanceArgs($this->args);
      }

    return call_user_func_array(array($this->db, $method), $args);
    }
  }

To call it you only need to modify this line:

$DB = new \Library\PDO(/* normal arguments */);

And the type-hinting if you are using it to (\Library\PDO $DB).

It's really similar to both the accepted answer and yours; however it has a notably advantage. Consider this code:

$DB = new \Library\PDO( /* args */ );

$STH = $DB->prepare("SELECT * FROM users WHERE user = ?");
$STH->execute(array(25));
$User = $STH->fetch();

While it might look like normal PDO (it changes by that \Library\ only), it actually doesn't initialize the object until you call the first method, whichever it is. That makes it more optimized, since the PDO object creation is slightly expensive. It's a transparent class, or what it's called a Ghost, a form of Lazy Loading. You can treat the $DB as a normal PDO instance, passing it around, doing the same operations, etc.

Rarefy

This is called "Decorator pattern" – Provisory

Answer 4

There are a few basic flaws in your setup:

1. The configure.php file shouldn't be in the web server's document root — if the server is ever missconfigured you might expose credentials to the public. You might think it won't happen to you, but it's a risk you just don't need to be taking. Classes also shouldn't be there... this isn't as important but still anything that doesn't need to be public shouldn't be public.
2. Unless you're working with an especially large project, you shouldn't have an "initialization" directory. Loading one large file is approximately 10x faster than loading ten small files with the same contents. This tends to really add up as a project grows and can really slow down PHP sites.
3. Try not to load things unless you actually need to. For example don't connect with PDO unless you actually need to. Don't session_start() you actually read/write to session. Don't include a class definition file unless you create an instance of the class. There are limits to how many connections you can have. And APIs like session establish "locks" that can pause code execution for other people using the same resource.
4. As far as I can tell, you're not using Composer. You should be using it - it'll make life so much easier both for your own code and for third party dependencies.

Here's my proposed directory structure, which is similar to what I use for medium sized projects:

init.php                Replaces public_html/initialize. Your PDO connection details
                        are held here.
classes/                Replaces public_html/classes
vendor/autoload.php     Your class autoload script generated using the
                        industry standard Composer command line tool
composer.json           The file where you describe how autoload.php
                        operates among other things. For example if you
                        don't use namespaces (maybe you should) it might be:
                        {"autoload": {"psr-4": { "": "classes/" }}}
public_html/index.php   Your landing page
public_html/other.php   Some other page
public_html/css/foobar.css ...and so on for all static resources

The init.php file might look something like:

date_default_timezone_set('Etc/UTC');

require 'vendor/autoload.php';

$pdoConnect = function() {
  static $pdo = false;
  if (!$pdo) {
    $pdo = new PDO('mysql:dbname=db;host=localhost', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
  }
  return $pdo;
};

// similar anonymous functions for session_start(), etc.

index.php might look like:

require '../init.php';

$pdo = $pdoConnect();

// go from there

other.php might be similar but maybe it doesn't connect to the database, therefore doesn't execute $pdoConnect.

As much as possible, you should write the bulk of your code into the classes directory. Keep index.php, other.php, etc as short and sweet as possible.

Splashdown

Answer 5

$dsn = 'mysql:host=your_host_name;dbname=your_db_name_here'; // define host name and database name
    $username = 'you'; // define the username
    $pwd='your_password'; // password
    try {
        $db = new PDO($dsn, $username, $pwd);
    }
    catch (PDOException $e) {
        $error_message = $e->getMessage();
        echo "this is displayed because an error was found";
        exit();
}

Xenophobia