Simple PHP PDO MySQL Cheatsheet – CRUD

This is a very simple cheatsheet for PHP’s MySQL PDO driver. For a more exhaustive guide, check out this tutorial.

It includes MySQL connection and basic CRUD (Create, Read, Update, Delete) examples.

try {
    $mysql = new PDO('mysql:host=localhost;dbname=DATABASE_NAME;charset=utf8', 'USER_NAME', 'USER_PASSWORD');
} catch (PDOException $e) {
    // handle the error
    // $e->getMessage()
}

// Uncomment this line to show MySQL errors:
// $mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Include this file when you need to access the database:

require_once __DIR__ . '/mysql.php';

Select queries

$mysql_query = $mysql->prepare('SELECT * FROM users WHERE email=? LIMIT 1');

$mysql_query->execute(
    [
        'a@b.com',
    ]
);

$query_row = $mysql_query->fetch(PDO::FETCH_ASSOC);
if ($mysql_query->rowCount() > 0) {
    // $query_row['column'] ...
} else {
    // no results
}
$mysql_query = $mysql->prepare('SELECT * FROM users');
$mysql_query->execute();

while ($query_row = $mysql_query->fetch(PDO::FETCH_ASSOC)) {
    // $query_row['column'] ...
}

Insert

$mysql_query = $mysql->prepare('INSERT INTO users (user, email) VALUES (:username, :email))');

$mysql_query->execute(
    [
        'email' => 'a@b.com',
        'username' => 'john',
    ]
);

$id = $mysql->lastInsertId();

Update

$mysql_query = $mysql->prepare('UPDATE users SET email=:email WHERE user=:username LIMIT 1');

$mysql_query->execute(
    [
        'email' => 'a@b.com',
        'username' => 'john',
    ]
);

Delete

$mysql_query = $mysql->prepare('DELETE FROM users WHERE email=:email');

$mysql_query->execute(
    [
        'email' => 'a@b.com',
    ]
);