PHP PDO MySQL Cheat sheet guide

A guide on the basics for using PDO PHP for MySQL with pre-prepared statements.

Table of Contents

Creating connection

1a inline

1b function

1c class example
SELECT queries

2a Loop

2b One row

2c One column

2d Count

2e If exists
INSERT queries

3a insert

3b insert short form from array

3c on duplicate key update

3d bind types

3e get last inserted id
UPDATE queries

4a update column/s

4b get amount of rows updated
DELETE queries

Creating the database connection

Method 1: Inline

$db = new PDO('mysql:host=127.0.0.1;dbname=database;charset=utf8mb4', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Method 2: Function

function db_connect(): PDO
{
    $host = '127.0.0.1';
    $db_name = 'database';
    $db_user = 'username';
    $db_password = 'password';
    $db = "mysql:host=$host;dbname=$db_name;charset=utf8mb4";
    $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
    return new PDO($db, $db_user, $db_password, $options);
}

$db = db_connect();//Assign to variable before use

Options:

See here for options.

ATTR_ERRMODE throws exceptions on errors.

SELECT

loop

$select = $db->prepare("SELECT `column`, `column2` FROM `table`");
$select->execute();
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    $db_col = $row['column'];
    $db_col2 = $row['column2'];
    echo "$db_col $db_col2<br>";
}

loop with where

$status = 1;

$select = $db->prepare("SELECT `column`, `column2` FROM `table` WHERE `column3` = ?;");
$select->execute([$status]);
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    $db_col = $row['column'];
    $db_col2 = $row['column2'];
    echo "$db_col $db_col2<br>";
}

One row

Selecting one row

$user_id = 37841;

$select = $db->prepare("SELECT `name`, `email`, `age` FROM `users` WHERE `uid` = ? LIMIT 1;");
$select->execute([$user_id]);
$row = $select->fetch(PDO::FETCH_ASSOC);
$name = $row['name'];
$email = $row['email'];
$age = $row['age'];

Alternate placeholder binding method:

$user_id = 37841;
$status = 1;

$select = $db->prepare("SELECT `name`, `email`, `age` FROM `users` WHERE `uid` = :uid AND `status` = :status LIMIT 1;");
$select->execute(array(':uid' => $user_id, ':status' => $status));
$row = $select->fetch(PDO::FETCH_ASSOC);
$name = $row['name'];
$email = $row['email'];
$age = $row['age'];

One column

Selecting one column only

$user_id = 37841;

$select = $db->prepare("SELECT `name` FROM `users` WHERE `uid` = ? LIMIT 1;");
$select->execute([$user_id]);
$name = $select->fetchColumn();

count

Count the returned rows amount

$age = 50;

$select = $db->prepare("SELECT `name` FROM `users` WHERE `age` > ?;");
$select->execute([$age]);
$row_count = $select->rowCount();//Row count

if exists

Check if row found for the query

$user_id = 37841;

$select = $db->prepare("SELECT `name` FROM `users` WHERE `uid` = ? LIMIT 1;");
$select->execute([$user_id]);
$row = $select->fetch(PDO::FETCH_ASSOC);
if (!empty($row)) {//Row found
    echo $row['name'];
} else {//NO row found
    echo "DOES NOT EXIST";
}

INSERT

$insert = $db->prepare("INSERT INTO `table` (`col`, `col2`) VALUES (?, ?)");
$insert->execute([$value1, $value2]);

Or insert ignore

$insert = $db->prepare("INSERT IGNORE INTO `table` (`col`, `col2`) VALUES (?, ?)");
$insert->execute([$value1, $value2]);

Alternate value binding:

$insert = $db->prepare('INSERT INTO `table` (`col`, `col2`, `col3`) VALUES (:value, :value2, :value3)');
$insert->execute([
    'value' => 1,
    'value2' => $val2,
    'value3' => $val3,
]);

Insert short form from array

$users_array = array(
  ['uid' => 1, 'name' => 'Mike', 'age' => 42],
  ['uid' => 2, 'name' => 'John', 'age' => 36],
  ['uid' => 3, 'name' => 'Tony', 'age' => 51]
); 

$db->beginTransaction();
$insert = $db->prepare("INSERT INTO `users` (`uid`, `name`, `age`) VALUES (?, ?, ?)");
foreach ($users_array as $user) {
    $insert->execute(array(
        $user->uid,
        $user->name,
        $user->age,
    ));
}
$db->commit();

Insert on duplicate key update

$query = $db->prepare('INSERT INTO `table` (id, name, price, quantity) VALUES(:id, :name, :price, :quantity)
    ON DUPLICATE KEY UPDATE `quantity` = :quantity2, `price` = :price2');
$query->bindParam(':id', $id, PDO::PARAM_INT);
$query->bindParam(':name', $name, PDO::PARAM_STR);
$query->bindParam(':price', $price, PDO::PARAM_STR);
$query->bindParam(':quantity', $quantity, PDO::PARAM_INT);
$query->bindParam(':price2', $price, PDO::PARAM_STR);
$query->bindParam(':quantity2', $quantity, PDO::PARAM_STR);
$query->execute();

Common bindParam values: PARAM_BOOL, PARAM_NULL, PARAM_INT & PARAM_STR

Note there is NO float type.

without binding:

$query = $db->prepare('INSERT INTO `table` (id, name, price, quantity) VALUES(?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE `quantity` = ?, `price` = ?');
$query->execute([$id, $name, $price, $quantity, $price, $quantity]);

Getting last inserted id

$last_id = $db->lastInsertId();

UPDATE

Update column/s

$score = 453;
$user_id = 37841;

$update = $db->prepare("UPDATE `users` SET `score` = ? WHERE `uid` = ? LIMIT 1;");
$update->execute([$score, $user_id]);

Get amount of rows affected/updated:

$status = 1;

$update = $db->prepare("UPDATE `users` SET `status` = ? WHERE `score` > 75;");
$update->execute([$status]);
$updated_rows = $update->rowCount();//Returns rows amount that got updated

DELETE

Deleting a row

$user_id = 37841;

$delete = $db->prepare("DELETE FROM `users` WHERE `uid` = ? LIMIT 1;");
$delete->execute([$user_id]);

Class example

PDO connection class example where you won't need to keep setting and creating a connection

class test_class
{
    protected const HOSTNAME = '127.0.0.1';
    protected const DATABASE = 'database';
    protected const USERNAME = 'root';
    protected const PASSWORD = 'thepassword';
    protected PDO $db;

    public function __construct()
    {
        $db = "mysql:host=" . self::HOSTNAME . ";dbname=" . self::DATABASE . ";charset=utf8mb4";
        $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
        $this->db = new PDO($db, self::USERNAME, self::PASSWORD, $options);
    }

    public function nameForUID(int $id)
    {
        $select = $this->db->prepare("SELECT `name` FROM `users` WHERE `uid` = ? LIMIT 1;");
        $select->execute([$id]);
        $row = $select->fetch(PDO::FETCH_ASSOC);
        if (!empty($row)) {//Row found
            return $row['name'];
        } else {//NO row found
            return 'Error: No name found';
        }
    }

    //..........
}

usage:

$test_db = new test_class();

echo $test_db->nameForUID(1);

License

cp6/PHP-PDO-MySQL-cheat-sheet

ErrorLooks like something went wrong!

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Error
Looks like something went wrong!

Packages