MySQLDriver Class

Overview

The Kotchasan\Connection\MySQLDriver class is a specialized driver for MySQL and MariaDB databases. It supports all MySQL features including SQL functions, table optimization, and charset management.

Installation and Requirements

Requirements

• PHP 7.4 or higher
• PDO MySQL extension (pdo_mysql)
• MySQL 5.7+ or MariaDB 10.2+

Checking Extension

if (!extension_loaded('pdo_mysql')) {
    throw new \Exception('PDO MySQL extension is not installed');
}

Connection Configuration

Basic Configuration

use Kotchasan\Connection\MySQLDriver;

$config = [
    'host' => 'localhost',
    'port' => 3306,
    'database' => 'myapp',
    'username' => 'dbuser',
    'password' => 'dbpass',
    'charset' => 'utf8mb4'
];

$driver = new MySQLDriver();
$connected = $driver->connect($config);

Advanced Configuration

$config = [
    'host' => 'mysql.example.com',
    'port' => 3306,
    'database' => 'production_db',
    'username' => 'app_user',
    'password' => 'secure_password',
    'charset' => 'utf8mb4',
    'unix_socket' => '/var/run/mysqld/mysqld.sock', // For Unix socket
];

SSL Configuration

$config = [
    'host' => 'secure-mysql.example.com',
    'port' => 3306,
    'database' => 'secure_db',
    'username' => 'ssl_user',
    'password' => 'ssl_password',
    'charset' => 'utf8mb4',
    // SSL PDO options are handled by the driver
];

Basic Usage

Connecting and Checking Status

$driver = new MySQLDriver();

if ($driver->connect($config)) {
    echo "MySQL connection successful";

    if ($driver->isConnected()) {
        echo "Status: Connected";
    }
} else {
    echo "Connection failed: " . $driver->getLastError();
}

Preparing Statements

$sql = "SELECT * FROM users WHERE status = ? AND created_at > ?";
$statement = $driver->prepare($sql);

// Use statement
$result = $statement->execute(['active', '2024-01-01']);

Transaction Management

