MySQL VS MySQLi When Using PHP

MySQL vs MySQLi when using PHP

If you have a look at MySQL Improved Extension Overview, it should tell you everything you need to know about the differences between the two.

The main useful features are:

• an Object-oriented interface
• support for prepared statements
• support for multiple statements
• support for transactions
• enhanced debugging capabilities
• embedded server support.

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.

Which is better Mysql vs Mysqli in php

Always use mysqli, the original mysql extension is deprecated as of PHP 5.5 (manual)

mysqli vs mysql-performance and usage of mysqli is complicated

If you are planning for high traffic website, then this small
difference in performance may cause problem.

Have you actually tested this out with your code? In many cases it is a negligible difference at best. So I would not worry. Typically these dire warnings of speed differences mean that something that took 1.5 seconds to complete would now take 1.6 seconds.

Besides, PDO adapters are simply the future. That is why they allow for prepared statements via ORM (object-relational mapping) or straight queries (see the example below).

But in mysqli we have to use this something very complicated query for
select/update/delete/insert etc. Is there any way I can use almost
similar code for mysqli as well?

Yes, you can. mysqli has the capabilities to allow you to use pure MySQL queries without using PDO structure:

$db = new mysqli('localhost', 'user', 'pass', 'demo');
$result = $db->query("SELECT * FROM `summary` WHERE id='35'  limit 1");
while($row = $result->fetch_assoc()){
  echo '<pre>';
  print_r($row);
  echo '</pre>';
}

Also, if you are truly worried about MySQL performance, then just run a script like this MySQL Tuning Primer script regularly to performance tune your MySQL install. That will do more to improve performance than obsessing over mysql versus mysqli in PHP.

Should I have to upgrade my website to PHP MySQLi or PDO?

The answer is fairly simple.

If, like majority of PHP users, you are going to use database API functions right in the application code, without any intermediate wrapper, then PDO is your only choice, as it's a sort of wrapper already, automating many operations that with mysqli have to be done manually.

No, there are no migration options, because the very approach is changed dramatically: instead of placing variables right in the query, they have to be substituted in the query with special marks. There is no way to automate this process.

---

Related Topics