How to Properly Set Up a Pdo Connection

How to properly set up a PDO connection

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

Using PDO connection method

While moving to PDO (which is object oriented library), try to move your project to same manner of codding - the object oriented paradigm.

A simple architecture you can build would be a:

Class that makes the PDO connection (i.e. Database)
Config file that has the credentials
A class (i.e. Base) that is extended from each of your other classes that interact with database. This class should contain the instance of your database class (i.e. protected $_db, in constructor $this->_db = new Database())
Each of your classes that interact of database (extends Base) can now do $this->_db->prepare(....)

So the most important thing here is your connection to be an object which is shared through your database interaction classes.

About the loop. You will hardly be able to use while() for methods return value. In fact you will be, but it's an overkill.

Your methods should return the fetched result:

public function getUsers() {
    $query = $this->_db->prepare("SELECT username FROM users");
    $query->execute();
    return $query->fetch(PDO::FETCH_ASSOC);
}

or (this should not be necessary):

while ($row = $query->fetch(PDO::FETCH_ASSOC) {
    $users[] = $row;
}
return $users;

This way your getUsers() method will return the result set as an associative array, and the best built-in function in PHP for dealing with arrays is foreach(), so you'd need to iterate through the method return value:

foreach ($obj->getUser() as $row) {
    // do smth
}

How to create PDO connection in __construct()?

To access class properties, you have to use -> syntax:

Class myclass
{
    private $db;

    public function __construct() {

        $this->db = new PDO("mysql:host = hostname; dbname = database",username, password);
    }

    function one()   {/* it needs to database and I will use it like this: */
        $this->db->prepare("select ...");
    }
    function two()   {/* also it needs to database */}
    function three() {/* also it needs to database */}
}

If you want to detect errors, enable error signalling with

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

and use try/catch around the code that uses the DB:

function one() {
    try {
        $this->db->prepare(...);
    } catch (PDOException $e) {
        // handle error
    }
}

How to handle PDO connection errors properly?

Your current code has some flaws. Let me give you few pointers.

You do not need the function you have created, at least not in the current form. Every time you call this function it creates a new PDO object, which can hinder your script's performance. Ideally you would want to have only one connection throughout the execution of your whole script.
When creating new PDO connection you need to remember 3 things: to set proper connection charset, enable error reporting, and disable emulated prepares.
- Proper charset is important. Without it your data could get corrupted or even leave you vulnerable to SQL injection. The recommended charset is utf8mb4.
- Enabling error reporting saves you the trouble of manually checking every single function call for failures. You just need to tell PHP to trigger exceptions when an error occurs and find a suitable way of logging the errors on your server (read more: My PDO Statement doesn't work)
- Emulated prepares are enabled by default, but the truth is you are better off without them. If your database supports native prepared statements, and most do, then use them instead.
The full code should look something like this:
```
$options = [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new \PDO('mysql:host=localhost;dbname=dbname;charset=utf8mb4', 'user', 'pw', $options);
```
Don't catch the exceptions unless you know what to do with them and are positively sure you need to do so. Exceptions are best left be; let PHP handle them together with the other exceptions/errors/warnings. After all why make an exception just for PHP exceptions? All PHP errors should be handled the same. Whatever you do never print (including die, exit, echo, var_dump) the error message manually on the screen. This is a huge security issue if such code ever makes its way into production.

If your PDO connection is set to throw exceptions on errors, you never need to use if statements to check return code of prepare() or execute().

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

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
}

PDO: Where to declare the database connection?

You're close but your fetch function won't work because $dbh is outside its scope.

You could globalize it but a better solution is to pass your handler to your class upon instantiation

class Example {
    /** @var \PDO */
    protected $pdo;

    public function __construct(\PDO $pdo) {
         $this->pdo = $pdo;
    }
}
$class = new Example($dbh);

This is a best practice. This way the logistics of setting up and naming your db pointer are irrelevant. Your class defines how it's going to receive it and you use the instance of the pointer you were passed.

How to create 2 PDO database connection instances PHP

You could create your User class and pass the connection to the constructor. This will give you flexibility to swap out the connection.

Regarding your database class, it seems to be a wrapper, to create a PDO connection. You could do away with it, or extend the class with different params.

Perhaps look at dependency injection, and containers. They might help you here.

<?php  
class User
{   

    private $conn;

    public function __construct(PDO $conn)
    {
        $this->conn = $conn;
    }

    public function runQuery($sql)
    {
        $stmt = $this->conn->prepare($sql);

        return $stmt;
    }
}

How to Properly Set Up a Pdo Connection