Simple yet secure PHP PDO database wrapper with CRUD methods...

pdo-wrapper.php

<?php
# PDO Wrapper, supporting MySQL and Sqlite
# Usage:
#   $db = new db();
#
#   // table, data
#   $db->create('users', array(
#     'fname' => 'john',
#     'lname' => 'doe'
#   ));
#   
#   // table, where, where-bind
#   $db->read('users', "fname LIKE :search", array(
#     ':search' => 'j%'
#   ));
#
#   // table, data, where, where-bind
#   $db->update('users', array(
#     'fname' => 'jame'
#   ), 'gender = :gender', array(
#     ':gender' => 'female'
#   ));
#   
#   // table, where, where-bind
#   $db->delete('users', 'lname = :lname', array(
#     ':lname' => 'doe'
#   ));

class db
{
    private $config = array(
        # "dbdriver" => "sqlite",
        # "sqlitedb" => "path/to/db.sqlite"
        
        "dbdriver" => "mysql",
        "dbuser" => "root",
        "dbpass" => "",
        "dbname" => "test"
    );

    function db() {
        $dbhost = $this->config['dbhost'];
        $dbuser = $this->config['dbuser'];
        $dbpass = $this->config['dbpass'];
        $dbname = $this->config['dbname'];

        # $sqlitedb = $this->config['sqlitedb'];

        $options = array(
            PDO::ATTR_PERSISTENT => true, 
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        );

        try {
            switch($this->config["dbdriver"]) {
                case "sqlite":
                    $conn = "sqlite:{$sqlitedb}";
                    break;
                case "mysql":
                    $conn = "mysql:host={$dbhost};dbname={$dbname}";
                    break;
                default:
                    echo "Unsuportted DB Driver! Check the configuration.";
                    exit(1);
            }

            $this->db = new PDO($conn, $dbuser, $dbpass, $options);
            
        } catch(PDOException $e) {
            echo $e->getMessage(); exit(1);
        }
    }

    function run($sql, $bind=array()) {
        $sql = trim($sql);
        
        try {

            $result = $this->db->prepare($sql);
            $result->execute($bind);
            return $result;

        } catch (PDOException $e) {
            echo $e->getMessage(); exit(1);
        }
    }

    function create($table, $data) {
        $fields = $this->filter($table, $data);

        $sql = "INSERT INTO " . $table . " (" . implode($fields, ", ") . ") VALUES (:" . implode($fields, ", :") . ");";

        $bind = array();
        foreach($fields as $field)
            $bind[":$field"] = $data[$field];

        $result = $this->run($sql, $bind);
        return $this->db->lastInsertId();
    }

    function read($table, $where="", $bind=array(), $fields="*") {
        $sql = "SELECT " . $fields . " FROM " . $table;
        if(!empty($where))
            $sql .= " WHERE " . $where;
        $sql .= ";";

        $result = $this->run($sql, $bind);
        $result->setFetchMode(PDO::FETCH_ASSOC);

        $rows = array();
        while($row = $result->fetch()) {
            $rows[] = $row;
        }

        return $rows;
    }

    function update($table, $data, $where, $bind=array()) {
        $fields = $this->filter($table, $data);
        $fieldSize = sizeof($fields);

        $sql = "UPDATE " . $table . " SET ";
        for($f = 0; $f < $fieldSize; ++$f) {
            if($f > 0)
                $sql .= ", ";
            $sql .= $fields[$f] . " = :update_" . $fields[$f]; 
        }
        $sql .= " WHERE " . $where . ";";

        foreach($fields as $field)
            $bind[":update_$field"] = $data[$field];
        
        $result = $this->run($sql, $bind);
        return $result->rowCount();
    }

    function delete($table, $where, $bind="") {
        $sql = "DELETE FROM " . $table . " WHERE " . $where . ";";
        $result = $this->run($sql, $bind);
        return $result->rowCount();
    }

    private function filter($table, $data) {
        $driver = $this->config['dbdriver'];

        if($driver == 'sqlite') {
            $sql = "PRAGMA table_info('" . $table . "');";
            $key = "name";
        } elseif($driver == 'mysql') {
            $sql = "DESCRIBE " . $table . ";";
            $key = "Field";
        } else {    
            $sql = "SELECT column_name FROM information_schema.columns WHERE table_name = '" . $table . "';";
            $key = "column_name";
        }   

        if(false !== ($list = $this->run($sql))) {
            $fields = array();
            foreach($list as $record)
                $fields[] = $record[$key];
            return array_values(array_intersect($fields, array_keys($data)));
        }

        return array();
    }
}

Thanks it´s very useful.

Clean code =)

Hi,

Let me first say that you did an excellent job, clean code and very powerfull !

I think this is the most stupid quest you have ever heard but, I pars an query to the read function

class ClassRead extends db {
	
	function ShowTeam(){
		$q = new db();
		$q -> read("Team", 
			   "Name=:search", 
		           array(':search' => 'Team 1')
		       );
	}
}

after that I want to use this function ClassRead::ShowTeam();. If I do this I got nothing.
If I put print_r($rows); in the read function, above the return $rows;, i see the result of my query in array format. That is also my problem. I don't want the array format, just a clean string of my result. And im not skilled enough, i guess, to manage this.

If i use a foreach loop i get the result i want (putting it in the read function). But i want the function read so dynamic possible so that i can pars almost every query in to it.

Hope you can help me

When I run the following code:
$db->update('dispatch', array( 'name' => $name, 'phone' => $phone), 'id = :id', array( ':id' => $id));
Nothing updates, if I refresh the page nothing is affected. Is there something I am missing?

private $config = array(
        # "dbdriver" => "sqlite",
        # "sqlitedb" => "path/to/db.sqlite"
        
        "dbdriver" => "mysql",
        "dbuser" => "root",
        "dbpass" => "",
        "dbname" => "test"
    );

Missed setting dbhost variable in config array...
Add that variable and check. Hope this help.