ExPDO Full Documentation

ExPDO is an extended PDO class for MySQL, providing convenient, secure, and flexible methods for database operations. It supports associative arrays, objects, parameterized queries, and includes helpers for SQL generation.

1. Constructor & Connection

use Nobanno\ExPDO;
$db = new ExPDO('localhost', 'database', 'user', 'password');

Sets ERRMODE_EXCEPTION, disables emulated prepares, uses utf8, and buffered queries.

2. Insert Methods

insert($sql, $data = null) (deprecated, use executeInsert)
executeInsert($sql, $data = null)
insertAuto($table, $data, $isParam = true) (deprecated, use insertRecord)
insertRecord($table, $data, $isParam = true)

// Parameterized insert
$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$db->executeInsert($sql, ['name' => 'Alice', 'email' => 'alice@example.com']);

// Auto insert
$db->insertRecord('users', ['name' => 'Bob', 'email' => 'bob@example.com']);

3. Select Methods

fetchAssoc($sql, $args = null): Single row as assoc array
fetchAssocs($sql, $args = null): Multiple rows as assoc arrays
fetchObject($sql, $args = null): Single row as object
fetchObjects($sql, $args = null): Multiple rows as objects
fetchClass($sql, $class, $args = null): Single row as class instance
fetchClasses($sql, $class, $args = null): Multiple rows as class instances
fetchColumnValues($sql, $args = null, $col = 0): Array of values from one column

// Fetch single user as assoc array
$user = $db->fetchAssoc("SELECT * FROM users WHERE id = :id", ['id' => 1]);

// Fetch all users as objects
$users = $db->fetchObjects("SELECT * FROM users");

4. Update Methods

update($sql, $data = null) (deprecated, use executeUpdate)
executeUpdate($sql, $data = null)
updateAuto($table, $data, $where, $isParam = true) (deprecated, use updateRecord)
updateRecord($table, $data, $where, $isParam = true)

// Parameterized update
$sql = "UPDATE users SET name = :name WHERE id = :id";
$db->executeUpdate($sql, ['name' => 'Alice', 'id' => 1]);

// Auto update
$db->updateRecord('users', ['name' => 'Bob'], ['id' => 2]);

5. Delete Methods

delete($sql, $data = null)

// Parameterized delete
$sql = "DELETE FROM users WHERE id = :id";
$db->delete($sql, ['id' => 1]);

6. SQL Preparation Helpers

prepareInsertSql($data, $table, $isParam = true)
prepareSelectSql($columns, $table, $where, $isParam)
prepareUpdateSql($table, $data, $where, $isParam)
prepareDeleteSql($where, $table, $isParam)
prepareWhereClause($where, $isParam)

// Example: Generate insert SQL
$sql = ExPDO::prepareInsertSql(['name' => 'Alice', 'email' => 'alice@example.com'], 'users');
// Returns: INSERT INTO users(`name`, `email`) VALUES(:name, :email)

7. Utility & Error Handling

All methods throw PDOException with detailed backtrace on error.
Supports both arrays and objects for data/parameters.

8. Best Practices

Always use parameterized queries for user input.
Use the auto methods (insertRecord, updateRecord) for convenience and safety.
Handle exceptions with try/catch.
Review SQL generated by helpers for complex queries.

9. Example: Full CRUD

// Create
$userId = $db->insertRecord('users', ['name' => 'Alice', 'email' => 'alice@example.com']);
// Read
$user = $db->fetchAssoc('SELECT * FROM users WHERE id = :id', ['id' => $userId]);
// Update
$db->updateRecord('users', ['name' => 'Alicia'], ['id' => $userId]);
// Delete
$db->delete('DELETE FROM users WHERE id = :id', ['id' => $userId]);