PHP code example of decmuc / pdodb

1. Go to this page and download the library: Download decmuc/pdodb library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

decmuc / pdodb example snippets



use decMuc\PDOdb\PDOdb;


$db->get($_POST['table'])

$db->update($_POST['table'], $data)

$db->query("SELECT * FROM " . $_GET['table'])


// with manual installation
 or with composer autoload


$db = new PDOdb('localhost', 'root', 'secret', 'my_database');


// create a default instance
$db = new PDOdb([
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'secret',
    'db'       => 'my_database',
    'port'     => 3306,
    'charset'  => 'utf8mb4'
]);

// creates a named instance
$db2 = new PDOdb([
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'secret',
    'db'       => 'my_database',
    'port'     => 3306,
    'charset'  => 'utf8mb4',
    'instance' => 'customer'
]);

// Retrieve active or named instance:
$db    = PDOdb::getInstance();              // returns last active default
$db2   = PDOdb::getInstance('customer');        // explicitly retrieve 'customer'


$pdo = new PDO(
    'mysql:host=localhost;dbname=my_database;charset=utf8mb4',
    'root',
    'secret',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$db = new PDOdb($pdo);


function init() {
    // Create and register global instance
    $db = new \decMuc\PDOdb\PDOdb([
          'host'     => 'localhost',
          'username' => 'root',
          'password' => 'secret',
          'db'       => 'my_database',
          'port'     => 3306,
          'charset'  => 'utf8mb4'
      ]);
}

function myFunction() {
    // Retrieve globally shared instance
    $db = \decMuc\PDOdb\PDOdb::getInstance();
    ...
}


$db2 = new PDOdb([
     'host'     => 'localhost',
     'username' => 'root',
     'password' => 'secret',
     'db'       => 'my_database',
     'port'     => 3306,
     'charset'  => 'utf8mb4'
 ]);

// or
$db->addConnection('slave', [
    'host'     => 'localhost',
    'username' => 'root',
    'password' => 'secret',
    'db'       => 'my_database',
    'port'     => 3306,
    'charset'  => 'utf8mb4'
]);


$users = $db->connection('slave')->get('users');


namespace App\Data\Library;

use \decMuc\PDOdb\PDOdb;

class CustDB
{
    /**
     * Creates a new PDOdb instance with arbitrary credentials
     */
    public static function createInstance(
        string $host,
        string $username,
        string $password,
        string $dbName,
        int $port = 3306,
        string $charset = 'utf8mb4',
        string $instance = 'customer'
    ): PDOdb {
        return new PDOdb([
            'host'     => $host,
            'username' => $username,
            'password' => $password,
            'db'       => $dbName,
            'port'     => $port,
            'charset'  => $charset,
            'instance' => $instance // important!! without you got the last default
        ]);
    }
}


$data = [
    "login" => "admin",
    "firstName" => "John",
    "lastName" => 'Doe'
];
$id = $db->insert('users', $data);
if($id)
    echo 'user was created. Id=' . $id;


$data = [
    'login'      => 'admin',
    'active'     => true,
    'firstName'  => 'John',
    'lastName'   => 'Doe',
    'password'   => $db->func('SHA1(?)', ['secretpassword+salt']),
    'createdAt'  => $db->now(''),         // 👉 NOW()
    'updatedAt'  => $db->currentDate(1),  // 👉 1 = Y-m-d H:i:s (0 = Y-m-d, 2 = UNIX timestamp)
    'expires'    => $db->now('+1Y')       // 👉 NOW() + INTERVAL 1 YEAR
];
$id = $db->insert('users', $data);
if ($id) {
    echo 'User was created. ID = ' . $id;
} else {
    echo 'Insert failed: ' . $db->getLastError();
}


$data = [
    'login'     => 'admin',
    'firstName' => 'John',
    'lastName'  => 'Doe',
    'createdAt' => $db->now(''),       // NOW()
    'updatedAt' => $db->now('')        // NOW()
];

$updateColumns = ['updatedAt'];       // Only update this field on duplicate
$lastInsertId  = 'id';                // Returns the ID on insert, true/false on update

$db->onDuplicate($updateColumns, $lastInsertId);
$id = $db->insert('users', $data);

if ($id) {
    echo "Insert or update successful. ID: $id";
} else {
    echo "Insert failed: " . $db->getLastError();
}


$data = [
    [ 'login' => 'admin', 'firstName' => 'John', 'lastName' => 'Doe' ],
    [ 'login' => 'other', 'firstName' => 'Another', 'lastName' => 'User', 'password' => 'very_cool_hash' ]
];

$ids = $db->insertMulti('users', $data);
if (!$ids) {
    echo 'Insert failed: ' . $db->getLastError();
} else {
    echo 'New users inserted with following IDs: ' . implode(', ', $ids);
}


$data = [
    ['admin', 'John', 'Doe'],
    ['other', 'Another', 'User']
];
$keys = ['login', 'firstName', 'lastName'];

$ids = $db->insertMulti('users', $data, $keys);


$rows = [];
for ($i = 1; $i <= 1000; $i++) {
    $rows[] = [
        'name'        => 'Product ' . $i,
        'category_id' => rand(1, 5),
        'supplier_id' => rand(1, 5),
        'price'       => rand(10, 999) + 0.99
    ];
}

$count = $db->insertBulk('test_products_bulk', $rows);

if ($count) {
    echo "Inserted $count records successfully.";
} else {
    echo "Insert failed: " . $db->getLastError();
}


$data = [
    'id'        => 1,                  // Must match an existing unique key
    'login'     => 'admin',
    'firstName' => 'John',
    'lastName'  => 'Doe',
    'updatedAt' => $db->currentDate(1)
];

$id = $db->replace('users', $data);

if ($id) {
    echo "User was replaced. ID: " . $id;
} else {
    echo "Replace failed: " . $db->getLastError();
}


$data = [
    'firstName'  => 'Bobby',
    'lastName'   => 'Tables',
    'editCount'  => $db->inc(2),     // editCount = editCount + 2
    'active'     => $db->not()       // active = !active
];

$db->where('id', 1);
if ($db->update('users', $data)) {
    echo $db->count . ' records were updated';
} else {
    echo 'update failed: ' . $db->getLastError();
}


$db->update('users', $data, 10);
// Generates: UPDATE users SET ... LIMIT 10


$users = $db->get('users');
// returns all users

$users = $db->get('users', 10);
// returns the first 10 users


$columns = ['id', 'login', 'email'];
$users = $db->get('users', null, $columns);

if ($db->count > 0) {
    foreach ($users as $user) {
        echo "{$user['id']}: {$user['login']} ({$user['email']})\n";
    }
}


$db->where('id', 1);
$user = $db->getOne('users');

if ($user) {
    echo "Welcome back, {$user['login']}";
}


$stats = $db->getOne('users', 'SUM(id) AS total, COUNT(*) AS cnt');

echo "Total ID sum: {$stats['total']}, total users: {$stats['cnt']}";


$total = $db->getValue('users', 'COUNT(*)');
echo "{$total} users found";


$logins = $db->getValue('users', 'login', null);
// SELECT login FROM users

$logins = $db->getValue('users', 'login', 5);
// SELECT login FROM users LIMIT 5

foreach ($logins as $login) {
    echo "Login: $login\n";
}


$db->loadData("users", "/path/to/file.csv");
$db->loadXml("users", "/path/to/file.xml");


if (($fp = fopen('users.csv', 'r')) !== false) {
    while (($row = fgetcsv($fp, 0, ';')) !== false) {
        $db->insert('users', [
            'name' => $row[0],
            'email' => $row[1],
            // ...
        ]);
    }
    fclose($fp);
}


$page = 1;

// Set number of results per page (default is 20)
$db->pageLimit = 2;

// Fetch paginated results using arrayBuilder()
$products = $db->arrayBuilder()->paginate("products", $page);

echo "Showing page $page out of " . $db->totalPages;


$users = $db->rawQuery('SELECT * FROM users WHERE id >= ?', [10]);
foreach ($users as $user) {
    print_r($user);
}


$user = $db->rawQueryOne('SELECT * FROM users WHERE id = ?', [10]);
echo $user['login'];


$password = $db->rawQueryValue('SELECT password FROM users WHERE id = ? LIMIT 1', [10]);
echo "Password is {$password}";


$logins = $db->rawQueryValue('SELECT login FROM users LIMIT 10');
foreach ($logins as $login) {
    echo $login;
}


$id = $_GET['id'];
// ❌ Dangerous: exposes your database to SQL injection!
$users = $db->rawQuery("SELECT * FROM users WHERE id = $id");

// ✅ Instead, use:
$users = $db->rawQuery("SELECT * FROM users WHERE id = ?", [$id]);


$params = [10, 1, 10, 11, 2, 10];
$q = "(
    SELECT a FROM t1 WHERE a = ? AND B = ? ORDER BY a LIMIT ?
) UNION (
    SELECT a FROM t2 WHERE a = ? AND B = ? ORDER BY a LIMIT ?
)";
$results = $db->rawQuery($q, $params);
print_r($results);


