Simple MySQL PDO Wrapper

[Truffle]

I wrote this for a project I've been working on to help simplify database queries and keep the code clean. Also read that binding the parameters in your queries is great protection against injection hacks.

First, code examples

INSERT/UPDATE

PHP Code:

$db = new Database();
$data = array( "John", "Smith" );
$sql = "INSERT INTO names ( firstname, lastname) VALUES( ?, ? )";
$db->insert( $data, $sql ); 


Selecting a single row

PHP Code:

$data = array( 102 );
$sql = "SELECT * FROM names WHERE id = ? LIMIT 1";
$obj = $db->fetch_single_row( $data, $sql );
echo "{$obj->firstname} {$obj->lastname}"; 


Selecting Multiple Rows

PHP Code:

$data = array( 'M', 27 );
$sql = "SELECT * FROM names WHERE sex = ? AND age = ?";
$result = $db->fetch_multiple_rows( $data, $sql );

while( $row = $result->fetch() ) {
    echo "{$row->firstname} {$row->lastname}\n<br/>";
} 


And finally the class

PHP Code:

class Database {
    private $host;
    private $username;
    private $password;
    private $con;
        private $pdo;
    
    public function __construct( $database = "yourdb" )
    {
        $this->host = "localhost";
        $this->username = $_SERVER['DB_USER'];
        $this->password = $_SERVER['DB_PASSWORD'];
                $conStr = "host={$this->host};dbname={$database}";
                try {
                    $this->pdo = new PDO( "mysql:$conStr", $this->username, $this->password );
                    $this->pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
                }
                catch( PDOException $e ) {
                    echo "error ". $e->getMessage();
                }
    }
        
    public function fetch_single_row( $data, $sql )
    {
        if( $data !== null )
            $data = array_values( $data ); // Incase it's an associative array
        $sel = $this->pdo->prepare( $sql );
        $sel->execute( $data );
        $sel->setFetchMode( PDO::FETCH_OBJ );
        $obj = $sel->fetch();
        return $obj;
    }
    
    public function fetch_multiple_rows( $data, $sql )
    {
        if( $data !== null )
            $data = array_values( $data ); // Incase it's an associative array
        $sel = $this->pdo->prepare( $sql );
        $sel->execute( $data );
        $sel->setFetchMode( PDO::FETCH_OBJ );
        return $sel;
    }
    
    public function insert( $data, $sql )
    {
        if( $data !== null )
            $data = array_values( $data ); // Incase it's an associative array
        $ins = $this->pdo->prepare( $sql );
        $ins->execute( $data );
    }

    public function close()
    {
        $this->pdo = null;
    }
        
    public function __destruct()
    {
        $this->pdo = null;
    }
} 


[kbluhm]

In this example, since you are having to write out the entire INSERT query, it would make more send to turn rename the insert() method query():

PHP Code:

$data = array( "John", "Smith" );
$sql = "INSERT INTO names ( firstname, lastname) VALUES( ?, ? )";
$db->query( $data, $sql ); 


It would also seem more intuitive to have the query as the first parameter, and the array of values as an optional second parameter.

[Truffle]

Originally Posted by kbluhm

In this example, since you are having to write out the entire INSERT query, it would make more send to turn rename the insert() method query():

PHP Code:

$data = array( "John", "Smith" );
$sql = "INSERT INTO names ( firstname, lastname) VALUES( ?, ? )";
$db->query( $data, $sql ); 


It would also seem more intuitive to have the query as the first parameter, and the array of values as an optional second parameter.

Yeah you're right. I actually us $db->insert() when doing any query that doesn't require a return so it's more of a generic function than just insert

[Dormilich]

you can simplify your class by using Dependency Injection:

PHP Code:

class Database {

    private $pdo;
    
    public function __construct( PDO $database )
    {
        $this->pdo = $database;
    //  $this->pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    } 


though you would have to define the PDO object outside the Database class, it now works with every database supported by PDO.

another problem the class currently has (may have) is that it only supports string type data (the default data type for lazy bind)

the other thing is that try … catch inside the constructor doesn’t make sense. firstly you shouldn’t print from a constructor. secondly, if your connection fails, the object is still created and will throw further errors if you try one of the methods ($this->pdo is not an object). use try … catch in your script where you can (programmatically) fix the problem (even if that means to skip the whole database section).