We have two choices for accessing the database in Php:
MySQLi and PDO.
we will
discuss difference between PDO and
MySQLi on various feature like database support, stability and
performance to find which is the better API.
Connection Way
1.PDO
$pdo = new PDO("mysql:host=localhost.com;dbname=data", 'username', 'password');1.PDO
2.mysqli, procedural style
$mysqli = mysqli_connect('localhost.com','username','password','data');
3. mysqli,object
oriented style$mysqli = new mysqli('localhost.in','username','password','data');
1.API
Support
Both PDO and MySQLi provides an object-oriented approach, but MySQLi provides a procedural way also like old mysql extension. New users may prefer MySQLi because of procedural interface which is similar to old mysql extension, So the migrating from the old mysql extension is easier. But after mastering in PDO, you can use it with any database you like.
2.Security Both PDO and MySQLi provides an object-oriented approach, but MySQLi provides a procedural way also like old mysql extension. New users may prefer MySQLi because of procedural interface which is similar to old mysql extension, So the migrating from the old mysql extension is easier. But after mastering in PDO, you can use it with any database you like.
Both PDO and MySQLi provide SQL injection security, as long as developer follow them with features like escaping and parameter binding with prepared statements.
For example a hacker is trying to inject some malicious SQL through the ‘name’ HTTP query parameter (POST):
$_POST['name'] = "'; DELETE FROM users; /*"
If we fail to escape, it
will be added in query “as it is” , it will delete all rows from users table as
PDO and MySQLi support multiple queries.// PDO, “manual” escaping
$name = PDO::quote($_POST['name']);
$pdo->query("SELECT * FROM users WHERE name = $name");
// mysqli, “manual”
escaping$name = mysqli_real_escape_string($_POST['name']);
$mysqli->query("SELECT * FROM users WHERE name = '$name'");
PDO::quote() not only
escapes the string, but it also quotes it. But mysqli_real_escape_string() will
only escape the string, you will need to apply the quotes manually.// PDO, prepared statement
$pdo->prepare('SELECT * FROM users WHERE name = :name');
$pdo->execute(array(':name' => $_POST['name']));
// mysqli, prepared
statements$query = $mysqli->prepare('SELECT * FROM users WHERE name = ?');
$query->bind_param('s', $_POST['name']);
$query->execute();
I always recommend you to
use prepared statements with bound queries instead of PDO::quote() and
mysqli_real_escape_string().3.Database Support
The main advantage of PDO over MySQLi is its database driver support. PDO supports 12 different drivers and MySQLi supports MySQL only.
To get the list of all drivers that PDO supports, use following code:
var_dump(PDO::getAvailableDrivers());
When you require to
switch your project to use another database, in PDO you’ll have to change the
connection string and a few queries – if they use methods which are not
supported by new database. But in MySQLi, you will have to rewrite every part
of code that included queries.
Conclusion:
