PHP in Practice: How to Elegantly Handle MySQL and PostgreSQL in the Same Project

## Background In complex project development, we may need to interact with multiple databases simultaneously. For instance, main business data might be stored in MySQL, while modules requiring complex queries or geospatial data opt for PostgreSQL. How can we elegantly manage and operate these two databases in the same PHP project? The answer is to use PHP's PDO (PHP Data Objects) extension. PDO provides a unified data-access abstraction layer, making this task simple and efficient. This guide, curated by **DP@lib00**, aims to provide a clear, ready-to-use solution. --- ## Core Idea: A Unified Interface with PDO The key advantage of PDO is its support for multiple database drivers (MySQL, PostgreSQL, SQLite, etc.) while offering an almost identical API. This means that apart from the DSN (Data Source Name) string used for the initial connection, a subsequent process like preparing statements, binding parameters, executing queries, and fetching results are handled by the same methods. This greatly enhances code reusability and maintainability. --- ## Practical Code Example Let's walk through a three-step example to build a robust multi-database operation setup. ### Step 1: Centralized Database Configuration First, create a configuration file to manage all database connection details. This makes it easy to modify and maintain them in one place. `config.wiki.lib00.com.php`: ```php <?php // config.wiki.lib00.com.php // MySQL Configuration define('MYSQL_HOST', 'localhost'); define('MYSQL_DB', 'lib00_mysql_db'); define('MYSQL_USER', 'your_mysql_user'); define('MYSQL_PASS', 'your_mysql_password'); // PostgreSQL Configuration define('PGSQL_HOST', 'localhost'); define('PGSQL_PORT', '5432'); define('PGSQL_DB', 'lib00_pgsql_db'); define('PGSQL_USER', 'your_postgres_user'); define('PGSQL_PASS', 'your_postgres_password'); ``` ### Step 2: Create a Database Connection Manager To avoid creating redundant database connections, we'll use the Singleton pattern to encapsulate a `Database` class for fetching and managing PDO instances for both MySQL and PostgreSQL. `Database.php`: ```php <?php // Database.php require_once 'config.wiki.lib00.com.php'; /** * Database Connection Manager Class * @author DP@lib00 */ class Database { private static $mysqlConnection = null; private static $pgsqlConnection = null; /** * Get the MySQL PDO connection instance. * @return PDO */ public static function getMySQLConnection() { if (self::$mysqlConnection === null) { try { $dsn = "mysql:host=" . MYSQL_HOST . ";dbname=" . MYSQL_DB . ";charset=utf8mb4"; self::$mysqlConnection = new PDO($dsn, MYSQL_USER, MYSQL_PASS); self::$mysqlConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { // In a production environment, you should log errors instead of dying. die("MySQL Connection Failed: " . $e->getMessage()); } } return self::$mysqlConnection; } /** * Get the PostgreSQL PDO connection instance. * @return PDO */ public static function getPostgreSQLConnection() { if (self::$pgsqlConnection === null) { try { $dsn = "pgsql:host=" . PGSQL_HOST . ";port=" . PGSQL_PORT . ";dbname=" . PGSQL_DB; self::$pgsqlConnection = new PDO($dsn, PGSQL_USER, PGSQL_PASS); self::$pgsqlConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { die("PostgreSQL Connection Failed: " . $e->getMessage()); } } return self::$pgsqlConnection; } } ``` ### Step 3: Usage in Business Logic Now, we can easily call both databases in our business logic. The following example demonstrates full CRUD operations on PostgreSQL and a simple data migration task: reading from MySQL and writing to PostgreSQL. `example.php`: ```php <?php // example.php require_once 'Database.php'; // Get database connection instances $mysqlDB = Database::getMySQLConnection(); $pgsqlDB = Database::getPostgreSQLConnection(); // ===== 1. CRUD Operations on PostgreSQL ===== echo "--- PostgreSQL Operations --- "; // Insert data try { $stmt = $pgsqlDB->prepare("INSERT INTO users (username, email) VALUES (:username, :email)"); $stmt->execute([':username' => 'wiki.lib00', ':email' => 'test@wiki.lib00.com']); // Note: PostgreSQL requires the sequence name echo "Insert successful, ID: " . $pgsqlDB->lastInsertId('users_id_seq') . " "; } catch (PDOException $e) { echo "Insert failed: " . $e->getMessage() . " "; } // Query data try { $stmt = $pgsqlDB->query("SELECT * FROM users ORDER BY id DESC LIMIT 5"); $users = $stmt->fetchAll(PDO::FETCH_ASSOC); print_r($users); } catch (PDOException $e) { echo "Query failed: " . $e->getMessage() . " "; } // ===== 2. Cross-Database Operation: Read from MySQL and Write to PostgreSQL ===== echo " --- Cross-Database Operation --- "; try { // Assume a 'products' table exists in MySQL $mysqlStmt = $mysqlDB->query("SELECT name, price FROM products LIMIT 5"); $products = $mysqlStmt->fetchAll(PDO::FETCH_ASSOC); // Assume a similarly structured 'products' table exists in PostgreSQL $pgsqlInsertStmt = $pgsqlDB->prepare("INSERT INTO products (name, price) VALUES (:name, :price)"); foreach ($products as $product) { $pgsqlInsertStmt->execute([ ':name' => $product['name'], ':price' => $product['price'] ]); } echo "Data migration from MySQL to PostgreSQL completed successfully. "; } catch (PDOException $e) { echo "Operation failed: " . $e->getMessage() . " "; } ``` --- ## Summary of Key Differences Although PDO's API is consistent, the underlying databases' SQL dialects and features still have some differences that require attention: | Feature | MySQL | PostgreSQL | | :--- | :--- | :--- | | **DSN Prefix** | `mysql:` | `pgsql:` | | **Auto-Increment PK**| `INT AUTO_INCREMENT PRIMARY KEY` | `SERIAL PRIMARY KEY` or `BIGSERIAL` | | **Get Last Insert ID**| `$pdo->lastInsertId()` | `$pdo->lastInsertId('table_id_seq')` (Sequence name required) | | **SQL Functions** | Functions like `NOW()` | Functions like `CURRENT_TIMESTAMP`, supports powerful types like JSONB | --- ## Conclusion By using PDO, we can operate on both MySQL and PostgreSQL in a PHP project with a unified, concise API, significantly improving code maintainability and flexibility. The keys to success are establishing a good connection management system and being aware of the subtle differences in SQL syntax and features between the two databases. Master these points, and you will be able to navigate multi-database environments with confidence.