$db->setQueryOption('LOW_PRIORITY')->insert($table, $param);
// Produces: INSERT LOW_PRIORITY INTO table ...


$db->setQueryOption('FOR UPDATE')->get('users');
// Produces: SELECT * FROM users FOR UPDATE;


$db->setQueryOption(['LOW_PRIORITY', 'IGNORE'])->insert($table, $param);
// Produces: INSERT LOW_PRIORITY IGNORE INTO table ...


$db->setQueryOption('SQL_NO_CACHE');
$users = $db->get("users");
// Produces: SELECT SQL_NO_CACHE * FROM users;


$results = $db
    ->where('id', 1)
    ->where('login', 'admin')
    ->get('users');
// Produces: SELECT * FROM users WHERE id = 1 AND login = 'admin'


id = 1); DROP TABLE users --


$db->where('id', 123);
$user = $db->get('users');
// SELECT * FROM users WHERE id = 123


$db->where('age', 18, '>=');
$db->where('status', ['active', 'pending'], 'IN');


$db->where('country', 'DE');
$db->where('emailVerified', true);


$db->where('type', 'admin')
   ->orWhere('type', 'manager');
// WHERE type = 'admin' OR type = 'manager'


$name = 'John';
$db->where('name', '%'.$name.'%', 'LIKE');
// WHERE name LIKE '%John%'


