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 it 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...

Answer 1

You can create and maintain a single/reusable connection per-database and make sure that the connection has been set up properly. It's recommended to use both anonymous function and factory pattern for dealing with PDO connection this way :

$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 );

Use this for a different file or lower in the same file:

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

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

The factory can be used 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 will let you have a centralized structure, which will make sure that the connection is created only when required. It will also make the process of unit-testing and maintenance much easier. The provider in the above case can be found somewhere at the bootstrap stage. This approach will also give a clear location where to define the configuration, that you use for connecting to the DB.