Advantages of MySQLi Over MySQL

Advantages Of MySQLi over MySQL

See the docs:

What is PHP's mysqli Extension?

The mysqli extension, or as it is
sometimes known, the MySQL improved
extension, was developed to take
advantage of new features found in
MySQL systems versions 4.1.3 and
newer. The mysqli extension is
included with PHP versions 5 and
later.

The mysqli extension has a number of
benefits, the key enhancements over
the mysql extension being:

• Object-oriented interface
• Support for Prepared Statements
• Support for Multiple Statements
• Support for Transactions
• Enhanced debugging capabilities

When should I use MySQLi instead of MySQL?

Reasons why you should use MySQLi extension instead of the MySQL extension are many:

1. MySQLi gives you prepared statements - a safer way of sending data to MySQL and protecting you from SQL injection. This alone should be enough for always choosing MySQLi over MySQL.
2. MySQLi enables most of the MySQL features.
3. MySQLi is object orientated.
4. MySQLi supports prepared statements, transactions and multiple statements.
5. The old MySQL extension is deprecated as of PHP 5.5.0

And there are other benefits. But mainly, you should focus on security and stabiltity - and MySQLi gives you just that.

MySQL vs MySQLi in PHP

You can use prepared statements with mysqli.

And there's also a function to store large (blob) data that the "old" mysql extension has not.

// php-mysql: no oo-interface
$mysqli = new mysqli('localhost', 'localonly', 'localonly');
if ($mysqli->connect_error) {
  die($mysqli->connect_error);
}

// php-mysql: no prepared statements
$stmt = $mysqli->prepare("INSERT INTO foo (mydata) VALUES (?)");
$stmt->bind_param("b", $null);

// php-mysql: no function to send data in chunks
$fp = fopen("php://input", "r");
while (!feof($fp)) {
  $chunk = fread($fp, 4096);
  $stmt->send_long_data(0, $chunk);
}
$stmt->execute();

Advantages of pdo over mysql_* or mysqli_*

It's not about supporting multiple databases with one app (although you can, with care). It's not about making it easy to change databases in the future (although it helps). It's about having one consistent, sensible interface to use regardless of database. Not only does that benefit programmers (by making their skills more broadly applicable) and projects (by making it easier for programmers to jump in), it also makes it a lot easier to create libraries that sit one layer above the data access layer. Perl has had the DBI for 20 years and it's been a decidedly good thing. PDO is a very similar concept (in fact it steals at least half of DBI's interface).

What are the advantages of sqli over sql?

I assume you are talking about the PHP MySQL extensions mysql and mysqli.

Besides the fact that the mysql extension is deprecated since PHP 5.5.x and should not be used in new projects anymore, the mysqli docs actually list the most important improvements:

The mysqli extension has a number of benefits, the key enhancements
over the mysql extension being:

• Object-oriented interface

• Support for Prepared Statements

• Support for Multiple Statements

• Support for Transactions

• Enhanced debugging capabilities

• Embedded server support

From a security perspective, Support for Prepared Statements would probably your strongest argument for the new extension. Prepared Statements are your best defense against SQL injections.

Difference between mysqli and mysql?

Use MySQLi over the older MySQL functions. The "i" stands for "improved". The list of improvements can be found in the docs.

---

Related Topics