$db->where('createdAt', ['2024-01-01', '2024-12-31'], 'BETWEEN');
// WHERE createdAt BETWEEN '2024-01-01' AND '2024-12-31'


$db->where(function($db) {
    $db->where('type', 'admin');
    $db->orWhere('type', 'editor');
});
$db->where('active', true);


// Find user with id = 42
$db->whereInt('id', 42);
$user = $db->getOne('users');

// Find users with id = 42 or id = 99
$db->whereInt('id', 42)
   ->orWhereInt('id', 99);
$users = $db->get('users');


// Valid usage — allowed
$db->whereInt('id', 123);

// Invalid usage — throws exception or blocks
$db->whereInt('id', '123abc');    // Non-pure integer string
$db->whereInt('id', '1 OR 1=1');  // Injection attempt
$db->whereInt('id', 'DROP TABLE'); // Injection attempt


// Find user with username 'john_doe'
$db->whereString('username', 'john_doe');
$user = $db->getOne('users');

// Find users with username 'john_doe' or 'jane_smith'
$db->whereString('username', 'john_doe')
->orWhereString('username', 'jane_smith');
$users = $db->get('users');


// Valid usage — allowed
$db->whereString('username', 'valid_user');

// Invalid usage — throws exception or blocks
$db->whereString('username', "john'; DROP TABLE users; --");  // Injection attempt
$db->whereString('username', "admin OR 1=1");                 // Injection attempt


// Find users created on 2025-07-01
$db->whereDate('created_at', '2025-07-01');
$user = $db->getOne('users');

// Find users created on 2025-07-01 or 2025-07-02
$db->whereDate('created_at', '2025-07-01')
->orWhereDate('created_at', '2025-07-02');
$users = $db->get('users');


// Valid usage — allowed
$db->whereDate('created_at', '2025-07-01');

// Invalid usage — throws exception or blocks
$db->whereDate('created_at', '2025-07-01; DROP TABLE users'); // Injection attempt
$db->whereDate('created_at', 'invalid-date');                 // Invalid format


// Find active users
$db->whereBool('is_active', true);
$users = $db->get('users');

// Find inactive or banned users
$db->whereBool('is_active', false)
->orWhereBool('is_banned', true);
$users = $db->get('users');


// Valid usage — allowed
$db->whereBool('is_active', true);
$db->whereBool('is_active', 0);

// Invalid usage — throws exception or blocks
$db->whereBool('is_active', 'yes');   // Invalid boolean
$db->whereBool('is_active', '1 OR 1=1'); // Injection attempt


// Find users with ids in 1, 2, 3
$db->whereIn('id', [1, 2, 3]);
$users = $db->get('users');

// Find users with status in 'active' or 'pending'
$db->whereIn('status', ['active', 'pending'])
->orWhereIn('status', ['banned', 'suspended']);
$users = $db->get('users');


