Connecting to databases in PHP can sometimes feel repetitive. Setting up PDO instances and handling different database types often leads to boilerplate code in various parts of your application. What if you could centralize your database connection logic into a clean and reusable class?

This article introduces a simple yet powerful static Database class in PHP that streamlines the process of connecting to SQLite, MySQL, and even MS Access databases using PDO. Let's dive in!

class Database
{
    private static $pdo = null;
    public static $dbName = null;
    public static $dbPath = 'data/';
    public static $dbHost = null;
    public static $dbUser = null;
    public static $dbPass = null;
    public static $dbType = null;

    /**
     * Establishes a static PDO connection to a database.
     *
     * @return PDO|string A PDO instance on successful connection, or an error message string.
     */
    public static function connect(): PDO|string
    {
        // Check if a connection already exists.
        if (self::$pdo !== null) {
            return self::$pdo;
        }

        $dbType = self::$dbType;

        switch ($dbType) {
            case 'sqlite':
                return self::connectToSQLite();
            case 'mysql':
                return self::connectToMySQL();
            case 'access':
                return self::connectToMsAccess();
            default:
                return "Error: Invalid database type specified: '$dbType'.";
        }
    }

    /**
     * Establishes a static PDO connection to a SQLite database.
     *
     * @return PDO|string A PDO instance on successful connection, or an error message string.
     */
    private static function connectToSQLite(): PDO|string
    {
        $dbPath = self::$dbPath;
        $dbName = self::$dbName;

        // Ensure the database directory exists.
        if (!is_dir($dbPath)) {
            if (!mkdir($dbPath, 0755, true)) {
                return "Error: Could not create database directory '$dbPath'.";
            }
        }

        // Construct the full database file path.
        $dbFile = $dbPath . $dbName . '.db';

        try {
            // Create a new PDO instance for SQLite.
            self::$pdo = new PDO("sqlite:$dbFile");
            // Set PDO error mode to exception.
            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            // Set default fetch mode to associative array.
            self::$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            return self::$pdo;
        } catch (PDOException $e) {
            // Return an error message if the connection fails.
            return "Error: Database connection failed: " . $e->getMessage();
        }
    }

    /**
     * Establishes a static PDO connection to a MySQL database.
     *
     * @return PDO|string A PDO instance on successful connection, or an error message string.
     */
    private static function connectToMySQL(): PDO|string
    {
        $dbHost = self::$dbHost;
        $dbName = self::$dbName;
        $dbUser = self::$dbUser;
        $dbPass = self::$dbPass;

        try {
            // Create a new PDO instance for MySQL.
            self::$pdo = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUser, $dbPass);
            // Set PDO error mode to exception.
            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            // Set default fetch mode to associative array.
            self::$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            return self::$pdo;
        } catch (PDOException $e) {
            // Return an error message if the connection fails.
            return "Error: MySQL database connection failed: " . $e->getMessage();
        }
    }

    /**
     * Establishes a static PDO connection to a MS Access database.
     *
     * @return PDO|string A PDO instance on successful connection, or an error message string.
     */
    private static function connectToMsAccess(): PDO|string
    {
        $dbPath = self::$dbPath;
        $dbName = self::$dbName;

        // Ensure the database directory exists.
        if (!is_dir($dbPath)) {
            if (!mkdir($dbPath, 0755, true)) {
                return "Error: Could not create database directory '$dbPath'.";
            }
        }

        // Construct the full database file path.
        $dbFile = $dbPath . $dbName . '.mdb';

        try {
            // Create a new PDO instance for MS Access.
            self::$pdo = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=$dbFile;");
            // Set PDO error mode to exception.
            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            // Set default fetch mode to associative array.
            self::$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            return self::$pdo;
        } catch (PDOException $e) {
            // Return an error message if the connection fails.
            return "Error: MS Access database connection failed: " . $e->getMessage();
        }
    }

    /**
     * Closes the static PDO connection.
     *
     * This method is optional, as PHP will automatically close the connection
     * at the end of the script's execution. However, you can use it to
     * explicitly close the connection if needed.
     */
    public static function closeConnection(): void
    {
        self::$pdo = null;
    }
}
?>

Key Features and Explanation

This Database class offers several advantages:

  • Static Methods and Properties: The use of static methods and properties means you don't need to instantiate the Database class every time you need a connection. You can directly access its methods and properties using the class name (e.g., Database::$dbType).
  • Centralized Connection Logic: All the logic for establishing database connections is encapsulated within this single class, making your code cleaner and easier to maintain.
  • Multiple Database Support: The class currently supports three popular database systems: SQLite, MySQL, and MS Access. It's designed to be easily extensible to support other database types in the future.
  • PDO for Database Interaction: It utilizes PDO (PHP Data Objects), a consistent interface for accessing different databases, promoting portability and security.
  • Error Handling: The connect method and its helper functions return either a PDO instance on successful connection or an informative error message as a string if the connection fails.
  • SQLite Directory Creation: For SQLite connections, the class automatically checks if the specified database directory exists and creates it if it doesn't.
  • Default Fetch Mode: It sets the default PDO fetch mode to PDO::FETCH_ASSOC, which returns results as associative arrays, often the most convenient format for working with data in PHP.

