Understanding MySQL Connection in PHP: Transitioning from `mysql_` to `mysqli_` or `PDO`

Introduction

Connecting to a MySQL database is a common task when developing web applications. In earlier versions of PHP, this was typically done using the mysql_ functions. However, as software evolves, so do programming standards and best practices. With the release of PHP 7, the mysql_ extension was deprecated and subsequently removed. This tutorial will guide you through establishing a database connection in modern PHP applications using either MySQLi or PDO.

Understanding the Transition

The Deprecation of mysql_

The mysql_ functions were designed for connecting to and interacting with MySQL databases but lacked many features required by contemporary applications, such as prepared statements, multiple statements execution, and support for newer MySQL versions. As a result, they were deprecated in PHP 5.5.0 and removed entirely in PHP 7.

The Rise of MySQLi and PDO

To address these limitations, two new extensions were introduced: MySQLi (MySQL Improved) and PDO (PHP Data Objects).

• MySQLi: Provides an object-oriented interface as well as a procedural one for interacting with MySQL databases.
• PDO: Offers a database access layer providing a uniform method of access to multiple databases.

Both APIs support prepared statements, which enhance security by preventing SQL injection attacks. They also provide improved error handling and other features that are important in modern web development.

Connecting to MySQL using MySQLi

MySQLi is specifically designed for use with MySQL databases. It can be used in both procedural and object-oriented styles.

Procedural Style Example:

// Connect to the database
$link = mysqli_connect('hostname', 'username', 'password', 'database');

if (!$link) {
    die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}

// Perform a query and fetch data
$result = mysqli_query($link, "SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysqli_fetch_assoc($result);
echo htmlentities($row['_message']);

// Close the connection
mysqli_close($link);

Object-Oriented Style Example:

// Connect to the database
$mysqli = new mysqli('hostname', 'username', 'password', 'database');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// Perform a query and fetch data
$result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);

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

Connecting to MySQL using PDO

PDO offers a database access layer that supports multiple database systems. The following example demonstrates how to connect to a MySQL database.

try {
    // Connect to the database
    $pdo = new PDO('mysql:host=hostname;dbname=database', 'username', 'password');
    
    // Set error mode
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Perform a query and fetch data
    $statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
    $row = $statement->fetch(PDO::FETCH_ASSOC);
    echo htmlentities($row['_message']);
    
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

Choosing Between MySQLi and PDO

When deciding whether to use MySQLi or PDO, consider the following:

• Database Support: If your application will only interact with MySQL databases, MySQLi may be a suitable choice. If you need support for multiple database types or plan to do so in the future, PDO is more appropriate.

• Feature Set: Both extensions provide prepared statements and other features, but if you require specific functionality (like transactions), make sure it’s supported by your chosen extension.

• Personal Preference: Some developers have a preference for either object-oriented programming or procedural style, which can influence their choice of API.

Conclusion

When connecting to MySQL databases in PHP, it is now recommended to use either MySQLi or PDO due to their robust feature sets and security benefits. These extensions allow you to write secure, efficient code that takes advantage of the latest advancements in PHP database connectivity. As you continue developing with PHP, consider upgrading your existing codebase to adopt these modern techniques.