// Valid usage — allowed
$db->whereIn('id', [1, 2, 3]);

// Invalid usage — throws exception or blocks
$db->whereIn('id', []);                // Empty array
$db->whereIn('id', ['1; DROP TABLE']); // Injection attempt if unescaped (blocked here)


// Exclude users with ids 10, 20, 30
$db->whereNotIn('id', [10, 20, 30]);
$users = $db->get('users');

// Exclude users with status 'inactive' or 'banned'
$db->whereNotIn('status', ['inactive', 'banned'])
->orWhereNotIn('status', ['pending']);
$users = $db->get('users');


// Valid usage — allowed
$db->whereNotIn('id', [10, 20, 30]);

// Invalid usage — throws exception or blocks
$db->whereNotIn('id', []);             // Empty array
$db->whereNotIn('id', ['DROP TABLE']); // Injection attempt if unescaped (blocked here)


// Users without a phone number
$db->whereIsNull('phone');
$users = $db->get('users');

// OR-condition with NULL check
$db->where('email', '[email protected]')
->orWhereIsNull('email');
$users = $db->get('users');


// Users with a password set
$db->whereIsNotNull('password');
$users = $db->get('users');

// OR-condition combined
$db->where('active', 1)
->orWhereIsNotNull('last_login');
$users = $db->get('users');


$unsafe = "'; DROP TABLE users; --";
$safe = $db->escape($unsafe);
// -> returns: \'; DROP TABLE users; --


$db->where('name', $name); // safely bound and escaped internally


$name = $db->escape($_GET['name']);
$sql = "SELECT * FROM users WHERE name = '$name'";
$db->rawQuery($sql); // 🚨 vulnerable to mistakes


$db->orderBy("id", "asc");
$db->orderBy("login", "desc");
$db->orderBy("RAND()");
$results = $db->get('users');
// SELECT * FROM users ORDER BY id ASC, login DESC, RAND()


$db->orderBy('userGroup', 'ASC', ['superuser', 'admin', 'users']);
$db->get('users');
// SELECT * FROM users ORDER BY FIELD(userGroup, 'superuser', 'admin', 'users') ASC


$db->setPrefix("t_");

$db->orderBy("users.id", "asc");
$results = $db->get('users');
// WRONG: SELECT * FROM t_users ORDER BY users.id ASC

$db->orderBy("`users`.id", "asc");
$results = $db->get('users');
// CORRECT: SELECT * FROM t_users ORDER BY t_users.id ASC


$db->groupBy ("name");
$results = $db->get ('users');
// Gives: SELECT * FROM users GROUP BY name;


$db->where("agentId", 10);
$db->where("active", true);

$customers = $db->copy();
$res = $customers->get("customers", [10, 10]);
// SELECT * FROM customers WHERE agentId = 10 AND active = 1 LIMIT 10, 10

$cnt = $db->getValue("customers", "count(id)");
echo "total records found: " . $cnt;
// SELECT count(id) FROM customers WHERE agentId = 10 AND active = 1


$sq = $db->subQuery();
$sq->get("users");
// (SELECT * FROM users)


$sq = $db->subQuery("sq");
$sq->get("users");
// (SELECT * FROM users) sq


$ids = $db->subQuery();
$ids->where("qty", 2, ">");
$ids->get("products", null, "userId");

$db->where("id", $ids, "IN");
$res = $db->get("users");
// SELECT * FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2)


$userIdQ = $db->subQuery();
$userIdQ->where("id", 6);
$userIdQ->getOne("users", "name");

$data = [
    "productName" => "test product",
    "userId"      => $userIdQ,
    "lastUpdated" => $db->now()
];

$id = $db->insert("products", $data);
// INSERT INTO products (productName, userId, lastUpdated)
// VALUES ("test product", (SELECT name FROM users WHERE id = 6), NOW());


$ids = $db->subQuery ();
$ids->where ("qty", 2, ">");
$ids->get ("products", null, "userId");

$db->where ("id", $ids, 'in');
$res = $db->get ("users");
// Gives SELECT * FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2)


$usersQ = $db->subQuery("u");
$usersQ->where("active", 1);
$usersQ->get("users");

$db->join($usersQ, "p.userId=u.id", "LEFT");
$products = $db->get("products p", null, "u.login, p.productName");

print_r($products);
// SELECT u.login, p.productName FROM products p
// LEFT JOIN (SELECT * FROM t_users WHERE active = 1) u ON p.userId = u.id;


