PHP Data Objects

Connect to the database

Make a connection to the database using PDO.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'pdo';
$dsn        = "mysql:host=$host;dbname=$dbname";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}

SELECT a row using PDO

Make a simple SELECT statement.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'test';
$dsn        = "mysql:host={$host};dbname={$dbname}";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    $sqlStatement = 'SELECT * FROM users';
    $pdoStatement = $pdo->prepare($sqlStatement);
    $pdoStatement->execute();
    $users = $pdoStatement->fetchAll();
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}

Make a simple SELECT statement with value given.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'test';
$dsn        = "mysql:host={$host};dbname={$dbname}";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $email = '[email protected]';
    $pdo = new PDO($dsn, $username, $password, $options);
    $sqlStatement = 'SELECT * FROM users WHERE email = :email';
    $pdoStatement = $pdo->prepare($sqlStatement);
    $pdoStatement->bindValue(':email', $email);
    $pdoStatement->execute();
    $users = $pdoStatement->fetchAll();
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}

Another example make a simple SELECT statement to get only one row.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'test';
$dsn        = "mysql:host={$host};dbname={$dbname}";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $email = '[email protected]';
    $pdo = new PDO($dsn, $username, $password, $options);
    $sqlStatement = 'SELECT * FROM users';
    $pdoStatement = $pdo->prepare($sqlStatement);
    $pdoStatement->execute();
    $user = $pdoStatement->fetch();
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}

Another example make a simple SELECT statement with value given.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'test';
$dsn        = "mysql:host={$host};dbname={$dbname}";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $email = $_GET['search'] ?? null;
    $pdo = new PDO($dsn, $username, $password, $options);
    $sqlStatement = 'SELECT * FROM users WHERE email LIKE :email';
    $pdoStatement = $pdo->prepare($sqlStatement);
    $pdoStatement->bindValue(':email', "%{$email}%");
    $pdoStatement->execute();
    $users = $pdoStatement->fetchAll();
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}

INSERT new row using PDO

Make a simple INSERT statement.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'test';
$dsn        = "mysql:host={$host};dbname={$dbname}";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    $sqlStatement = 'INSERT INTO pengguna (field_a, field_b, field_c) VALUES (:field_a, :field_b, :field_c)';
    $pdoStatement = $pdo->prepare($sqlStatement);
    $pdoStatement->bindValue(':field_a', $_POST['field_a']);
    $pdoStatement->bindValue(':field_b', $_POST['field_b']);
    $pdoStatement->bindValue(':field_c', $_POST['field_c']);
    $pdoStatement->execute();
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}

Make a simple INSERT statement and get last insert ID.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'test';
$dsn        = "mysql:host={$host};dbname={$dbname}";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    $sqlStatement = 'INSERT INTO pengguna (field_a, field_b, field_c) VALUES (:field_a, :field_b, :field_c)';
    $pdoStatement = $pdo->prepare($sqlStatement);
    $pdoStatement->bindValue(':field_a', $_POST['field_a']);
    $pdoStatement->bindValue(':field_b', $_POST['field_b']);
    $pdoStatement->bindValue(':field_c', $_POST['field_c']);
    $pdoStatement->execute();
    $id = $pdo->lastInsertId();
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}

UPDATE row using PDO

Make a simple UPDATE statement.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'test';
$dsn        = "mysql:host={$host};dbname={$dbname}";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    $sqlStatement = 'UPDATE pengguna SET field_a = :field_a, field_b = :field_b, field_c = :field_c WHERE id = :id';
    $pdoStatement = $pdo->prepare($sqlStatement);
    $pdoStatement->bindValue(':field_a', $_POST['field_a']);
    $pdoStatement->bindValue(':field_b', $_POST['field_b']);
    $pdoStatement->bindValue(':field_c', $_POST['field_c']);
    $pdoStatement->bindValue(':id', $_GET['id']);
    $pdoStatement->execute();
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}

DELETE row using PDO

Make a simple DELETE statement.

<?php

$host       = 'localhost';
$username   = 'root';
$password   = 'root';
$dbname     = 'test';
$dsn        = "mysql:host={$host};dbname={$dbname}";
$options    = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    $sqlStatement = 'DELETE FROM pengguna WHERE id = :id';
    $pdoStatement = $pdo->prepare($sqlStatement);
    $pdoStatement->bindValue(':id', $_GET['id']);
    $pdoStatement->execute();
} catch (Throwable $throwable) {
    // Handling any exception
} finally {
    // Close PDO connection
    $pdo = null;
}