PDO in PHP: drivers, connections, queries, and inserts

PDO (PHP Data Objects) is a database access layer that provides a uniform API across different drivers (MySQL, SQLite, and others).

Available drivers

List installed PDO drivers:

print_r(PDO::getAvailableDrivers());

Connecting

try {
    // MySQL via PDO_MYSQL
    $dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

    // SQLite
    $dbh = new PDO("sqlite:my/database/path/database.db");
} catch (PDOException $e) {
    echo $e->getMessage();
}

Connection attributes

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

Closing a connection

Set the connection variable to null:

$dbh = null;

Creating a table

$dbh->exec("CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY,
    title TEXT,
    message TEXT,
    time INTEGER
)");

Another example:

$dbh->exec("CREATE TABLE IF NOT EXISTS followers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    facebook_id TEXT
)");

SELECT

$result = $dbh->query("SELECT id FROM spreadsheet WHERE status = 1 AND thread IS NULL");

Inserting a row

$sth = $this->dbh->prepare("INSERT INTO links (link, processed) VALUES (:link, 0)");
$sth->execute([':link' => $url]);