MySQL vs MySQLi in PHP: Key Differences Explained

If you have worked with older PHP code, you have probably seen functions such as mysql_connect() and mysql_query(). Try the same code on modern PHP and PHP will be less nostalgic than you are. Those functions no longer exist.

The MySQL and MySQLi names are also easy to misunderstand. MySQL is the database server. In comparisons such as “MySQL vs MySQLi,” MySQL usually refers to PHP’s old mysql_* extension, while MySQLi means the newer MySQL Improved extension.

The old MySQL extension was deprecated in PHP 5.5 and completely removed in PHP 7.0. MySQLi is supported in current PHP versions and is one of the standard ways to access MySQL from PHP.

Quick answer: MySQL vs MySQLi

For modern PHP applications, use MySQLi or PDO. Do not use the old mysql_* API.

MySQLi improves on the removed MySQL extension with features such as prepared statements, object-oriented and procedural APIs, transaction methods, stored procedure support, and better access to modern MySQL functionality.

MySQL and MySQLi are not two competing databases

This distinction is worth making before comparing their APIs.

MySQL is a relational database management system. MySQLi is a PHP extension used to communicate with a MySQL server.

The historical comparison is really between these two PHP APIs:

• mysql_*, the old MySQL extension that was removed from PHP.
• mysqli_* and the mysqli class, the improved MySQL extension used by modern PHP.

So moving from mysql_query() to mysqli_query() does not mean changing your database. Your MySQL database can remain the same. You are changing the PHP interface used to access it.

Old MySQL extension vs MySQLi syntax

The old and new APIs look similar in procedural code, which is one reason migration can seem deceptively simple. The important difference is that MySQLi uses a connection object or connection resource explicitly in most operations.

Connecting with the old MySQL extension

Legacy PHP applications may contain code like this:

<?php
$connection = mysql_connect('localhost', 'root', 'password');
mysql_select_db('company', $connection);

$result = mysql_query('SELECT id, name FROM employees', $connection);
?>

This code cannot run on PHP 7.0 or later because the mysql_* functions were removed.

Connecting with MySQLi

The procedural MySQLi equivalent is:

<?php
$connection = mysqli_connect('localhost', 'root', 'password', 'company');

if (!$connection) {
    throw new RuntimeException('Database connection failed.');
}

$result = mysqli_query(
    $connection,
    'SELECT id, name FROM employees'
);
?>

MySQLi also provides an object-oriented API:

<?php
$mysqli = new mysqli('localhost', 'root', 'password', 'company');

$result = $mysqli->query(
    'SELECT id, name FROM employees'
);
?>

Both MySQLi styles use the same extension. Choosing procedural or object-oriented syntax is mostly a code-style decision. For a new codebase, I usually prefer one style consistently rather than mixing both throughout the application.

Prepared statements are the most important difference

The old MySQL extension did not support prepared statements. Developers often built SQL by joining strings together, which made unsafe code very easy to write.

For example, this old pattern is dangerous when the value comes from a user:

<?php
$email = $_POST['email'];

$sql = "SELECT id, name FROM users WHERE email = '$email'";
$result = mysql_query($sql);
?>

Escaping functions were commonly added around values, but that approach was easy to forget or apply incorrectly.

With MySQLi, use a prepared statement and bind the value separately:

<?php
$mysqli = new mysqli('localhost', 'root', 'password', 'company');

$stmt = $mysqli->prepare(
    'SELECT id, name FROM users WHERE email = ?'
);

$email = $_POST['email'];
$stmt->bind_param('s', $email);
$stmt->execute();

$result = $stmt->get_result();
?>

The SQL structure and the value are sent separately. This is the normal MySQLi prepared statement approach when SQL contains external input.

Prepared statements are not simply a nicer replacement for manual escaping. They are one of the main reasons MySQLi is safer and easier to maintain than the removed MySQL extension.

MySQLi supports transactions directly

Transactions are another practical improvement. They let a group of database changes succeed or fail as one unit.

This matters when several queries belong to the same operation. A payment, order, inventory update, or account transfer should not be left half-complete because the third query failed.

MySQLi provides dedicated transaction methods:

<?php
$mysqli = new mysqli('localhost', 'root', 'password', 'company');

