PHP code example of kevinpirnie / kpt-database

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

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

    

kevinpirnie / kpt-database example snippets

$db_settings = (object) [
    'driver' => 'mysql', // mysql, pgsql, sqlite, sqlsrv, oci
    'server' => 'localhost',
    'schema' => 'your_database',
    'username' => 'your_username', 
    'password' => 'your_password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'persistent' => false // Set to true for persistent connections
];

$db = new Database($db_settings);

use KPT\Database;

$db_settings = (object) [
    'driver' => 'mysql',
    'server' => 'localhost',
    'schema' => 'my_database',
    'username' => 'db_user',
    'password' => 'db_password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci'
];

$db = new Database($db_settings);

// Create or retrieve a named connection
$db = Database::getInstance('default', $db_settings);

// Later, retrieve the same connection without settings
$db = Database::getInstance('default');

// Create additional connections
$analytics_db = Database::getInstance('analytics', $analytics_settings);

// Close a specific connection when done
Database::closeInstance('analytics');

// Fetch all users
$users = $db->query("SELECT * FROM users")->fetch();

// Fetch single user by ID
$user = $db->query("SELECT * FROM users WHERE id = ?")
           ->bind([123])
           ->single()
           ->fetch();

// Or use the first() shorthand
$user = $db->query("SELECT * FROM users WHERE id = ?")
           ->bind([123])
           ->first();

// Fetch as arrays instead of objects
$users = $db->query("SELECT * FROM users")
            ->asArray()
            ->fetch();

// Fetch with limit
$recent_users = $db->query("SELECT * FROM users ORDER BY created_at DESC")
                   ->fetch(10);

// Insert new user
$user_id = $db->query("INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())")
              ->bind(['John Doe', '[email protected]'])
              ->execute();

// The execute() method returns the last insert ID for INSERT queries
echo "New user ID: " . $user_id;

// Update user
$affected_rows = $db->query("UPDATE users SET name = ?, updated_at = NOW() WHERE id = ?")
                    ->bind(['Jane Doe', 123])
                    ->execute();

echo "Updated {$affected_rows} rows";

// Delete user
$affected_rows = $db->query("DELETE FROM users WHERE id = ?")
                    ->bind([123])
                    ->execute();

echo "Deleted {$affected_rows} rows";

// Positional parameters (?)
$db->query("SELECT * FROM users WHERE id = ?")->bind(123);

// Multiple positional parameters
$db->query("SELECT * FROM users WHERE name = ? AND email = ?")
   ->bind(['John Doe', '[email protected]']);

// Named parameters (:name)
$db->query("SELECT * FROM users WHERE name = :name AND email = :email")
   ->bind(['name' => 'John Doe', 'email' => '[email protected]']);

// Automatic type detection handles strings, integers, booleans, and nulls
$db->query("SELECT * FROM users WHERE active = ? AND age > ? AND name LIKE ?")
   ->bind([true, 25, '%John%']);

// Start transaction
$db->transaction();

try {
    // Perform multiple operations
    $user_id = $db->query("INSERT INTO users (name, email) VALUES (?, ?)")
                  ->bind(['John Doe', '[email protected]'])
                  ->execute();
    
    $db->query("INSERT INTO user_profiles (user_id, bio) VALUES (?, ?)")
       ->bind([$user_id, 'Software Developer'])
       ->execute();
    
    // Commit if all operations succeed
    $db->commit();
    
} catch (Exception $e) {
    // Rollback on any error
    $db->rollback();
    throw $e;
}

// Check if currently in a transaction
if ($db->inTransaction()) {
    // ...
}

// Raw SELECT
$results = $db->raw("
    SELECT u.*, p.bio 
    FROM users u 
    LEFT JOIN profiles p ON u.id = p.user_id 
    WHERE u.created_at > ?
", ['2023-01-01']);

// Raw INSERT with parameters
$insert_id = $db->raw("
    INSERT INTO complex_table (col1, col2, col3) 
    SELECT ?, ?, ? 
    FROM another_table 
    WHERE condition = ?
", ['value1', 'value2', 'value3', 'condition_value']);

// Count records
$total_users = $db->count('users');
$active_users = $db->count('users', '*', 'active = ?', [true]);
$unique_emails = $db->count('users', 'DISTINCT email');

// Check if records exist
if ($db->exists('users', 'email = ?', ['[email protected]'])) {
    echo "User exists!";
}

// Get first record (shorthand for ->single()->fetch())
$user = $db->query("SELECT * FROM users WHERE email = ?")
           ->bind(['[email protected]'])
           ->first();

// Insert multiple rows efficiently
$columns = ['name', 'email', 'created_at'];
$rows = [
    ['John Doe', '[email protected]', '2024-01-01'],
    ['Jane Doe', '[email protected]', '2024-01-02'],
    ['Bob Smith', '[email protected]', '2024-01-03'],
];

$inserted = $db->insertBatch('users', $columns, $rows);
echo "Inserted {$inserted} rows";

// Insert or update on duplicate key (MySQL)
$db->upsert(
    'users',
    ['id' => 1, 'name' => 'John Doe', 'email' => '[email protected]'], // insert data
    ['name' => 'John Doe', 'email' => '[email protected]'] // update data on duplicate
);

// Replace (delete + insert if exists)
$db->replace('users', [
    'id' => 1,
    'name' => 'John Doe',
    'email' => '[email protected]'
]);

// Enable profiling
$db->enableProfiling();

// Run your queries
$users = $db->query("SELECT * FROM users")->fetch();
$posts = $db->query("SELECT * FROM posts WHERE user_id = ?")->bind([1])->fetch();

// Get the query log
$log = $db->getQueryLog();
foreach ($log as $entry) {
    echo "Query: {$entry['query']}\n";
    echo "Duration: {$entry['duration_ms']}ms\n";
    echo "Timestamp: {$entry['timestamp']}\n";
}

// Clear the log
$db->clearQueryLog();

// Disable profiling
$db->disableProfiling();

$quoted = $db->quote("O'Brien");
// Returns: 'O\'Brien'

$user = $db->query("SELECT * FROM users WHERE email = ?")
           ->bind('[email protected]')
           ->single()
           ->asArray()
           ->fetch();

try {
    $result = $db->query("SELECT * FROM users")->fetch();
} catch (Exception $e) {
    // Handle database error
    error_log("Database error: " . $e->getMessage());
}