try {
    $driver->beginTransaction();

    // Multiple operations
    $stmt1 = $driver->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt1->execute(['John Doe', 'john@example.com']);

    $stmt2 = $driver->prepare("UPDATE profiles SET user_id = ? WHERE id = ?");
    $stmt2->execute([$driver->lastInsertId(), 1]);

    $driver->commit();
    echo "Transaction successful";

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

MySQL-Specific Features

Table Optimization

// Optimize single table
$success = $driver->optimizeTable('users');

if ($success) {
    echo "Table users optimized successfully";
}

// Optimize multiple tables
$tables = ['users', 'posts', 'comments'];
foreach ($tables as $table) {
    $driver->optimizeTable($table);
}

Emptying Tables

// Use TRUNCATE (faster)
$success = $driver->emptyTable('temp_data', ['use_truncate' => true]);

// Use DELETE (safer)
$success = $driver->emptyTable('logs', ['use_truncate' => false]);

SQL Injection Prevention

$userInput = "'; DROP TABLE users; --";
$escaped = $driver->escape($userInput);

// Use in query (but prepared statements are recommended)
$sql = "SELECT * FROM users WHERE name = {$escaped}";

SQL Functions

Date and Time Functions

// Extract year
$yearFunc = $driver->formatSqlFunction('YEAR', ['column' => 'created_at'], 'year_created');
// Result: YEAR(`created_at`) AS `year_created`

// Extract month
$monthFunc = $driver->formatSqlFunction('MONTH', ['column' => 'created_at'], 'month_created');
// Result: MONTH(`created_at`) AS `month_created`

// Current timestamp
$nowFunc = $driver->formatSqlFunction('NOW', [], 'current_time');
// Result: NOW() AS `current_time`

// Date formatting
$dateFormatFunc = $driver->formatSqlFunction('DATE_FORMAT', [
    'column' => 'created_at',
    'format' => '%Y-%m-%d'
], 'formatted_date');
// Result: DATE_FORMAT(`created_at`, '%Y-%m-%d') AS `formatted_date`

String Functions

// Concatenate strings
$concatFunc = $driver->formatSqlFunction('CONCAT', [
    'fields' => ['first_name', 'last_name'],
    'separator' => ' '
], 'full_name');
// Result: CONCAT_WS(' ', `first_name`, `last_name`) AS `full_name`

// Group concatenation
$groupConcatFunc = $driver->formatSqlFunction('GROUP_CONCAT', [
    'column' => 'tag_name',
    'separator' => ', ',
    'distinct' => true,
    'order' => ['tag_name']
], 'tags');
// Result: GROUP_CONCAT(DISTINCT `tag_name` ORDER BY `tag_name` SEPARATOR ', ') AS `tags`

Aggregate Functions

// Count
$countFunc = $driver->formatSqlFunction('COUNT', [
    'column' => 'id',
    'distinct' => true
], 'total_users');
// Result: COUNT(DISTINCT `id`) AS `total_users`

// Sum
$sumFunc = $driver->formatSqlFunction('SUM', ['column' => 'amount'], 'total_amount');
// Result: SUM(`amount`) AS `total_amount`

// Average
$avgFunc = $driver->formatSqlFunction('AVG', ['column' => 'score'], 'average_score');
// Result: AVG(`score`) AS `average_score`

Other Functions

// Random
$randFunc = $driver->formatSqlFunction('RAND', [], null);
// Result: RAND()

// Handle NULL values
$ifnullFunc = $driver->formatSqlFunction('IFNULL', [
    'column1' => 'nickname',
    'column2' => 'first_name'
], 'display_name');
// Result: IFNULL(`nickname`, `first_name`) AS `display_name`

// Date difference
$datediffFunc = $driver->formatSqlFunction('DATEDIFF', [
    'column1' => 'end_date',
    'column2' => 'start_date'
], 'duration_days');
// Result: DATEDIFF(`end_date`, `start_date`) AS `duration_days`

Advanced Usage Examples

Complex Query Building

$driver = new MySQLDriver();
$driver->connect($config);

// Create sales report query
$sql = "
    SELECT
        " . $driver->formatSqlFunction('YEAR', ['column' => 'order_date'], 'year') . ",
        " . $driver->formatSqlFunction('MONTH', ['column' => 'order_date'], 'month') . ",
        " . $driver->formatSqlFunction('COUNT', ['column' => 'id'], 'total_orders') . ",
        " . $driver->formatSqlFunction('SUM', ['column' => 'total_amount'], 'total_sales') . ",
        " . $driver->formatSqlFunction('AVG', ['column' => 'total_amount'], 'average_order') . "
    FROM orders
    WHERE status = 'completed'
    GROUP BY
        " . $driver->formatSqlFunction('YEAR', ['column' => 'order_date'], null) . ",
        " . $driver->formatSqlFunction('MONTH', ['column' => 'order_date'], null) . "
    ORDER BY year DESC, month DESC
";

$statement = $driver->prepare($sql);
$result = $statement->execute();

Large Data Management

// Clean old data
$driver->beginTransaction();

try {
    // Delete old data
    $stmt = $driver->prepare("DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)");
    $stmt->execute();

    // Optimize table
    $driver->optimizeTable('logs');

    $driver->commit();
    echo "Data cleanup successful";

} catch (\Exception $e) {
    $driver->rollback();
    echo "Error occurred: " . $e->getMessage();
}

Backup and Restore

// Backup table
function backupTable($driver, $tableName) {
    $backupTable = $tableName . '_backup_' . date('Ymd_His');

    $sql = "CREATE TABLE `{$backupTable}` AS SELECT * FROM `{$tableName}`";
    $statement = $driver->prepare($sql);

    return $statement->execute();
}

// Usage
if (backupTable($driver, 'users')) {
    echo "Users table backup successful";
}

Error Handling

Common Errors

try {
    $driver->connect($config);
} catch (\PDOException $e) {
    switch ($e->getCode()) {
        case 1045: // Access denied
            echo "Invalid username or password";
            break;

        case 1049: // Unknown database
            echo "Kotchasan\Database not found";
            break;

        case 2002: // Connection refused
            echo "Cannot connect to MySQL server";
            break;

        default:
            echo "MySQL error: " . $e->getMessage();
    }
}

Connection Status Check

function checkConnection($driver) {
    if (!$driver->isConnected()) {
        throw new \Exception("Not connected to database");
    }

    // Test connection
    try {
        $stmt = $driver->prepare("SELECT 1");
        $stmt->execute();
        return true;
    } catch (\Exception $e) {
        return false;
    }
}

Performance Tuning

Recommended MySQL Settings for Kotchasan

-- Recommended settings in my.cnf
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
query_cache_size = 128M
query_cache_type = 1
max_connections = 200

Efficient Index Usage

// Check indexes
$sql = "SHOW INDEX FROM users";
$statement = $driver->prepare($sql);
$result = $statement->execute();

// Create index
$sql = "CREATE INDEX idx_user_email ON users (email)";
$statement = $driver->prepare($sql);
$statement->execute();

Best Practices and Considerations

Best Practices

1. Use utf8mb4: Supports emoji and special characters
2. Use Prepared Statements: Prevent SQL injection
3. Manage Transactions: Use transactions for multi-step operations
4. Optimize Tables: Use OPTIMIZE TABLE regularly

Considerations

1. Charset: Set charset correctly
2. Connection Timeout: Set appropriate timeout values
3. Memory Usage: Be careful with large datasets

Common Issues

1. Charset Issues: Use utf8mb4 instead of utf8
2. Connection Lost: Check connection before use
3. Lock Timeout: Keep transactions as short as possible

Related Classes

• Connection - Basic connection class
• ConnectionManager - Connection manager
• DriverInterface - Driver interface
• PostgreSQLDriver - PostgreSQL driver
• PDOStatement - Statement execution