Session 5.5 – PHP-MySQL Connectivity

Introduction

Connecting PHP to MySQL databases enables dynamic web applications that can store, retrieve, and manipulate data. PHP provides two main extensions for MySQL connectivity: MySQLi and PDO.

Key Insight

A database extension is a PHP module that provides the functions and classes needed to communicate with a specific type of database server. Without one of these extensions installed and enabled in php.ini, PHP has no built-in ability to speak the MySQL wire protocol.

Both MySQLi and PDO sit on top of the same underlying C library (libmysqlclient or mysqlnd) — the difference is purely in the PHP-level API they expose.

Why Not the Old `mysql_*` Functions?

PHP 5 shipped an older extension simply called mysql (e.g. mysql_connect(), mysql_query()). That extension was deprecated in PHP 5.5 and removed entirely in PHP 7.0. Code written with it will throw a fatal error on any modern PHP version. MySQLi and PDO are its replacements.

Never Use mysql_* Functions

Functions such as mysql_connect() and mysql_query() are completely removed from PHP 7+. Any legacy code still using them must be migrated to MySQLi or PDO before deployment.

MySQLi (MySQL Improved)

MySQL-specific extension
Supports both procedural and OOP
Prepared statements support
Better performance for MySQL
More MySQL-specific features

PDO (PHP Data Objects)

Database-agnostic interface
Object-oriented only
Supports 12+ database drivers
Named and positional parameters
Better for database portability

MySQLi Extension

What Is MySQLi?

MySQLi (MySQL Improved) was introduced in PHP 5.0 as a direct replacement for the old mysql extension. The "Improved" label refers to support for prepared statements, multiple statements, transactions, and an object-oriented interface — none of which existed in the original extension.

MySQLi is MySQL-only: it cannot connect to PostgreSQL, SQLite, or any other database engine. If you know your application will always use MySQL or MariaDB and you want the fastest possible interface with the most MySQL-specific features, MySQLi is a good choice.

Procedural vs Object-Oriented Style

MySQLi uniquely supports two programming styles:

Procedural: Uses global functions like mysqli_connect(), mysqli_query(). Familiar to developers coming from the old mysql_* API.
Object-Oriented: Creates a mysqli object and calls methods on it ($mysqli->query()). Cleaner and preferred for new code.

Both styles are fully equivalent — they call the same underlying C functions. The examples below demonstrate both.

Tip: mysqlnd vs libmysqlclient

Modern PHP distributions ship with mysqlnd (MySQL Native Driver) as the default backend for both MySQLi and PDO_MySQL. mysqlnd is a PHP-native reimplementation that offers better memory handling and exposes extra statistics. You can check which backend is active with phpinfo().

Connecting to Database

<?php
// Procedural style
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'mydb';

$conn = mysqli_connect($host, $username, $password, $database);

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

echo "Connected successfully";

// Close connection
mysqli_close($conn);

// Object-oriented style
$mysqli = new mysqli($host, $username, $password, $database);

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

echo "Connected successfully";

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

Connection Parameters

The four required parameters are host, username, password, and database name. Optionally you can pass a port and socket as 5th and 6th arguments. Using 'localhost' (not '127.0.0.1') causes PHP to use a Unix domain socket on Linux, which is faster than a TCP connection for local servers.

Executing SELECT Queries

<?php
// Object-oriented style
$mysqli = new mysqli("localhost", "root", "", "mydb");

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

// Execute query
$sql = "SELECT id, name, email FROM users";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    // Fetch all rows
    while ($row = $result->fetch_assoc()) {
        echo "ID: {$row['id']}, Name: {$row['name']}, Email: {$row['email']}<br>";
    }
} else {
    echo "No results found";
}

// Free result set
$result->free();

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

Result Set Fetch Methods

Method	Returns	Use When
`fetch_assoc()`	Associative array (column name keys)	Most common — access by name
`fetch_row()`	Numeric array (index keys)	When column order is known
`fetch_array()`	Both associative + numeric	Rarely needed — wastes memory
`fetch_object()`	stdClass object	OOP-style code
`fetch_all(MYSQLI_ASSOC)`	Array of all rows	Small result sets

Always call $result->free() after you are done with a result set to release the memory held by the MySQL client library.

INSERT, UPDATE, DELETE

<?php
$mysqli = new mysqli("localhost", "root", "", "mydb");

// INSERT
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";

if ($mysqli->query($sql) === TRUE) {
    echo "New record created successfully";
    echo "Last inserted ID: " . $mysqli->insert_id;
} else {
    echo "Error: " . $mysqli->error;
}

// UPDATE
$sql = "UPDATE users SET email = 'newemail@example.com' WHERE id = 1";

