cp6/pdo_php_guide.md

## pdo_php_guide.md

      
    Raw
  

              pdo_php_guide.md
            
          
    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);