try {
    $mysqli->begin_transaction();

    $stmt = $mysqli->prepare(
        'UPDATE accounts SET balance = balance - ? WHERE id = ?'
    );

    $amount = 500.00;
    $fromAccountId = 10;

    $stmt->bind_param('di', $amount, $fromAccountId);
    $stmt->execute();

    $stmt = $mysqli->prepare(
        'UPDATE accounts SET balance = balance + ? WHERE id = ?'
    );

    $toAccountId = 20;

    $stmt->bind_param('di', $amount, $toAccountId);
    $stmt->execute();

    $mysqli->commit();
} catch (Throwable $exception) {
    $mysqli->rollback();
    throw $exception;
}
?>

The old MySQL extension could execute SQL statements such as START TRANSACTION and COMMIT, but it did not provide the dedicated transaction API available in MySQLi.

MySQLi supports both procedural and object-oriented programming

One useful feature of MySQLi is that it does not force you into one programming style.

The procedural version may feel familiar if you are maintaining older PHP code:

<?php
$connection = mysqli_connect(
    'localhost',
    'root',
    'password',
    'company'
);

$result = mysqli_query(
    $connection,
    'SELECT id, name FROM employees'
);
?>

The object-oriented version expresses the same operation like this:

<?php
$mysqli = new mysqli(
    'localhost',
    'root',
    'password',
    'company'
);

$result = $mysqli->query(
    'SELECT id, name FROM employees'
);
?>

There is no performance advantage worth choosing one over the other for normal application code. Consistency and readability are more important.

MySQLi supports more MySQL-specific features

MySQLi was designed specifically for MySQL. Because of that, it exposes features that the old extension either lacked or handled poorly.

Useful examples include prepared statements, transactions, multiple queries, stored procedures, asynchronous queries, and detailed connection information.

This MySQL-specific design is also the main difference between MySQLi and PDO. MySQLi works only with MySQL-compatible servers, while PDO provides a common interface for several database systems.

If your application is staying on MySQL, MySQLi is a straightforward choice. If database portability is an important requirement, PDO may be a better fit.

Can you replace mysql_* with mysqli_* directly?

Not always. The function names look similar, but migrating old code usually requires more than adding an i.

For example, this old code:

<?php
mysql_connect('localhost', 'root', 'password');
mysql_select_db('company');

$result = mysql_query(
    "SELECT id, name FROM users WHERE email = '$email'"
);
?>

should not be converted mechanically into another string-built query. A better migration uses a MySQLi connection and a prepared statement:

<?php
$mysqli = new mysqli(
    'localhost',
    'root',
    'password',
    'company'
);

$stmt = $mysqli->prepare(
    'SELECT id, name FROM users WHERE email = ?'
);

$stmt->bind_param('s', $email);
$stmt->execute();

$result = $stmt->get_result();
?>

When modernizing a legacy application, treat the migration as a chance to fix unsafe SQL construction instead of preserving it with newer function names.

Common problems when migrating from MySQL to MySQLi

1. mysql_* functions are undefined

If an old application produces an error such as:

Call to undefined function mysql_connect()

the application is running on PHP 7.0 or later. The old extension cannot be enabled because it was removed from PHP itself.

The fix is to migrate the database code to MySQLi or PDO.

2. The connection parameter is in the wrong position

Some MySQLi procedural functions use a different parameter order from the old MySQL API.

For example:

<?php
// Old MySQL
$result = mysql_query($sql, $connection);

// MySQLi
$result = mysqli_query($connection, $sql);
?>

This small difference can cause confusing errors when legacy code is converted quickly.

3. Mixing procedural and object-oriented syntax

MySQLi allows both styles, but mixing them without a reason makes code harder to follow.

<?php
$mysqli = new mysqli(
    'localhost',
    'root',
    'password',
    'company'
);

$result = mysqli_query(
    $mysqli,
    'SELECT id, name FROM employees'
);
?>

This works, but using one style consistently is usually clearer:

<?php
$result = $mysqli->query(
    'SELECT id, name FROM employees'
);
?>

4. Migrating the API but keeping unsafe queries

Replacing mysql_query() with mysqli_query() does not automatically make an application secure.

If external values are still concatenated into SQL strings, SQL injection remains possible. Use prepared statements for external values coming from forms, URLs, APIs, cookies, or other external sources.

Error handling is better in modern MySQLi

Older PHP database code often checked the return value of every query manually. Modern MySQLi can throw exceptions when database operations fail.

