Use of Pdo in Classes

Use of PDO in classes

You can instantiate your connection to the database in a class that implement the singleton pattern.
The connection will be done once and this class will be easily accessible by all of your other objects / scripts.

i use a class called "Core" in the following example;

class Core
{
    public $dbh; // handle of the db connexion
    private static $instance;

    private function __construct()
    {
        // building data source name from config
        $dsn = 'pgsql:host=' . Config::read('db.host') .
               ';dbname='    . Config::read('db.basename') .
               ';port='      . Config::read('db.port') .
               ';connect_timeout=15';
        // getting DB user from config                
        $user = Config::read('db.user');
        // getting DB password from config                
        $password = Config::read('db.password');

        $this->dbh = new PDO($dsn, $user, $password);
    }

    public static function getInstance()
    {
        if (!isset(self::$instance))
        {
            $object = __CLASS__;
            self::$instance = new $object;
        }
        return self::$instance;
    }

    // others global functions
}

this class take parameters from a static class called "Config" where you can store your configuration:

<?php
class Config
{
    static $confArray;

    public static function read($name)
    {
        return self::$confArray[$name];
    }

    public static function write($name, $value)
    {
        self::$confArray[$name] = $value;
    }

}

// db
Config::write('db.host', '127.0.0.1');
Config::write('db.port', '5432');
Config::write('db.basename', 'mydb');
Config::write('db.user', 'myuser');
Config::write('db.password', 'mypassword');

in all your scripts / objects you just have to get the instance of Core and then query the DB

$sql = "select login, email from users where id = :id";

