Master PHP Database Connections: MySQLi and PDO Step‑by‑Step Guide

Connecting to a database is a fundamental task when developing web applications with PHP. This guide walks you through the process of establishing, using, and closing connections to various databases.

Establishing a Connection

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";

Using PDO

PDO (PHP Data Objects) provides a unified interface for different database types. Three common ways to create a PDO connection are shown 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 {
    // Create connection
    $conn = new PDO($dsn, $username, $password);
    // Set error mode
    $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 userId
$userId = 1;
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId); // i indicates integer
$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();