if ($mysqli->query($sql) === TRUE) {
    echo "Record updated successfully";
    echo "Affected rows: " . $mysqli->affected_rows;
} else {
    echo "Error: " . $mysqli->error;
}

// DELETE
$sql = "DELETE FROM users WHERE id = 1";

if ($mysqli->query($sql) === TRUE) {
    echo "Record deleted successfully";
    echo "Affected rows: " . $mysqli->affected_rows;
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();
?>

Affected Rows vs. Insert ID

$mysqli->affected_rows returns the number of rows changed by the last INSERT, UPDATE, or DELETE. $mysqli->insert_id returns the AUTO_INCREMENT value generated by the last INSERT. Both are properties on the connection object, not on a result set.

MySQLi Prepared Statements

<?php
$mysqli = new mysqli("localhost", "root", "", "mydb");

// Prepare statement
$stmt = $mysqli->prepare("SELECT id, name, email FROM users WHERE id = ?");

// Bind parameters
$userId = 1;
$stmt->bind_param("i", $userId);  // "i" = integer

// Execute
$stmt->execute();

// Bind result variables
$stmt->bind_result($id, $name, $email);

// Fetch values
while ($stmt->fetch()) {
    echo "ID: $id, Name: $name, Email: $email<br>";
}

// Close statement
$stmt->close();

// INSERT with prepared statement
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

$name = "Jane Smith";
$email = "jane@example.com";

// "s" = string
$stmt->bind_param("ss", $name, $email);

if ($stmt->execute()) {
    echo "New record created";
}

$stmt->close();
$mysqli->close();

// Type specifiers:
// i - integer
// d - double (float)
// s - string
// b - blob
?>

PDO Extension

What Is PDO?

PDO (PHP Data Objects) is a lightweight, consistent interface for accessing any database from PHP. It was introduced in PHP 5.1 and is bundled with every standard PHP installation. The key difference from MySQLi is that PDO is database-agnostic: to switch from MySQL to PostgreSQL you only change the DSN string — the rest of your PHP code stays the same.

PDO uses drivers to talk to specific database engines. The driver for MySQL is called PDO_MySQL. Other available drivers include PDO_PGSQL (PostgreSQL), PDO_SQLITE, PDO_SQLSRV (Microsoft SQL Server), and more than a dozen others.

DSN — Data Source Name

A DSN is a specially formatted string that tells PDO which driver to use and how to find the database server. Its general format is:

driver:key=value;key=value;...

MySQL example: mysql:host=localhost;dbname=mydb;charset=utf8mb4
PostgreSQL example: pgsql:host=localhost;dbname=mydb
SQLite example: sqlite:/path/to/database.db

Always include charset=utf8mb4 in the MySQL DSN. This ensures that PHP and MySQL agree on the encoding for every query, preventing garbled multi-byte characters and certain injection vectors.

PDO Connection Options

The fourth argument to new PDO() is an array of driver options. Three settings matter most:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION — Throw a PDOException on any error instead of silently returning false. Always set this.
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC — Return rows as associative arrays by default so you don't need to specify it on every fetch().
PDO::ATTR_EMULATE_PREPARES => false — Use real (native) prepared statements in the database engine rather than a PHP-side emulation. Required for true parameterisation.

Connecting with PDO

<?php
// Connection parameters
$host = 'localhost';
$dbname = 'mydb';
$username = 'root';
$password = '';

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

// Options
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,  // Throw exceptions
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,  // Associative arrays
    PDO::ATTR_EMULATE_PREPARES => false,  // Use real prepared statements
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    echo "Connected successfully";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

Why Wrap the Connection in try/catch?

When PDO::ERRMODE_EXCEPTION is active, a failed connection throws a PDOException. Catching it lets you log the real error message privately (using error_log()) and show the user a safe, generic message. Never output $e->getMessage() directly to the browser in production — it may expose your database credentials or server details.

Executing Queries with PDO

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Simple query
    $sql = "SELECT id, name, email FROM users";
    $stmt = $pdo->query($sql);

    // Fetch all rows
    $users = $stmt->fetchAll();

    foreach ($users as $user) {
        echo "ID: {$user['id']}, Name: {$user['name']}<br>";
    }

    // Fetch one row at a time
    $stmt = $pdo->query($sql);
    while ($row = $stmt->fetch()) {
        echo "{$row['name']}<br>";
    }

    // Fetch single row
    $stmt = $pdo->query("SELECT * FROM users WHERE id = 1");
    $user = $stmt->fetch();

    // Fetch single value
    $stmt = $pdo->query("SELECT COUNT(*) FROM users");
    $count = $stmt->fetchColumn();
    echo "Total users: $count";

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

PDO Fetch Modes

Constant	Returns
`PDO::FETCH_ASSOC`	Associative array (column name keys) — recommended default
`PDO::FETCH_NUM`	Numerically indexed array
`PDO::FETCH_BOTH`	Both associative and numeric (default if not set)
`PDO::FETCH_OBJ`	Anonymous object with properties named after columns
`PDO::FETCH_CLASS`	Populates a named class instance
`PDO::FETCH_COLUMN`	Single column value per row

INSERT, UPDATE, DELETE with PDO

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // INSERT
    $sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
    $affected = $pdo->exec($sql);
    echo "Inserted $affected row(s)";
    echo "Last insert ID: " . $pdo->lastInsertId();

    // UPDATE
    $sql = "UPDATE users SET email = 'new@example.com' WHERE id = 1";
    $affected = $pdo->exec($sql);
    echo "Updated $affected row(s)";

    // DELETE
    $sql = "DELETE FROM users WHERE id = 1";
    $affected = $pdo->exec($sql);
    echo "Deleted $affected row(s)";

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

PDO Prepared Statements

How Prepared Statements Work

A prepared statement separates the SQL structure from the data values using a two-step process:

Prepare: The SQL template (with ? or :name placeholders) is sent to the database server, which parses, compiles, and optimises a query plan. No data is involved yet.
Execute: The actual parameter values are sent separately. The database server substitutes them into the pre-compiled plan — it never interprets them as SQL code.

This architectural separation is why prepared statements completely prevent SQL injection: an attacker's payload is treated as a string value, not as SQL syntax.

SQL Injection — The Risk Without Prepared Statements

Consider a login query built by string concatenation:

$sql = "SELECT * FROM users WHERE email = '$email'";

If an attacker sets $email to ' OR '1'='1, the resulting query becomes:
SELECT * FROM users WHERE email = '' OR '1'='1'
This returns every user in the table. Prepared statements make this attack impossible.

Positional vs Named Parameters

Style	Syntax	Notes
Positional	`WHERE id = ?`	Order must match `execute()` array. Cannot reuse a placeholder.
Named	`WHERE id = :id`	Order-independent. Same placeholder can appear multiple times.

Positional Parameters (?)

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SELECT with positional parameters
    $sql = "SELECT * FROM users WHERE email = ? AND status = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['john@example.com', 'active']);

    $users = $stmt->fetchAll();

    // INSERT with positional parameters
    $sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['John Doe', 'john@example.com', 30]);

    echo "User created with ID: " . $pdo->lastInsertId();

    // Multiple inserts
    $users = [
        ['Jane Smith', 'jane@example.com', 25],
        ['Bob Johnson', 'bob@example.com', 35]
    ];

    foreach ($users as $user) {
        $stmt->execute($user);
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

bindValue() vs bindParam()

Both methods bind a value to a placeholder, but they differ in timing:

bindValue() — Binds the current value of a variable at the time of the call. Safe to use in loops where the variable changes.
bindParam() — Binds a reference to the variable. The value is read only at execute() time. Useful for large BLOBs (avoids copying data). Do not use bindParam() in a loop without understanding this distinction — the placeholder will always take the last value the variable held.

Named Parameters (:name)

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SELECT with named parameters
    $sql = "SELECT * FROM users WHERE email = :email AND age > :min_age";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':email' => 'john@example.com',
        ':min_age' => 18
    ]);

    $users = $stmt->fetchAll();

    // Alternative binding
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':min_age', $minAge, PDO::PARAM_INT);

    $email = 'john@example.com';
    $minAge = 18;
    $stmt->execute();

    // INSERT with named parameters
    $sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
    $stmt = $pdo->prepare($sql);

    $data = [
        ':name' => 'John Doe',
        ':email' => 'john@example.com',
        ':age' => 30
    ];

    $stmt->execute($data);

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Transactions

