PHP code example of stule-ru / modernpdo

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

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

    

stule-ru / modernpdo example snippets

use ModernPDO\ModernPDO;
use ModernPDO\Drivers\MySQLDriver;
use ModernPDO\Drivers\MariaDBDriver;
use ModernPDO\Drivers\PostgreSQLDriver;
use ModernPDO\Drivers\SQLite3Driver;

// Initiolize by PDO
$mpdo = new ModernPDO(
    pdo: $pdo,
);

// Initiolize MySQL
$mpdo = new MySQLDriver(
    host: $host,
    database: $database,
    username: $username,
    password: $password,
    charset: $charset,
    //port: $port,
);

// Initiolize MariaDB
$mpdo = new MariaDBDriver(
    host: $host,
    database: $database,
    username: $username,
    password: $password,
    charset: $charset,
    //port: $port,
);

// Initiolize PostgreSQL
$mpdo = new PostgreSQLDriver(
    host: $host,
    database: $database,
    username: $username,
    password: $password,
    //port: $port,
);

// Initiolize SQLite3
$mpdo = new SQLite3Driver(
    mode: $mode,
);

// Row query

$mpdo->exec('CREATE TABLE table_name (id int, name varchar(32));');

// Prepared queries

$stmt = $mpdo->query("SELECT * FROM table_name", []);

// Check query status
if ($stmt->status()) {

    // Get counts
    $stmt->rowCount();
    $stmt->columnCount();

    $stmt->fetchColumn($column); // Fetch cell
    $stmt->fetchObject(); // Fetch row as object
    $stmt->fetch(); // Fetch row as array
    $stmt->fetchAll(); // Fetch all rows as array
}

//
// Insert example
//

// INSERT INTO table (id, name) VALUES (10, 'test'), (11, 'test')
$mpdo->insert('table')->columns([
    'id', 'name',
])->values([
    [10, 'test'],
    [11, 'test'],
])->execute();

// INSERT INTO table VALUES (12, 'test')
$mpdo->insert('table')->values([
    [12, 'test'],
])->execute();

//
// Select examples
//

// SELECT * FROM table
$mpdo->select('table')->rows();

// SELECT * FROM table WHERE id=10 LIMIT 1
$mpdo->select('table')->where('id', 10)->row();
// SELECT * FROM table WHERE id=10 AND name='test' LIMIT 1
$mpdo->select('table')->where('id', 10)->and('name', 'test')->row();

// SELECT id, name FROM table
$mpdo->select('table')->columns(['id', 'name'])->rows();

// SELECT COUNT(*) FROM table
$mpdo->select('table')->columns([new Count()])->cell();

// SELECT SUM(amount) FROM table WHERE id BETWEEN 10 AND 50
$mpdo->select('table')->columns([new Sum('amount')])->where('id', new Between(10, 50))->cell();

// SELECT table.id AS id, table.name AS name, join_table.lastname AS lastname FROM table INNER JOIN join_table ON table.id=join_table.id
$mpdo->select('table')->columns([
    'id' => 'table.id',
    'name' => 'table.name',
    'lastname' => 'join_table.lastname',
])->innerJoin('join_table')->on('table.id', 'join_table.id')->rows();

// SELECT * FROM table ORDER BY id ASC
$mpdo->select('table')->orderBy('id')->rows();

// SELECT * FROM table LIMIT 1 OFFSET 10
$mpdo->select('table')->limit(1, 10)->row();

//
// Update example
//

// UPDATE table SET name='Mr. Gorski' WHERE id=10
$mpdo->update('table')->set(['name' => 'Mr. Gorski'])->where('id', 10)->execute();

//
// Delete example
//

// DELETE FROM table WHERE id NOT IN (10, 11, 20)
$mpdo->delete('table')->where('id', new NotIn([10, 11, 20]))->execute();

//
// Create Table
//

// CREATE TABLE IF NOT EXISTS table (id INT NOT NULL, email TEXT NOT NULL, name VARCHAR(32) NOT NULL)
$mpdo->createTable('table')->checkIfExists()->fields([
    new IntField('id'),
    new TextField('email'),
    new VarcharField('name', 32),
])->execute();

// CREATE TABLE IF NOT EXISTS table (id INT UNSIGNED NULL DEFAULT 100)
$mpdo->createTable('table')->checkIfExists()->fields([
    new IntField('id', unsigned: true, canBeNull: true, default: 100),
])->execute();

// CREATE TABLE IF NOT EXISTS table (id INT NOT NULL, PRIMARY KEY (id))
$mpdo->createTable('table')->checkIfExists()->fields([
    new IntField('id'),
])->keys([
    new PrimaryKey('id'),
])->execute();

//
// Update Table
//

// ALTER TABLE table RENAME TO new_table
$mpdo->alterTable('table')->rename('new_table')->execute();

// ALTER TABLE table ADD COLUMN amount INT NOT NULL
$mpdo->alterTable('table')->addColumns([
    new IntField('amount'),
])->execute();

// ALTER TABLE table RENAME COLUMN column TO new_column
$mpdo->alterTable('table')->renameColumns([
    'column' => 'new_column',
])->execute();

// ALTER TABLE table DROP COLUMN column
$mpdo->alterTable('table')->dropColumns([
    'column',
])->execute();

//
// Drop Table
//

// DROP TABLE IF EXISTS table
$mpdo->dropTable('table')->checkIfExists()->execute();

$transaction = $mpdo->transaction();

$transaction->begin();

try {
    if (!$transaction->isActive()) {
        // Your code...
    }

    // Your code...

    $transaction->commit();
} catch (\Throwable $ex) {
    $transaction->rollBack();

    throw $ex;
}