Let's break down the code:

Properties:

  • private static $pdo = null;: This static property will hold the single PDO instance once a connection is established. It's initialized to null and used to implement a simple singleton pattern within the class.
  • public static $dbName = null;: Stores the name of the database.
  • public static $dbPath = 'data/';: Specifies the path to the database file (primarily used for SQLite and MS Access). It defaults to a data/ directory in the project root.
  • public static $dbHost = null;: Stores the hostname or IP address of the database server (used for MySQL).
  • public static $dbUser = null;: Stores the username for database authentication (used for MySQL).
  • public static $dbPass = null;: Stores the password for database authentication (used for MySQL).
  • public static $dbType = null;: Specifies the type of database you want to connect to ('sqlite', 'mysql', or 'access').

Methods:

  • public static function connect(): PDO|string: This is the main method you'll call to get a database connection. It first checks if a connection already exists (self::$pdo !== null). If so, it returns the existing connection. Otherwise, it uses a switch statement based on the value of self::$dbType to call the appropriate connection method (connectToSQLite, connectToMySQL, or connectToMsAccess). If an invalid database type is provided, it returns an error message.
  • private static function connectToSQLite(): PDO|string: Handles the connection to a SQLite database. It constructs the database file path, ensures the directory exists, and then creates a new PDO instance for SQLite. It also sets the error mode to exceptions and the default fetch mode to associative arrays.
  • private static function connectToMySQL(): PDO|string: Handles the connection to a MySQL database. It uses the provided $dbHost, $dbName, $dbUser, and $dbPass to create a PDO instance for MySQL and sets the error mode and fetch mode.
  • private static function connectToMsAccess(): PDO|string: Handles the connection to an MS Access database. Similar to SQLite, it constructs the database file path and creates a PDO instance using the appropriate ODBC driver. Note: You'll need to ensure the Microsoft Access Database Engine is installed on your system for this to work.
  • public static function closeConnection(): void: This optional method explicitly sets the $pdo instance to null, effectively closing the database connection. While PHP automatically closes connections at the end of a script, you might want to use this in long-running scripts or specific scenarios to free up resources.

How to Use

To use this Database class in your project, you simply need to include the file containing the class definition. Then, before you need to interact with the database, set the necessary static properties based on your database configuration.

Here are examples for each supported database type:

SQLite:

require_once 'Database.php'; // Assuming the class is in 'Database.php'

Database::$dbType = 'sqlite';
Database::$dbName = 'my_app'; // Will create 'data/my_app.db' by default

$pdo = Database::connect();

if ($pdo instanceof PDO) {
    // Perform SQLite database operations here
    $stmt = $pdo->query("SELECT SQLITE_VERSION()");
    $version = $stmt->fetchColumn();
    echo "SQLite Version: " . $version . "\n";
    Database::closeConnection();
} else {
    echo $pdo; // Output the error message
}
?>

MySQL:

require_once 'Database.php';

Database::$dbType = 'mysql';
Database::$dbHost = 'localhost';
Database::$dbName = 'my_database';
Database::$dbUser = 'my_user';
Database::$dbPass = 'my_password';

$pdo = Database::connect();

if ($pdo instanceof PDO) {
    // Perform MySQL database operations here
    $stmt = $pdo->query("SELECT VERSION()");
    $version = $stmt->fetchColumn();
    echo "MySQL Version: " . $version . "\n";
    Database::closeConnection();
} else {
    echo $pdo;
}
?>

MS Access:

require_once 'Database.php';

Database::$dbType = 'access';
Database::$dbName = 'my_access_database'; // Will look for 'data/my_access_database.mdb'

$pdo = Database::connect();

if ($pdo instanceof PDO) {
    // Perform MS Access database operations here
    try {
        $stmt = $pdo->query("SELECT * FROM MyTable"); // Replace MyTable with your table name
        $results = $stmt->fetchAll();
        print_r($results);
    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
    Database::closeConnection();
} else {
    echo $pdo;
}
?>

Remember to replace the placeholder values (like 'your_mysql_db', 'your_mysql_user', 'your_mysql_password', and 'my_access_database') with your actual database credentials and file names.

Conclusion

This static Database class provides a convenient and organized way to handle database connections in your PHP projects. By centralizing the connection logic and supporting multiple database types, it can significantly simplify your code and improve maintainability.

Feel free to adapt and extend this class to suit your specific needs. You could add more database types, implement connection pooling, or introduce configuration options for PDO attributes.

Happy coding!