PDO Cheatsheet

Introduction

FreePBX has deprecated the use of PEAR and is transitioning to PDO. All new code should be written using PDO.

PDO Resources

The Object

You should no longer use

function foo($bar){
    global $db;
    ....

To use PDO outside a BMO class you can create the object as needed.

function foo($bar){
    $dbh = \FreePBX::Database();
    ....

Note you can use $db inside of the scope of a function. If you try to use is outside of a function it will collide with the global $db and break stuff in a nonsensical way.

Within a Class you can add the handle to your construct then reference it anywhere in your class as $this->Database;

public function __construct($freepbx = null) {
    if ($freepbx == null) {
        throw new Exception("Not given a FreePBX Object");
    }
    $this->FreePBX = $freepbx;
    $this->db = $freepbx->Database;
    ....

Examples

Fetch all items as an associated array or false

public function getArray(){
    $dbh = $this->Database;
    $sql = 'SELECT * FROM foo';
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

Fetch all items in an associated array by id or false

public function getArraybyID($id){
    $dbh = $this->Database;
    $sql = 'SELECT * FROM foo WHERE id = :id';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(':id' => $id));
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

Insert an Item and return id or false

public function putItem($item,$item2){
    $dbh = $this->Database;
    $sql = 'INSERT INTO foo (bar,baz) VALUES (:bar, :baz)';
    $stmt = $dbh->prepare($sql);
    $ret = $stmt->execute(array(':bar' => $item, ':baz' => $item2));
    if($ret){
        return $dbh->lastInsertId();
    }
    return $ret; //Will likely be false if you got here
}

Get one row as an associative array or false;

public function getItem($id){
    $dbh = $this->Database;
    $sql = 'SELECT * FROM foo WHERE id = :id LIMIT 1';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(':id' => $id));
    return $stmt->fetch(\PDO::FETCH_ASSOC);
}

Exceptions

You may wish to allow some exceptions and not have them bomb everything. You can catch exceptions in a try catch.

Exception code	Description

Exception code	Description
42S21	Column Exists
42S02	Table does not exist

/** In 13+ you should be doing this through doctrine **/
\outn(_("Adding Column bar"));
$sql = "ALTER TABLE foo ADD bar INT;";
$stmt = $dbh->prepare($sql);
try {
    $stmt->execute();
    \out(_("ok"));
} catch (\PDOException $e) {
    //We are ok with 42S21 because we are trying to add a column and it says that column is present.
    if($e->getCode() == '42S21'){
        \out(_("Column present"));
    }else{
        //All other exceptions are bad.
        \out($e->getMessage());
        throw $e;
    }
}

FreePBX Open Source