MySQL Connect

PHP Connect to MySQL

Modern PHP applications interact with MySQL databases primarily using two extensions:

• MySQLi: An improved extension specifically designed for MySQL. Offers both object-oriented and procedural interfaces.
• PDO (PHP Data Objects): A consistent database abstraction layer supporting multiple database systems (including MySQL). Primarily object-oriented.

The older mysql_* extension is deprecated and should not be used in new code due to security vulnerabilities and lack of modern features.

Should I Use MySQLi or PDO?

The choice often depends on project requirements:

• PDO: Recommended if you anticipate potentially switching to another database system later (e.g., PostgreSQL, SQLite). It provides a consistent API across different databases, making migration easier. It’s strictly object-oriented.
• MySQLi: Perfectly suitable if you are certain your application will only use MySQL. Some developers prefer its slightly more direct mapping to MySQL-specific features or its optional procedural style.
• Both: Support prepared statements (essential for security), transactions, and modern MySQL features.

For most new projects, PDO is often favored for its flexibility and abstraction.

Enabling MySQLi and PDO_MySQL

Both extensions are typically included with PHP but might need to be enabled.

• Linux Systems (package managers): Often provided by a package like php-mysql (which usually installs both MySQLi and PDO_MySQL). Example: sudo apt-get install php-mysql or sudo yum install php-mysqlnd. Restart your web server/PHP-FPM.
• Windows Systems: Check your php.ini file. Uncomment or add lines like extension=mysqli and extension=pdo_mysql. Ensure extension_dir is set correctly. Restart your web server.
• Checking: Use php -m or phpinfo() to verify that mysqli and pdo_mysql are listed as enabled modules.

Refer to your environment’s documentation for specifics.

Opening a Connection to MySQL

Before interacting with the database, you must establish a connection.

Best Practice: Store database credentials securely (e.g., using environment variables or configuration files outside the web root), not directly in your code.

Example: MySQLi (Object-Oriented Style)

<?php
$servername = getenv('DB_HOST') ?: "localhost";
$username = getenv('DB_USER') ?: "your_username";
$password = getenv('DB_PASS') ?: "your_password";
$dbname = getenv('DB_NAME') ?: "myDB";

// Create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Report errors as exceptions

try {
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Set the character set - VERY IMPORTANT
    $conn->set_charset("utf8mb4");

    echo "Connected successfully using MySQLi (OO)";

} catch (mysqli_sql_exception $e) {
    // Handle connection error
    // Log error in production instead of echoing details
    error_log("MySQLi Connection Error: " . $e->getMessage());
    die("Database connection failed. Please try again later."); // User-friendly message
}

// Connection object ($conn) is now ready for queries
// ... perform queries ...

// Close connection (optional, PHP closes automatically at script end)
// $conn->close();
?>

Example: MySQLi (Procedural Style)

<?php
$servername = getenv('DB_HOST') ?: "localhost";
$username = getenv('DB_USER') ?: "your_username";
$password = getenv('DB_PASS') ?: "your_password";
$dbname = getenv('DB_NAME') ?: "myDB";

// Report errors as exceptions
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);

    // Set the character set - VERY IMPORTANT
    mysqli_set_charset($conn, "utf8mb4");

    echo "Connected successfully using MySQLi (Procedural)";

} catch (mysqli_sql_exception $e) {
    // Handle connection error
    error_log("MySQLi Connection Error: " . $e->getMessage());
    die("Database connection failed. Please try again later.");
}

// Connection resource ($conn) is now ready for queries
// ... perform queries ...

// Close connection (optional)
// mysqli_close($conn);
?>

Example: PDO

<?php
$host = getenv('DB_HOST') ?: 'localhost';
$db   = getenv('DB_NAME') ?: 'myDBPDO';
$user = getenv('DB_USER') ?: 'your_username';
$pass = getenv('DB_PASS') ?: 'your_password';
$charset = 'utf8mb4'; // Recommended charset

// Data Source Name (DSN)
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Turn on errors in the form of exceptions
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Make the default fetch be an associative array
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Disable emulation for security and performance
];

try {
     // Create PDO instance
     $pdo = new PDO($dsn, $user, $pass, $options);
     echo "Connected successfully using PDO";

} catch (PDOException $e) {
     // Handle connection error
     error_log("PDO Connection Error: " . $e->getMessage());
     // throw new PDOException($e->getMessage(), (int)$e->getCode()); // Or re-throw
     die("Database connection failed. Please try again later.");
}

// PDO object ($pdo) is now ready for queries
// ... perform queries ...

// Close connection (optional, happens automatically)
// $pdo = null;
?>

Key Points in Connection Examples:

• Error Handling: Using try...catch blocks is the modern way. MySQLi requires mysqli_report() to throw exceptions; PDO does it via the ATTR_ERRMODE option.
• Credentials: Retrieving credentials via getenv() is shown as an example of external configuration.
• Charset: Explicitly setting the charset to utf8mb4 ($conn->set_charset() or in DSN/options) is crucial for correct handling of various characters, including emoji.
• PDO Options: Setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE for convenience, and disabling PDO::ATTR_EMULATE_PREPARES are recommended practices.

Closing the Connection

In typical web request scenarios, PHP automatically closes database connections when the script finishes execution. Explicitly closing the connection is usually unnecessary.

However, in long-running scripts or resource-constrained environments, it can be good practice to close connections as soon as they are no longer needed:

• MySQLi (OO): $conn->close();
• MySQLi (Procedural): mysqli_close($conn);
• PDO: $pdo = null; (Setting the object variable to null removes the reference, allowing PHP’s garbage collector to close the connection).

The examples above show where these calls would typically be placed (usually commented out).