Since PHP 8.1, MySQLi uses strict error reporting by default. A failed connection or query normally throws a mysqli_sql_exception instead of quietly returning an error that is easy to miss.

<?php
try {
    $mysqli = new mysqli(
        'localhost',
        'root',
        'password',
        'company'
    );

    $result = $mysqli->query(
        'SELECT id, name FROM employees'
    );
} catch (mysqli_sql_exception $exception) {
    // Log the technical error.
    error_log($exception->getMessage());

    // Show a safe message to the user.
    echo 'Unable to load employee data.';
}
?>

This makes database failures much easier to handle consistently. In production code, log the technical error instead of displaying database details to visitors.

A useful MySQLi detail: get_result() depends on the client library

You will often see get_result() used after executing a prepared statement:

<?php
$stmt = $mysqli->prepare(
    'SELECT id, name FROM employees WHERE department_id = ?'
);

$departmentId = 5;

$stmt->bind_param('i', $departmentId);
$stmt->execute();

$result = $stmt->get_result();

while ($employee = $result->fetch_assoc()) {
    echo $employee['name'];
}
?>

mysqli_stmt::get_result() requires the MySQL Native Driver, commonly called mysqlnd. It is available in most modern PHP installations, but this can still matter when maintaining an unusual or older server setup.

If get_result() is unavailable, you can bind result columns instead:

<?php
$stmt = $mysqli->prepare(
    'SELECT id, name FROM employees WHERE department_id = ?'
);

$departmentId = 5;

$stmt->bind_param('i', $departmentId);
$stmt->execute();

$stmt->bind_result($id, $name);

while ($stmt->fetch()) {
    echo $name;
}
?>

You do not normally need to design new code around this limitation, but it is useful to know when a MySQLi example works locally and behaves differently on another server.

MySQLi vs PDO: which should you use?

If you are replacing the old MySQL extension, MySQLi is not your only modern option. PDO also supports MySQL.

If your application uses MySQL and you want direct access to MySQL-specific functionality, MySQLi is a good choice. If you want one database API that can also work with PostgreSQL, SQLite, and other supported databases, PDO is usually the better fit.

Neither choice makes unsafe SQL safe by itself. With either API, prepared statements should be the default when SQL contains external values.

Should you still use MySQLi in PHP?

Yes. MySQLi is actively supported and is a suitable choice for modern PHP applications that use MySQL.

The old mysql_* extension is a different story. It was deprecated in PHP 5.5 and removed completely in PHP 7.0. There is no reason to use it for new development.

If you find mysql_connect(), mysql_query(), or similar functions in an old application, plan to replace them rather than trying to restore the extension.

MySQL vs MySQLi FAQ

Is MySQLi faster than the old MySQL extension?

Performance is not the main reason to choose MySQLi. The important differences are modern PHP support, prepared statements, transactions, object-oriented programming, and better MySQL functionality.

Application performance usually depends much more on query design, indexes, the amount of data retrieved, and database configuration.

Is MySQLi more secure than MySQL?

MySQLi gives you better tools for writing secure database code, especially prepared statements. However, merely changing mysql_query() to mysqli_query() does not prevent SQL injection.

You still need to bind external values instead of concatenating them into SQL.

Can MySQLi connect to databases other than MySQL?

No. MySQLi is specifically designed for MySQL-compatible database servers. If you need a common PHP interface for several database systems, consider PDO instead.

Do I need to migrate old mysql_* code?

Yes, if the application needs to run on a supported modern PHP version. The original MySQL extension was removed in PHP 7.0, so its functions are unavailable on PHP 7 and PHP 8.

Should I choose MySQLi or PDO for a new project?

Both are valid choices for MySQL applications.

Choose MySQLi when the application is tied to MySQL and you want its MySQL-specific API. Choose PDO when database portability or a consistent API across different database systems is important.

Conclusion

The historical “MySQL vs MySQLi” choice is no longer much of a choice.

PHP’s old mysql_* extension is gone. MySQLi is its modern replacement for applications that use MySQL, with prepared statements, transactions, procedural and object-oriented APIs, and support for current PHP versions.

If you are maintaining legacy code, do more than rename the functions. Move user-supplied values into prepared statements and use the migration as an opportunity to clean up the database layer.

For new PHP applications, use MySQLi or PDO. Leave mysql_* where it belongs: in old tutorials and archaeological codebases.