ACID Properties

A transaction is a unit of work that must either complete entirely or be entirely undone. Database transactions satisfy four guarantees collectively known as ACID:

Atomicity — All operations in the transaction succeed or none of them do.
Consistency — The database transitions from one valid state to another valid state.
Isolation — Concurrent transactions do not see each other's intermediate state.
Durability — Once committed, the changes survive a system crash.

InnoDB Required for Transactions

MySQL transactions only work with the InnoDB storage engine. The older MyISAM engine does not support transactions — calls to beginTransaction() are silently ignored. Always create tables with ENGINE=InnoDB (the default in MySQL 5.5+).

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=mydb", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Begin transaction
    $pdo->beginTransaction();

    try {
        // Multiple operations
        $pdo->exec("INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com')");
        $pdo->exec("INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com')");
        $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
        $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");

        // Commit transaction
        $pdo->commit();
        echo "Transaction completed successfully";

    } catch (Exception $e) {
        // Rollback on error
        $pdo->rollBack();
        echo "Transaction failed: " . $e->getMessage();
    }

} catch (PDOException $e) {
    echo "Connection error: " . $e->getMessage();
}
?>

Error Handling

Three PDO Error Modes

PDO can handle errors in three different ways, controlled by PDO::ATTR_ERRMODE:

