1. Go to this page and download the library: Download ocallit/sqler 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/ */
ocallit / sqler example snippets
use Ocallit\Sqler\SqlExecutor;
use Ocallit\Sqler\QueryBuilder;
use Ocallit\Sqler\DatabaseMetadata;
use Ocallit\Sqler\Historian;
// Initialize database connection
$sql = new SqlExecutor([
'hostname' => 'localhost',
'username' => 'user',
'password' => 'password',
'database' => 'mydb'
]);
// Execute queries in different result formats
$userCount = $sql->firstValue("SELECT COUNT(*) FROM users WHERE active = ?", [1]);
$user = $sql->row("SELECT * FROM users WHERE id = ?", [123]);
$users = $sql->array("SELECT * FROM users WHERE department = ?", ['IT']);
$usersByDept = $sql->arrayKeyed("SELECT * FROM users", 'department');
// Build queries safely
$qb = new QueryBuilder();
$insert = $qb->insert('users', [
'name' => 'John Doe',
'email' => '[email protected]',
'created_at' => 'NOW()', // MySQL functions recognized
'active' => 1
], true); // Enable ON DUPLICATE KEY UPDATE
$sql->query($insert['query'], $insert['parameters']);
// Different result shapes for different needs
$count = $sql->firstValue("SELECT COUNT(*) FROM orders"); // Single value
$order = $sql->row("SELECT * FROM orders WHERE id = ?", [123]); // Single row
$orders = $sql->array("SELECT * FROM orders WHERE status = ?", ['pending']); // All rows
$ordersByStatus = $sql->arrayKeyed("SELECT * FROM orders", 'status'); // Keyed by column
$statusList = $sql->vector("SELECT DISTINCT status FROM orders"); // Single column values
$statusCounts = $sql->keyValue("SELECT status, COUNT(*) FROM orders GROUP BY status"); // Key-value pairs
try {
$sql->query("UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?", [5, 123]);
} catch (Exception $e) {
if ($sql->is_last_error_duplicate_key()) {
// Handle unique constraint violation
} elseif ($sql->is_last_error_table_not_found()) {
// Handle missing table
}
// Other specific error checks available
}
$qb = new QueryBuilder();
// INSERT with ON DUPLICATE KEY UPDATE
$insert = $qb->insert('products', [
'name' => 'Widget',
'price' => 29.99,
'updated_at' => 'NOW()',
'stock' => 100
], true, ['created_at']); // Update on duplicate, but don't touch created_at
// UPDATE with WHERE conditions
$update = $qb->update('products',
['price' => 34.99, 'updated_at' => 'NOW()'],
['category' => 'electronics', 'active' => 1]
);
// Complex WHERE clauses
$where = $qb->where([
'category' => ['electronics', 'gadgets'], // IN clause
'price' => 50, // Exact match
'created_date' => 'CURDATE()', // MySQL function
'active' => 1
], 'AND');
$sql->query($update['query'], $update['parameters']);
// Initialize once
DatabaseMetadata::initialize($sql);
$meta = DatabaseMetadata::getInstance();
// Get table structure
$userColumns = $meta->table('users');
foreach ($userColumns as $column) {
echo "{$column['Field']} - {$column['Type']} - {$column['Key']}\n";
}
// Get relationships
$primaryKeys = $meta->primaryKeys();
$foreignKeys = $meta->getForeignKeys('orders');
$allRelationships = $meta->foreignKeysAll();
// Analyze query results
$queryMeta = $meta->query("SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id");
// Returns detailed info about each column including source table and type
$historian = new Historian($sql, 'users', ['id']); // Track 'users' table, primary key is 'id'
// Track an INSERT
$sql->query("INSERT INTO users (name, email) VALUES (?, ?)", ['Alice', '[email protected]']);
$newUser = $sql->row("SELECT * FROM users WHERE id = ?", [$sql->last_insert_id()]);
$historian->register('insert', ['id' => $newUser['id']], $newUser, 'admin', 'New user registration');
// Track an UPDATE
$sql->query("UPDATE users SET email = ? WHERE id = ?", ['[email protected]', 123]);
$updatedUser = $sql->row("SELECT * FROM users WHERE id = ?", [123]);
$historian->register('update', ['id' => 123], $updatedUser, 'admin', 'Email change request');
// Track a DELETE
$userToDelete = $sql->row("SELECT * FROM users WHERE id = ?", [123]);
$sql->query("DELETE FROM users WHERE id = ?", [123]);
$historian->register('delete', ['id' => 123], $userToDelete, 'admin', 'Account deactivation');
// Get change history with automatic diff analysis
$changes = $historian->getChanges(['id' => 123]);
foreach ($changes as $change) {
echo "Action: {$change['action']} by {$change['user_nick']} on {$change['date']}\n";
foreach ($change['diff'] as $field => $diff) {
echo " {$field}: '{$diff['before']}' → '{$diff['after']}'\n";
}
}
// Automatic transaction with retry
$sql->transaction([
"UPDATE accounts SET balance = balance - 100 WHERE id = 1",
"UPDATE accounts SET balance = balance + 100 WHERE id = 2",
"INSERT INTO transactions (from_account, to_account, amount) VALUES (1, 2, 100)"
], 'Money transfer');
// Manual transaction control
$sql->begin('Complex operation');
try {
$sql->query("UPDATE inventory SET quantity = quantity - ? WHERE id = ?", [5, 123]);
$sql->query("INSERT INTO orders (product_id, quantity) VALUES (?, ?)", [123, 5]);
$sql->commit('Complex operation');
} catch (Exception $e) {
$sql->rollback('Complex operation');
throw $e;
}
// The library automatically retries on:
// - Deadlocks (1213)
// - Lock wait timeouts (1205)
// - Connection lost errors (2006, 2013)
// - Other transient errors
// Access logs for debugging
$queryLog = $sql->getLog(); // All executed queries
$errorLog = $sql->getErrorLog(); // All errors with retry attempts
// Specific error type checking
if ($sql->is_last_error_foreign_key_violation()) {
echo "Referenced record doesn't exist";
} elseif ($sql->is_last_error_child_records_exist()) {
echo "Cannot delete: child records exist";
}