Abstracting PDO initialization from other code

Page 1 of 1

5 Replies - 282 Views - Last Post: 06 September 2012 - 07:41 AM Rate Topic: -----

#1 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Abstracting PDO initialization from other code

Posted 05 September 2012 - 10:02 PM

When I used mysql_* functions for database operations, I used to put the connecting to the database and initialization code in a separate file and and include it on other pages and it worked well.

I have learned PDO recently so I thought of trying it out with PDO. I have 4 PHP files.

config.php files stores all the database related information.

<?php

$host = "localhost";
$username = "root";
$password = "pass";
$dbname = "tbl";

?>


init.php includes and config.php file and initializes the database connection.

<?php

include_once 'config.php';

$db = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $username, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

?>


And there's a separate file called functions.php which has all the functions. It includes the init.php file.

<?php

include_once 'init.php';

function AddUser($_username, $_password)
{
    global $db;
    
    $db->beginTransaction();
        
    $param1 = $_username;
    $param2 = $_password;
    $query = $db->prepare("INSERT INTO users(username, password) VALUES(:username, :password)");
    $query->bindParam(':username', $param1, PDO::PARAM_STR);
    $query->bindParam(':password', $param2, PDO::PARAM_STR);
    $query->execute();
    
    $db->commit();
}

?>


And the index.php file which displays a small form.

<?php

include_once 'functions.php';

if(isset($_POST['save']))
{
    try
    {
        $user = $_POST['uname'];
        $pass = $_POST['pass'];
        AddUser($user, $pass);
        
    }
    catch (Exception $e)
    {
        echo $e->getMessage();
    }
}

?>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
    <body>
        <h1>Users</h1>
        <form action="" method="post">
            <label for="uname">Username</label>
            <input type="text" name="uname" />
            <br />
            <label for="pass">Password</label>
            <input type="password" name="pass" />
            <br />
            <input type="submit" name="save" value="Save" />
        </form>
    </body>
</html>


This works fine. But I'm just wondering of there's a way to go about this without declaring $db as global in every other function? (If I remove that line 'undefined variable' error comes up).

Or is there a better, more efficient way to accomplish this?

Is This A Good Question/Topic? 0
  • +

Replies To: Abstracting PDO initialization from other code

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 2887
  • View blog
  • Posts: 7,533
  • Joined: 08-June 10

Re: Abstracting PDO initialization from other code

Posted 05 September 2012 - 10:18 PM

two main approaches are:
- putting the PDO connection in a Registry or Singleton object (or even the $GLOBALS superglobal)
- passing the PDO connection as parameter to each function
Was This Post Helpful? 1
  • +
  • -

#3 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Abstracting PDO initialization from other code

Posted 06 September 2012 - 05:00 AM

Thanks Dormilich for the reply. My knowledge in design patterns is zero so I've been following this article as a guide. I saw that he's written functions for PDO methods like fetch and fetchAll within that same class. I don't wanna do that so I figured if I could extend the PDO class, it would solve that, right?

This is what I have right now.

The DatabaseHandler.php file which extends the PDO class and creates one instance.

<?php

class DatabaseHandler extends PDO
{
    protected $db_config = array(
        'host'      =>  'localhost',
        'username'  =>  'root',
        'password'  =>  'abc123',
        'dbname'    =>  'tbl'
    );
    public static $instance;
    private $dbh;
    
    public function __construct() 
    {
        try
        {
            $this->dbh = new PDO('mysql:host=' . $this->db_config['host'] . ';dbname=' . $this->db_config['dbname'], $this->db_config['username'], $this->db_config['password']);
            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch (Exception $e)
        {
            die($e->getMessage());
        }
    }
    
    public static function getInstance()
    {
        if(!isset(self::$instance))
        {
            self::$instance = new DatabaseHandler();
        }
        return self::$instance;
    }
}

?>


The functions.php file which has all the functions.

<?php

include_once 'DatabaseHandler.php';

function AddUser($_username, $_password)
{
    $db = DatabaseHandler::getInstance();
    
    $db->beginTransaction();
        
    $param1 = $_username;
    $param2 = $_password;
    $query = $db->prepare("INSERT INTO users(username, password) VALUES(:username, :password)");
    $query->bindParam(':username', $param1, PDO::PARAM_STR);
    $query->bindParam(':password', $param2, PDO::PARAM_STR);
    $query->execute();
    
    $db->commit();
}

function GetUsers()
{
    $db = DatabaseHandler::getInstance();
    
    $query = $db->prepare("SELECT * FROM users");
    $query->execute();
    
    while ($row = $query->fetch(PDO::FETCH_ASSOC))
    {
        echo $row['username'] . "<br/>";
    }
}

?>


And the index.php file which displays the form.

<?php

include_once 'functions.php';

if(isset($_POST['save']))
{
    try
    {
        $user = $_POST['uname'];
        $pass = $_POST['pass'];
        AddUser($user, $pass);
        
    }
    catch (Exception $e)
    {
        echo $e->getMessage();
    }
}


if (isset($_POST['get']))
{
    try 
    {
        GetUsers();
    }
    catch (Exception $e)
    {
        echo $e->getMessage();
    }
}

?>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
    <body>
        <h1>Users</h1>
        <form action="" method="post">
            <label for="uname">Username</label>
            <input type="text" name="uname" />
            <br />
            <label for="pass">Password</label>
            <input type="password" name="pass" />
            <br />
            <input type="submit" name="save" value="Save" />
            <input type="submit" name="get" value="Get Users" />
        </form>
    </body>
</html>


I'm trying to test the GetUsers functions in the functions.php file. But this error comes up when I run it.

Posted Image

What do I have to do to correct this?
Was This Post Helpful? 0
  • +
  • -

#4 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Abstracting PDO initialization from other code

Posted 06 September 2012 - 05:22 AM

I got it! Made some changes in the constructor method.

<?php

class DatabaseHandler extends PDO
{
    protected $db_config = array(
        'host'      =>  'localhost',
        'username'  =>  'root',
        'password'  =>  'abc123',
        'dbname'    =>  'tbl'
    );
    public static $instance;
    
    public function __construct() 
    {
        $dsn = 'mysql:dbname=' . $this->db_config['dbname'] . ';host' . $this->db_config['host'];
        $username = $this->db_config['username'];
        $password = $this->db_config['password'];
        
        try
        {
            parent::__construct($dsn, $username, $password);
            $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch (Exception $e)
        {
            die($e->getMessage());
        }
    }
    
    public static function getInstance()
    {
        if(!isset(self::$instance))
        {
            self::$instance = new DatabaseHandler();
        }
        return self::$instance;
    }
}

?>


Have I written the singleton pattern right? The class works but I want to make sure if I've done it right.

Thank you btw :)
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 2887
  • View blog
  • Posts: 7,533
  • Joined: 08-June 10

Re: Abstracting PDO initialization from other code

Posted 06 September 2012 - 06:22 AM

don’t die() on an exception! an exception should be caught where you can handle the problem and that’s usually not in the connection constructor.
Was This Post Helpful? 2
  • +
  • -

#6 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Abstracting PDO initialization from other code

Posted 06 September 2012 - 07:41 AM

Alright I removed the try catch block from the DatabaseHandler class. Thanks :)

This post has been edited by nK0de: 06 September 2012 - 07:42 AM

Was This Post Helpful? 0
  • +
  • -
Page 1 of 1