How to properly set up a PDO connection

by

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:

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

More Related Contents:

  1. How to apply bindValue method in LIMIT clause?
  2. Getting raw SQL query string from PDO prepared statements
  3. PDO with INSERT INTO through prepared statements [closed]
  4. Use bound parameter multiple times
  5. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  6. Do SQL connections opened with PDO in PHP have to be closed
  7. PDO and MySQL ‘between’
  8. Query database then email posts from where user is subbed to
  9. Insert form in MySQL with PDO [duplicate]
  10. Undefined index $_POST
  11. PDO::fetchAll vs. PDO::fetch in a loop
  12. PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused
  13. Real escape string and PDO [duplicate]
  14. SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 102 [closed]
  15. Comma separated values in MySQL “IN” clause
  16. PHP PDO vs normal mysql_connect
  17. Can I create a database using PDO in PHP?
  18. Doing a while / loop to get 10 random results
  19. PHP PDO and MySQLi [duplicate]
  20. PDO fetch one column from table into 1-dimensional array
  21. Using value of a column as index in results using PDO
  22. Getting a PHP PDO connection from a mysql_connect()?
  23. Having issue with matching rows in the database
  24. PHP Laravel: No connection could be made because the target machine actively refused it
  25. PDO bindParam into one statement?
  26. UNION syntax in Cakephp
  27. How do i search from serialize field in mysql database?
  28. What is the PDO equivalent of function mysql_real_escape_string?
  29. Installing PDO driver on MySQL Linux server
  30. PDO Exception Questions – How to Catch Them

Leave a Comment