Mastering PHP Database Operations with DataBaseManager: A Comprehensive Tutorial for MariaDB and MySQL

In today's data-driven world, efficient database management is crucial for any application. Whether you're working with MariaDB or MySQL, having a robust abstraction layer can significantly simplify your database operations. In this tutorial, we'll explore the powerful DataBaseManager class, which is included with the TurboDepot library and provides a unified and super easy interface for working with both MariaDB and MySQL databases.

Table of Contents

  1. Introduction to DataBaseManager
  2. Getting Started
  3. Connecting to a Database
  4. Basic Database Operations
  5. Table Management
  6. Query Execution and Data Manipulation
  7. Transaction Handling
  8. Advanced Features
  9. Best Practices and Tips
  10. Conclusion

Introduction to DataBaseManager

The DataBaseManager class is a versatile PHP class that provides an abstraction layer for database operations. It supports both MariaDB and MySQL, allowing you to switch between these database engines seamlessly. Some key features include:

Getting Started

Before proceeding, ensure that the following requirements are met:

Download the latest TurboCommons and TurboDepot phar files and place them on your project as dependencies. Then you'll be able to directly use the DataBaseManager class:

require 'path/to/your/dependencies/folder/turbocommons-php-X.X.X.phar';
require 'path/to/your/dependencies/folder/turbodepot-php-X.X.X.phar';

use org\turbodepot\src\main\php\managers\DataBaseManager;

$dbManager = new DataBaseManager();

Connecting to a Database

You can connect to either a MariaDB or MySQL database using the following methods:

// For MariaDB
$connected = $dbManager->connectMariaDb('localhost', 'username', 'password', 'database_name');

// For MySQL
$connected = $dbManager->connectMysql('localhost', 'username', 'password', 'database_name');

if ($connected) {
    echo "Connected successfully!";
} else {
    echo "Connection failed: " . $dbManager->getLastError();
}

Basic Database Operations

Checking Database Existence

if ($dbManager->dataBaseExists('my_database')) {
    echo "Database exists!";
} else {
    echo "Database does not exist.";
}

Creating a Database

try {
    $dbManager->dataBaseCreate('my_new_database');
    echo "Database created successfully!";
} catch (UnexpectedValueException $e) {
    echo "Error creating database: " . $e->getMessage();
}

Selecting a Database

try {
    $dbManager->dataBaseSelect('my_database');
    echo "Database selected successfully!";
} catch (UnexpectedValueException $e) {
    echo "Error selecting database: " . $e->getMessage();
}

Table Management

Creating a Table

$columns = [
    'id INT AUTO_INCREMENT PRIMARY KEY',
    'name VARCHAR(50) NOT NULL',
    'email VARCHAR(100) UNIQUE'
];

try {
    $dbManager->tableCreate('users', $columns);
    echo "Table created successfully!";
} catch (UnexpectedValueException $e) {
    echo "Error creating table: " . $e->getMessage();
}

Adding Columns

try {
    $dbManager->tableAddColumn('users', 'age', 'INT');
    echo "Column added successfully!";
} catch (UnexpectedValueException $e) {
    echo "Error adding column: " . $e->getMessage();
}

Getting Table Information

$columnNames = $dbManager->tableGetColumnNames('users');
$columnTypes = $dbManager->tableGetColumnDataTypes('users');

print_r($columnNames);
print_r($columnTypes);

Query Execution and Data Manipulation

Executing Custom Queries

try {
    $result = $dbManager->query("SELECT * FROM users WHERE age > 18");
    print_r($result);
} catch (UnexpectedValueException $e) {
    echo "Query error: " . $e->getMessage();
}

Inserting Data

$rows = [
    ['name' => 'John Doe', 'email' => '[email protected]', 'age' => 30],
    ['name' => 'Jane Smith', 'email' => '[email protected]', 'age' => 25]
];