$db->join("users u", "p.tenantID = u.tenantID", "LEFT");
$db->where("u.id", 6);
$products = $db->get("products p", null, "u.name, p.productName");

print_r($products);
// SELECT u.name, p.productName FROM products p
// LEFT JOIN users u ON p.tenantID = u.tenantID
// WHERE u.id = 6


$db->join("users u", "p.tenantID = u.tenantID", "LEFT");
$db->joinWhere("users u", "u.tenantID", 5);
$products = $db->get("products p", null, "u.name, p.productName");

print_r($products);
// SELECT u.name, p.productName FROM products p
// LEFT JOIN users u ON (p.tenantID = u.tenantID AND u.tenantID = 5)


$db->join("users u", "p.tenantID = u.tenantID", "LEFT");
$db->joinOrWhere("users u", "u.tenantID", 5);
$products = $db->get("products p", null, "u.name, p.productName");

print_r($products);
// SELECT u.name, p.productName FROM products p
// LEFT JOIN users u ON (p.tenantID = u.tenantID OR u.tenantID = 5)


$db->join("users u", "p.tenantID = u.tenantID", "LEFT");
$db->where("u.tenantID", 5); // ⛔ NOT in JOIN ON, but in WHERE clause


$sub = $db->subQuery();
$sub->where("status", "active");
$sub->get("sessions s", null, "s.userId");

$db->where(null, $sub, 'EXISTS');
$db->get("users");
// SELECT * FROM users WHERE EXISTS (SELECT s.userId FROM sessions s WHERE status = 'active')


$sub = $db->subQuery();
$sub->where("status", "active");
$sub->get("sessions s", null, "s.userId");

$db->where(null, $sub, 'EXISTS');
$db->get("users");
// SELECT * FROM users WHERE EXISTS (SELECT s.userId FROM sessions s WHERE status = 'active')


$sub = $db->subQuery();
$sub->get("orders", null, "customerId");

$db->where("id", $sub, "IN");
$customers = $db->get("customers");
// SELECT * FROM customers WHERE id IN (SELECT customerId FROM orders)


$sub = $db->subQuery();
$sub->get("orders", null, "customerId");

$db->where("id", $sub, "NOT IN");
$customers = $db->get("customers");
// SELECT * FROM customers WHERE id NOT IN (SELECT customerId FROM orders)


$db->setLockMethod('WRITE'); // or 'READ'


$db->lock('users');
// LOCK TABLES users WRITE;


$db->lock(['users', 'logs']);
// LOCK TABLES users WRITE, logs WRITE;


$db->unlock();
// UNLOCK TABLES;


$db->lock('users WRITE');
$db->unlockOnFailure(); // ← not implemented yet


$db->startTransaction();

if (!$db->insert('myTable', $insertData)) {
    // Insert failed — rollback changes
    $db->rollback();
} else {
    // Everything ok — commit transaction
    $db->commit();
}


$db->startTransaction();

try {
    $db->insert('table1', $data1);
    $db->insert('table2', $data2);
    $db->update('table3', $data3);

    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
    echo "Transaction failed: " . $e->getMessage();
}


$db->debug(1);


$db->debug(2);


echo $db->getLastError();   // Last error message
echo $db->getLastErrno();   // Last error code

$db->where('login', 'admin')->update('users', ['firstName' => 'Jack']);

if ($db->getLastErrno() === 0){
    echo 'Update succesfull';
} else {
    echo 'Update failed. Error: '. $db->getLastError();
}


$db->setTrace(true);


$db->setTrace(true, $_SERVER['DOCUMENT_ROOT']);


$db->get("users");
$db->get("test");
print_r($db->trace);


    [0] => Array
        (
            [0] => SELECT * FROM t_products WHERE active = 1
            [1] => 0.0021378993988037         // execution time in seconds
            [2] => PDOdb->get() >> file "/var/www/api/ProductController.php" line #78
        )

    [1] => Array
        (
            [0] => SELECT name, email FROM t_users LIMIT 5
            [1] => 0.00084209442138672
            [2] => PDOdb->get() >> file "/var/www/api/UserService.php" line #142
        )

    [2] => Array
        (
            [0] => INSERT INTO t_logs (event, createdAt) VALUES (?, NOW())
            [1] => 0.0013091564178467
            [2] => PDOdb->insert() >> file "/var/www/api/Logger.php" line #55
        )