PHP MySQL Connect

PHP can connect to MySQL using two extensions: MySQLi (MySQL Improved) and PDO (PHP Data Objects). PDO supports multiple databases and is generally preferred for new projects.

MySQLi Connection (Procedural)

MySQLi Procedural

<?php
$host   = "localhost";
$user   = "root";
$pass   = "secret";
$dbname = "myapp";

// Create connection
$conn = mysqli_connect($host, $user, $pass, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "Connected successfully";

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

MySQLi Connection (OOP)

MySQLi OOP

<?php
$host   = "localhost";
$user   = "root";
$pass   = "secret";
$dbname = "myapp";

// OOP style
$mysqli = new mysqli($host, $user, $pass, $dbname);

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

echo "Connected. Server info: " . $mysqli->server_info;

// Set charset (important for UTF-8)
$mysqli->set_charset("utf8mb4");

// Close
$mysqli->close();
?>

PDO Connection

PDO uses a DSN (Data Source Name) string and supports multiple database drivers. Always use try-catch for connection errors.

PDO Connection

<?php
$host   = "localhost";
$dbname = "myapp";
$user   = "root";
$pass   = "secret";
$charset = "utf8mb4";

$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "PDO connected successfully";
    echo "Driver: " . $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
} catch (\PDOException $e) {
    // Never expose connection details in production
    error_log($e->getMessage());
    throw new \PDOException("Database connection failed", (int)$e->getCode());
}

// PDO connection is closed by setting to null
$pdo = null;
?>

Key Takeaways

Always use PDO or MySQLi with prepared statements - never concatenate user input into SQL queries.
Prepared statements prevent SQL injection by separating SQL code from data.
PDO supports multiple database drivers - switching from MySQL to PostgreSQL requires minimal code changes.
Always close database connections when done - use unset(\) or let it go out of scope.
Use try-catch blocks to handle database errors gracefully.
Store database credentials in environment variables or a config file outside the web root - never hardcode them.

PHP MySQL Connect

MySQLi Connection (Procedural)

MySQLi Connection (OOP)

PDO Connection

Ready to Level Up Your Skills?