Introduction

If the choice is between the old mysql_* extension and MySQLi, use MySQLi. The original mysql_* extension was deprecated long ago and removed from modern PHP, while MySQLi supports prepared statements, transactions, improved error handling, and features that the old extension never had.

The Old mysql_* Extension Is Legacy and Removed

Older PHP codebases often contain calls like mysql_connect, mysql_query, and mysql_fetch_assoc. That API belongs to the old mysql_* extension.

A legacy example looks like this:

1<?php
2$link = mysql_connect('localhost', 'user', 'password');
3mysql_select_db('app_db', $link);
4$result = mysql_query('SELECT id, name FROM users', $link);

That style is no longer a viable choice for modern PHP. The extension was deprecated in PHP 5.5 and removed in PHP 7.0, so new code should not use it and old code should be migrated away from it.

MySQLi Adds the Features You Actually Need

MySQLi, which stands for MySQL Improved, supports modern MySQL features and gives you both procedural and object-oriented APIs.

A small object-oriented example:

1<?php
2$mysqli = new mysqli('localhost', 'user', 'password', 'app_db');
3
4if ($mysqli->connect_error) {
5    die('Connection failed: ' . $mysqli->connect_error);
6}
7
8$result = $mysqli->query('SELECT id, name FROM users');
9
10while ($row = $result->fetch_assoc()) {
11    echo $row['id'] . ' ' . $row['name'] . PHP_EOL;
12}

The important improvement is not just syntax. MySQLi gives you access to prepared statements, transaction support, multiple statements, and better driver integration.

Prepared Statements Are a Major Reason to Prefer MySQLi

Prepared statements are the most important practical difference for day-to-day application code because they help you avoid SQL injection when binding user input.

1<?php
2$mysqli = new mysqli('localhost', 'user', 'password', 'app_db');
3
4$stmt = $mysqli->prepare('SELECT id, name FROM users WHERE email = ?');
5$stmt->bind_param('s', $email);
6
7$email = '[email protected]';
8$stmt->execute();
9$result = $stmt->get_result();
10
11while ($row = $result->fetch_assoc()) {
12    echo $row['name'] . PHP_EOL;
13}

This is a much safer pattern than concatenating raw values into SQL strings.

Procedural or Object-Oriented Style

One advantage of MySQLi is that it supports both procedural and object-oriented coding styles. The functionality is similar, so the right choice depends on the style of the project.

Procedural style:

1<?php
2$link = mysqli_connect('localhost', 'user', 'password', 'app_db');
3$result = mysqli_query($link, 'SELECT NOW() AS current_time');
4$row = mysqli_fetch_assoc($result);
5echo $row['current_time'];

Object-oriented style:

1<?php
2$mysqli = new mysqli('localhost', 'user', 'password', 'app_db');
3$result = $mysqli->query('SELECT NOW() AS current_time');
4$row = $result->fetch_assoc();
5echo $row['current_time'];

The object-oriented style is usually easier to maintain in larger applications.

What About PDO

Although the title asks about MySQL versus MySQLi, it is worth noting that many new PHP applications choose PDO instead of MySQLi. PDO provides a database abstraction layer that works across multiple database engines.

That said, if you are already committed to MySQL and only comparing the two MySQL-specific extensions, MySQLi is the correct modern answer.

Migration Mindset for Older Code

If you inherit a legacy codebase using mysql_*, migration usually means:

1. replace connection code with MySQLi or PDO
2. replace direct query calls
3. introduce prepared statements for user input
4. test result handling and error paths carefully

This is often mechanical work, but it is worth doing because the old API is not merely unfashionable. It is obsolete.

Common Pitfalls

• Treating mysql_* as a valid modern option is incorrect because the extension was removed from supported PHP versions.
• Migrating syntax without introducing prepared statements misses one of the biggest security benefits of MySQLi.
• Mixing procedural and object-oriented MySQLi styles in the same module makes the code harder to read.
• Assuming MySQLi is a full cross-database abstraction is wrong. If database portability matters, look at PDO instead.
• Porting legacy code line by line without revisiting error handling can preserve old bugs even after the API migration.

Summary

• The old mysql_* extension is legacy and should not be used in modern PHP.
• MySQLi is the direct modern replacement when you are staying with MySQL.
• Prepared statements are a major reason to prefer MySQLi over the old API.
• MySQLi supports both procedural and object-oriented styles, though object-oriented is often cleaner.
• For new code, the real modern choice is usually MySQLi or PDO, never the removed mysql_* extension.