try {
    $core = Core::getInstance();
    $stmt = $core->dbh->prepare($sql);
    $stmt->bindParam(':id', $this->id, PDO::PARAM_INT);

    if ($stmt->execute()) {
        $o = $stmt->fetch(PDO::FETCH_OBJ);
        // blablabla....

If you need more information about singleton look at the PHP doc http://php.net/manual/en/language.oop5.patterns.php

Use PDO database class without creating new connection every time?

You should take the road shown in the mr.void's answer. In short:

  1. get rid of clsDatabase.
  2. Create an instance of PDO.
  3. pass it into clsDBLogin's property like it shown in mr.void's answer.
  4. Then use this pdo instance in the form of $this->db->prepare() and so on

So it should be like 

class clsDBLogin
{
    public function __construct($db)
    {
        $this->db = $db;
    }

    public function validateLogin($email)
    {  
        $email = trim($email);

        // Check user in db to start verification
        $query = 'SELECT * FROM users u, users_info ui 
                  WHERE u.users_id = ui.users_id AND u.email = ?';
        $stmt = $this->db->prepare($query);
        $stmt->execute([$email]);
        return $stmt->fetch();
    }
}

$dsn = 'mysql: host=localhost;dbname=test;charset=utf8';
$options = array(
        PDO::ATTR_PERSISTENT            => true,
        PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
);
// Create a new PDO instanace
$pdo = new PDO($dsn, $this->user, $this->pass, $options); 

$DBLogin = new clsDBLogin($pdo);
$user = $DBLogin->validateLogin($email);

How to use PDO connection in other classes?

  • your current class is rather useless. It would make sense to create a database wrapper if it adds some extra functionality to PDO. But given its current code, better to use vanilla PDO instead.
  • Either way, create a single $db instance from either vanilla PDO or your database class.
  • pass it as a constructor parameter into every class that needs a database connection

database.php:

<?php
$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new \PDO($dsn, $user, $pass, $opt);

user.php

<?php
class User {
    /* Properties */
    private $conn;

    /* Get database access */
    public function __construct(\PDO $pdo) {
        $this->conn = $pdo;
    }

    /* List all users */
    public function getUsers() {
        return $this->conn->query("SELECT username, usermail FROM user")->fetchAll();
    }
}

app.php

include 'database.php';
$user = new User($pdo);
$list = $user->getUsers();

foreach($list as $test) {
    echo $test["username"],"\n";
}

output:

username_foo
username_bar
username_baz

Check out my (The only proper) PDO tutorial for more PDO details.

PHP PDO injection inside a class

You should create a getter for the actual connection if you want to encapsulate the PDO connection in an extra class

<?php 
    class Database {
        private $dbh = null;

        public function __construct($user, $pass, $database) {
            $this->dbh = new PDO("mysql:host=localhost;dbname=".$database.";", $user, $pass);
        }

        public function getConnection() {
            return $this->dbh;
        }
    }

    class User {
        private $db = null;

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

        public function createUser($email, $username, $password) {
            $stmt = $this->db->getConnection()->prepare("INSERT INTO users (email,username,password) VALUES (?, ?, ? )");
            if ($stmt->execute(array($email, $username, $password))) {
                echo "Account successful created";
            }else {
                echo "Something was wrong during the registration process.";
            }
        }       
    }

}

$user = new User(new Database('root', 'root', 'testdb'));

From the comments:

It would be better for your case to just pass an instance of PDO towards your classes. This should work:

<?php
class user {
    public function __construct(\PDO $db) {
        $this->db = $db;
    }
}
<?php
    /** Init the one and only DB connection for this request **/
   $dbh = new PDO("mysql:host=localhost;dbname=".$database.";", $user, $pass);

   //... Do some stuff
   //....
   $user = new User($dbh);

How to use the PDO connection work in the class

First, Connect() is a member function, while Query($sql) is a static function. I would suggest to define both the same way, either as member or as static.

Next $conn needs to be accessible from both methods, so make it a class variable instead a local variable in Connect().

Here is a version without static functions/variables:

<?php
class DBConnection{

    private $conn;

    public function __construct(){
    }
    public function Connect(){
        try {
            $this->conn = new PDO('mysql:host=localhost;dbname=hqe', 'root', ''); 
            $this->conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $this->conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        } catch (Exception $e) {
            echo 'ERROR: ' . $e->getMessage();
        }
    }

    public function Query($sql){
        return $this->conn->prepare($sql);
    }
}

As you can see, the member variable is accessed using $this. Further you need an object of the class to call Query(). Something like:

$c = new DBConnection();
$c->Query("Select id, first_name, last_name from tbl_user");

Here is a version where all is defined as static:

<?php
class DBConnection{

    private static $conn;

    public function __construct(){
    }
    public static function Connect(){
        try {
            self::$conn = new PDO('mysql:host=localhost;dbname=hqe', 'root', ''); 
            self::$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            self::$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        } catch (Exception $e) {
            echo 'ERROR: ' . $e->getMessage();
        }
    }

    static public function Query($sql){
        return self::$conn->prepare($sql);
    }
}

As you can see, the static variable is accessed using self::.

PDO incorporation within class

Instead of creating the database connection inside the constructor, it's better to make it a dependency:

public function __construct(PDO $db, $id)
{
    $this->pdo = $db;
    $this->ID = $id;
}

To use it, you would do:

// $db = new PDO('mysql:host='.SERVER.';dbname='.DB.';charset=utf8',USER, PASSWORD);
$info = new info($db, '123');
echo $info->getName();

The chief advantage of this approach is that you no longer have any configuration stored inside your class. Additionally, you can pass anything that implements PDO as well, enabling unit testing with mocked databases.

PHP PDO class use in different class (doesn't work)

So I'm running this off of fetching all data as I dont know where you're getting $id from.

I generally break my code up into files and folders as I found this easier to work with and others to work on.

// database connection file (dbc.php)

class Database
{
    private $host = "127.0.0.1";
    private $db = "";
    private $user = "";
    private $password = "";
    public $conn;

    public function dbConnect()
    {
        $this->conn = null;
        try
        {
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db, $this->user, $this->password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch (PDOException $exception)
        {
            echo "Connection error: " . $exception->getMessage();
        }

        return $this->conn;

    }
}

I then make a common file, this is where you can store all of your frequently used functions or your static functions

// Common file (common.php)

require_once('dbc.php');

class DBCommon
{
    private $conn;

    public function __construct()
    {
        $database = new Database();
        $db = $database->dbConnect();
        $this->conn = $db;
    }

    public function run($sql)
    {
        $stmt = $this->conn->prepare($sql);
        return $stmt;
    }

}

So to explain this a little more, you will see a function of run() this is just to save the tedious $this->conn->prepare on every query.. Now you can run $this->run()

The next would be your class file.. this is where your logic goes:

// your class file... (class.btc.php)

require_once "common.php";

class BTC extends DBCommon

{
    public function getQuery()
    {
        $stmt = $this->run("SELECT * FROM `btc`");
        $stmt->execute();

        while ($row = $stmt->fetch(PDO::FETCH_OBJ))
        {
            $rows[] = $row;
        }

        return $rows;

    }

}

Self explanatory..

And then your calling method.. Lets say index.php

// File you're calling your class from (index.php)

    require_once "class.btc.php";

    $fetch = new BTC();

    $returnData = $fetch->getQuery();

    foreach ($returnData as $data)
    {
        echo
        "<p>$data->something</p>
        <p>$data->somethingElse</p>";
    }

It seems a little long winded I know, but the time you'll save overall will help!

PHP - How to access pdo object from other (multiple) classes

You dont want any of these to extend the database class because that will essentially make them all singletons of which you can only have one instance... you Want to make them USE the database class instead. So you would put you most abstract db methods on the Database and then methods that create queries for specific things would be on the User or what have you. This means your Database actually wraps PDO and is what all other classes work with for db operations. The Main or Base class may not even be needed unless you are trying to implement active record or something.

class Database {
   static protected $instance;

   /**
    * @var PDO
    */
   protected $connection;

   protected function __construct($dsn, $user, $pass, $attrs = array()) {
       // create pdo instance and assign to $this->pdo
   }

   public static function getInstance() {
       if(!self::$instance) {
           // get the arguments to the constructor from configuration somewhere
           self::$instance = new self($dsn, $user, $pass);
       }

       return self::$instance;
   }

   // proxy calls to non-existant methods on this class to PDO instance
   public function __call($method, $args) {
       $callable = array($this->pdo, $method);
       if(is_callable($callable)) {
           return call_user_func_array($callable, $args);
       }
   }
}

class Main {
   protected $db;

   public function __construct() {
      $this->db = Database::getInstance();
   }   
}

    class User extends Main{

       public function __construct() {
            parent::__construct();
       }

       public function findById($id) {
           $qry = $this->db->prepare('SELECT * FROM users WHERE userID = :userID');
           $qry->execute(array(':userID' => $id));
           $results = $qry->fetchAll(PDO::FETCH_ASSOC);

           return $results
       }
    }

Related Topics

Why Is Magento So Slow

PHP Create and Save a Txt File to Root Directory

Handle Fatal Errors in PHP Using Register_Shutdown_Function()

PHP Function for Get All Mondays Within Date Range

How to Check If the Request Is Made via Ajax in Codeigniter

How to Get First X Chars from a String, Without Cutting Off the Last Word

Encodeuri() in PHP

Getting a Checkbox Array Value from Post

Execute PHP Code in a String

Understanding Pdo MySQL Transactions

Developing a Tracking Pixel

Setup Http Expires Headers Using PHP and Apache

Png Transparency with PHP

Ajax Push System

Access Denied for User 'Root'@'Localhost' with PHPmyadmin

Extending PHP with C++

Is It Secure to Store a Password in a Session

What's the Difference Between $_Post, $_Get, and $_Request


Leave a reply



Submit