Mode	Constant	Behaviour
Silent	`ERRMODE_SILENT`	Returns `false` on error; you must manually call `errorInfo()`. Easy to miss errors.
Warning	`ERRMODE_WARNING`	Issues a PHP-level E_WARNING in addition to returning `false`. Useful for debugging only.
Exception	`ERRMODE_EXCEPTION`	Throws a `PDOException`. Recommended — forces you to handle errors explicitly and cleanly.

Never Show Database Errors to Users

A PDOException message often contains the SQL query, table names, and column names. Displaying it to the browser is an information-disclosure vulnerability. Use error_log($e->getMessage()) to record the details in the server log and show users only a generic message like "A database error occurred."

PDO Error Modes

<?php
// Silent mode (default) - check errors manually
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$stmt = $pdo->query("INVALID SQL");
if ($stmt === false) {
    $error = $pdo->errorInfo();
    echo "Error: " . $error[2];
}

// Warning mode - PHP warnings
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

// Exception mode (recommended) - throw exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $stmt = $pdo->query("INVALID SQL");
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
    echo "Error code: " . $e->getCode();
}
?>

Database Connection Class

<?php
// Database.php
class Database {
    private $host = 'localhost';
    private $dbname = 'mydb';
    private $username = 'root';
    private $password = '';
    private $pdo;

    public function connect() {
        if ($this->pdo === null) {
            try {
                $dsn = "mysql:host={$this->host};dbname={$this->dbname};charset=utf8mb4";
                $options = [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                    PDO::ATTR_PERSISTENT => true,  // Persistent connections
                ];

                $this->pdo = new PDO($dsn, $this->username, $this->password, $options);

            } catch (PDOException $e) {
                error_log("Database connection error: " . $e->getMessage());
                throw new Exception("Database connection failed");
            }
        }

        return $this->pdo;
    }

    public function disconnect() {
        $this->pdo = null;
    }
}

// Usage
try {
    $db = new Database();
    $pdo = $db->connect();

    $stmt = $pdo->query("SELECT * FROM users");
    $users = $stmt->fetchAll();

    foreach ($users as $user) {
        echo $user['name'] . "<br>";
    }

} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>

Best Practices

Securing Database Credentials

Credentials (hostname, username, password, database name) must never be hardcoded in PHP files that live inside the web root. If a misconfigured server serves .php files as plain text, your credentials are exposed.

Recommended approaches (in order of preference):

Environment variables — Set in the server/container environment and read with getenv('DB_PASSWORD') or $_ENV['DB_PASSWORD']. Used by cloud platforms and Docker.
Config file outside the web root — A PHP file stored one directory above public_html/ is not directly accessible via HTTP.
.env file + a library like vlucas/phpdotenv — Loads key=value pairs into $_ENV. Common in Laravel and Symfony projects. Never commit .env to version control.

Do's

Always use prepared statements
Use PDO for database portability
Enable exception error mode
Use transactions for related operations
Close connections when done
Store credentials securely (config files)
Use UTF-8 charset
Validate and sanitize input
Log database errors
Use connection pooling

Don'ts

Never concatenate user input in SQL
Don't use mysql_* functions (deprecated)
Don't hardcode credentials in code
Don't display database errors to users
Don't use root account for applications
Don't fetch all rows if not needed
Don't open multiple connections unnecessarily
Don't trust client-side validation
Don't use SELECT * in production
Don't ignore error handling

SQL Injection Prevention

<?php
// VULNERABLE - SQL Injection risk
$id = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = $id";  // DANGEROUS!

// SECURE - Using prepared statements
$id = $_GET['id'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);

// VULNERABLE
$email = $_POST['email'];
$sql = "SELECT * FROM users WHERE email = '$email'";  // DANGEROUS!

// SECURE
$email = $_POST['email'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute([':email' => $email]);
?>

Session Summary

Key Points

PHP provides MySQLi and PDO extensions for database connectivity
PDO is database-agnostic and recommended for portability
Always use prepared statements to prevent SQL injection
Configure error handling with PDO::ERRMODE_EXCEPTION
Use transactions for operations that must succeed or fail together
Named parameters (:name) are more readable than positional (?)
Store database credentials securely outside web root
Implement proper error handling and logging
Never display database errors directly to users

Next Session Preview

In the next session, we will explore CRUD Operations in PHP, implementing complete Create, Read, Update, and Delete functionality with MySQL databases.