Basic CRUD operations with PDO

07 Jan 2017

CRUD = Create, Read, Update, Delete

Open a database connection

$host = '127.0.0.1';
$dbname = 'test';
$username = 'root';
$password = '';
$charset = 'utf8mb4';
$collate = 'utf8mb4_unicode_ci';
$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_PERSISTENT => false,
    PDO::ATTR_EMULATE_PREPARES => true,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES $charset COLLATE $collate"
];

$pdo = new PDO($dsn, $username, $password, $options);

Select a single row

$statement = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status=:status LIMIT 1");
$statement->execute(['email' => $email, 'status' => $status]);
$userRow = $statement->fetch();

Select multiple rows

Without parameters:

$userRows = $pdo->query('SELECT * FROM users')->fetchAll();

With parameters (prepared statements):

$statement = $pdo->prepare("SELECT * FROM employees WHERE name = :name");
$statement->execute(['name' => $name]);

foreach ($statement as $row) {
    // do something with $row
}

// or with the fech method
while ($row = $statement->fetch()) {
   // do something with $row
}

Insert a single row

$row = [
    'username' => 'bob',
    'email' => 'bob@example.com'
];
$sql = "INSERT INTO users SET username=:username, email=:email;";
$status = $pdo->prepare($sql)->execute($row);

if ($status) {
    $userId = (int)$pdo->lastInsertId();
}

Insert multiple rows

$rows = [];

$rows[] = [
    'username' => 'bob',
    'email' => 'bob@example.com'
];

$rows[] = [
    'username' => 'max',
    'email' => 'max@example.com'
];

$sql = "INSERT INTO users SET username=:username, email=:email;";
$statement = $pdo->prepare($sql);

foreach ($rows as $row) {
    $statement->execute($row);
}

Update a single row

$row = [
    'id' => 1,
    'username' => 'bob',
    'email' => 'bob2@example.com'
];

$sql = "UPDATE users SET username=:username, email=:email WHERE id=:id;";
$pdo->prepare($sql)->execute($row);

Update multiple rows

$row = [
    'updated_at' => '2017-01-01 00:00:00'
];

$sql = "UPDATE users SET updated_at=:updated_at";
$pdo->prepare($sql)->execute($row);

// optional
$affected = $pdo->rowCount();

Delete a single row

$where = ['id' => 1];
$pdo->prepare("DELETE FROM users WHERE id=:id")->execute($where);

Delete multiple rows

$pdo->prepare("DELETE FROM users")->execute();

PDO data types

You can set a explicit data type for the parameter using the PDO::PARAM_* constants.

// Usage
$email = (string)$_POST['email'];

$statement = $pdo->prepare('INSERT INTO users SET email=:email;');
$statement->bindValue(':email', $email, PDO::PARAM_STR);
$statement->execute();

Prepared statements using the IN clause

According to the PHP manual it’s not possible with PDO:

You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

This helper function converts all array values into a (safe) quoted string.

function quote_values(PDO $pdo, array $values) {
    array_walk($values, function (&$value) use ($pdo) {
        if($value === null) {
            $value = 'NULL';
            return;
        }
        $value = $pdo->quote(is_bool($value) ? (int)$value : (string)$value);
    });
    
    return implode(',', $values);
}

Usage

$ids = [
    1,
    2,
    3,
    "'", 
    null,
    'string',
    123.456,
    true,
    false,
];

$pdo = new PDO('sqlite::memory:');
$sql = sprintf("SELECT id FROM users WHERE id IN(%s)", quote_values($pdo, $ids));
echo $sql . "\n";

$statement = $pdo->prepare($sql);
$statement->execute();

Generated SQL:

SELECT id FROM users WHERE id IN('1','2','3','''',NULL,'string','123.456','1','0')

Other solutions to create IN clauses: