Jump to content

PDO class inherit connection, is there a better way?


Go to solution Solved by Jacques1,

Recommended Posts

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'];
}
Link to post
Share on other sites
  • Solution

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.
  • Like 2
Link to post
Share on other sites

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.

 

Link to post
Share on other sites

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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.