Master PHP Database Connections: MySQL with mysqli and PDO

How to Use PHP to Connect to a Database

Connecting to a database is a fundamental task in PHP web development. This guide walks you through establishing connections using both the mysqli extension and PDO, covering procedural code, object‑oriented style, prepared statements, and proper connection closure.

Establishing a Connection with mysqli

Use the following code to create a mysqli connection and check for errors:

$servername = "localhost";
$username = "username";
$password = "password";
$database = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Successfully connected to the database";

Connecting with PDO

PDO provides a unified interface for various databases. Three common ways to create a PDO connection are demonstrated below.

Object‑oriented style

$host = "localhost";
$port = "3306"; // MySQL default port
$dbname = "dbname";
$username = "username";
$password = "password";

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

try {
    $conn = new PDO($dsn, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Successfully connected to the database";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Using PDOStatement

$host = "localhost";
$database = "dbname";
$username = "username";
$password = "password";

// Create connection
$conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);

// Prepare statement
$stmt = $conn->prepare("SELECT * FROM users");
$stmt->execute();

// Iterate results
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: " . $result['id'] . ", Name: " . $result['name'] . "<br>";
}

Short syntax

$dsn = "mysql:dbname=mydb;host=localhost";
$conn = new PDO($dsn, $username, $password);
// Example: retrieve user by ID
$userId = 1;
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
    echo "Name: " . $row['name'] . "<br>";
    echo "Email: " . $row['email'] . "<br>";
} else {
    echo "No user found";
}

Closing the Connection

After completing database operations, close the connection to free resources:

$conn->close();