try {
    $dbManager->tableAddRows('users', $rows);
    echo "Rows inserted successfully!";
} catch (UnexpectedValueException $e) {
    echo "Error inserting rows: " . $e->getMessage();
}

Updating Data

$keyValues = ['id' => 1];
$rowValues = ['age' => 31];

try {
    $dbManager->tableUpdateRow('users', $keyValues, $rowValues);
    echo "Row updated successfully!";
} catch (UnexpectedValueException $e) {
    echo "Error updating row: " . $e->getMessage();
}

Deleting Data

$columnValues = ['id' => 2];

try {
    $dbManager->tableDeleteRows('users', $columnValues);
    echo "Row(s) deleted successfully!";
} catch (UnexpectedValueException $e) {
    echo "Error deleting row(s): " . $e->getMessage();
}

Transaction Handling

The DataBaseManager class supports transaction management, which is crucial for maintaining data integrity:

try {
    $dbManager->transactionBegin();

    // Perform multiple operations
    $dbManager->tableAddRows('users', [['name' => 'Alice', 'email' => '[email protected]', 'age' => 28]]);
    $dbManager->tableUpdateRow('users', ['id' => 1], ['age' => 32]);

    $dbManager->transactionCommit();
    echo "Transaction completed successfully!";
} catch (UnexpectedValueException $e) {
    $dbManager->transactionRollback();
    echo "Transaction failed: " . $e->getMessage();
}

Advanced Features

Performance Monitoring

The DataBaseManager class provides built-in performance monitoring features:

// Set warning thresholds
$dbManager->warnForSlowQueries = 1; // throw a Php Warning for queries taking more than 1 second
$dbManager->warnForSlowQueriesTotal = 5; // throw a Php Warning when total accumulated query time exceeds 5 seconds

// After executing queries, you can check the query history
$queryHistory = $dbManager->getQueryHistory();
print_r($queryHistory);

Table Synchronization

The tableAlterToFitDefinition method allows you to synchronize your table structure with a defined schema. It tries to be the less destructive possible to prevent data loss when altering table structures:

$tableDef = [
    'columns' => [
        'id INT AUTO_INCREMENT PRIMARY KEY',
        'name VARCHAR(50) NOT NULL',
        'email VARCHAR(100) UNIQUE',
        'age INT'
    ],
    'primaryKey' => ['id'],
    'uniqueIndices' => [['email']],
    'indices' => [['name']],
    'deleteColumns' => 'yes',
    'resizeColumns' => true
];

try {
    $isModified = $dbManager->tableAlterToFitDefinition('users', $tableDef);
    echo $isModified ? "Table structure updated." : "No changes needed.";
} catch (UnexpectedValueException $e) {
    echo "Error synchronizing table: " . $e->getMessage();
}

Best Practices and Tips

  1. Always use transactions for operations that involve multiple queries to ensure data consistency.
  2. Handle exceptions properly to catch and log any database errors.
  3. Use prepared statements when dealing with user input to prevent SQL injection attacks.
  4. Monitor query performance using the built-in warning system to identify and optimize slow queries.
  5. Regularly backup your database before performing any major structural changes.
  6. Use meaningful table and column names to improve code readability and maintainability.
  7. Keep your database schema in sync with your application code using the tableAlterToFitDefinition method.

Conclusion

The DataBaseManager class provides a powerful and flexible way to work with MariaDB and MySQL databases in PHP. By abstracting away many of the complexities of database management, it allows developers to focus on building robust and efficient applications.

Whether you're creating tables, executing queries, or managing transactions, this class offers a comprehensive set of tools to streamline your database operations. By following the best practices outlined in this tutorial, you'll be well-equipped to build scalable and maintainable database-driven applications.

Remember to always test your code thoroughly, especially when working with critical data, and to keep your DataBaseManager class up-to-date with the latest improvements and security patches.

Happy coding, and may your queries be ever swift and your transactions always consistent!