Quick start guide to PHP PDO

In this quick start guide I'm going to be real brief, it's more of a "examples of PDO code". I will base my queries on MySQL commands since that is most popular, but with the correct drivers installed PDO can connect to most anything.

Main Connection for MySQL

$db = new PDO('mysql:host=127.0.0.1;dbname=MyDatabaseName', 'username', 'password');

Main Connection for Microsoft SQL Server

$db = new PDO('sqlserv:server=127.0.0.1;Database=MyDatabaseName', 'username', 'password');

Let's set a couple attributes, mainly when the data is returned I want the information reachable by name rather than index numbers. Such as accessing data via $row['column name']; versus $row[0] where 0 is the column index number.

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, False);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Selecting Data

$company_name = 'Joes Diner';
$results = $db->prepare('SELECT id, name, contact FROM contacts WHERE company = ?');
$results->execute(array($company_name));

while ($row = $results->fetch()) {
    print_r($row);
}

Selecting data without parameters

$results = $db->query('SELECT id, name, contact FROM contacts');

Inserting Data

$salutation = 'Mr.';
$company = 'Johns Diner';
$contact = 'John Doe';
$ins = $db->prepare('INSERT INTO CONTACTS (salutation, company, contact) VALUES (?,?,?)');
$ins->execute(array($salutation, $company, $contact));

Updating Data

$contact_id = 4;
$salutation = 'Mr.';
$company = 'Johns Diner';
$contact = 'John Doe';
$upd = $db->prepare('UPDATE CONTACTS SET salutation= ?, company = ?, contact = ? WHERE id = ?');
$upd->execute(array($salutation, $company, $contact, $contact_id));

Deleting Data

$contact_id = 4;
$del = $db->prepare('DELETE FROM CONTACTS WHERE id = ?');
$del->execute(array($contact_id));

Print