PDO class inherit connection, is there a better way?

[jiros1]

I want to clean up my code by inheriting the database class so I can connect from any class that wants to inherit the database connection.

 

I'm not sure this is the right way but I thought about fixing this by inheriting the __construct function, but how would I call it in this example?

 

Currently I have this; it works, but could this be improved? Or is there a better, cleaner way to do this?

        $pdo = parent::__construct();

My code:

class database {
     function __construct(){
        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "temp";
        $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $pdo;
    }
}

class user extends database {
    public function getAll(){
        $pdo = parent::__construct();
        $sql = "SELECT name FROM users";
        $stmt = $pdo->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);    
        return $result;
    }    
}

$user = new user;    
$getAllUsers = $user->getAll();
foreach($getAllUsers as $row){
    echo $row['name'];
}

[Jacques1]

You certainly don't want to open a new database connection for every instance of your classes. In fact, you're currently opening a new connection through the inherited constructor and then yet another connection whenever the getAll() method is called. That means a single object will flood the database server with lots of useless connection requests when it really just needs one connection.

 

Instead, create exactly one PDO instance outside of the objects and pass it to the constructor:

<?php

class Model
{
    protected $databaseConnection;

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

<?php

class User extends Model
{
    public function test()
    {
        var_dump($this->databaseConnection);
    }
}

<?php

$databaseConnection = new PDO(...);

$user = new User($databaseConnection);
$user->test();

And again:

• Don't use prepared statements for purely static queries. That's what query() is for.
• You must disable emulated prepared statements when connecting to PDO, otherwise you're not safe from SQL injection attacks.

[jiros1]

Thanks for your quick reply, Jacques1, it looks a lot better now thanks to you:

const DB_HOST = 'yourhost';
const DB_USER = 'user';
const DB_PASSWORD = 'pswd';
const DB_NAME = 'db';
const DB_CHARSET = 'UTF8';
$dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHARSET;
$databaseConnection = new PDO($dsn, DB_USER, DB_PASSWORD, [
    PDO::ATTR_EMULATE_PREPARES => false,                    // important: Disable emulated prepared statements:
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);


class Model {
    protected $databaseConnection;
     public function __construct(PDO $databaseConnection){
         $this->databaseConnection = $databaseConnection;
    }
}

class user extends Model {
    public function getAll(){
        $sql = "SELECT name FROM users";
        $stmt = $this->databaseConnection->query($sql);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);    
        return $result;
    }    
}

$user = new user($databaseConnection);    
$getAllUsers = $user->getAll();
foreach($getAllUsers as $row){
    echo $row['name'];
}

Also, thanks for the reminder:

• Don't use prepared statements for purely static queries. That's what query() is for.
• You must disable emulated prepared statements when connecting to PDO, otherwise you're not safe from SQL injection attacks.

 

[Jacques1]

The getAll() method should be

public function getAll()
{
    return $this->databaseConnection->query('SELECT name FROM users')->fetchAll();
}

A query doesn't need to be executed, and PDO::FETCH_ASSOC is your default fetch mode.