3Answer
MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements.
PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned.
I would recommend using PDO with prepared statements. It is a well-designed API and will let you more easily move to another database (including any that supports ODBC) if necessary.
- answered 7 years ago
- Community wiki
MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions, It has support for prepared statements.
PDO has been introduced in PHP, and the project aims to make a common API for all the databases access, so in theory you should be able to migrate between RDMS without changing any code (if you don't use specific RDBM function in your queries), also object-oriented.
- answered 7 years ago
- Community wiki
We have two choices for accessing the database in PHP : MySQLi and PDO. In this post, 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
// PDO
$pdo = new PDO("mysql:host=localhost.com;dbname=data", 'username', 'password');
// mysqli, procedural style
$mysqli = mysqli_connect('localhost.com','username','password','data');
// mysqli, object oriented style
$mysqli = new mysqli('localhost.com','username','password','data');
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.
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.
Object Mapping :
A really nice thing with PDO is you can fetch the data, injecting it automatically in an object. We have a Student class with some properties, which match field names from a database :
class Student {
public $id;
public $first_name;
public $last_name
public function getFullName() {
return $this->first_name.' '.$this->last_name
}
}
Without using object mapping, we need to fill each field’s value to use getFullName() method correctly. Object mapping allows us to predefine these properties before the object is constructed!
$query = "SELECT id, first_name, last_name FROM student";
// PDO
$result = $pdo->query($query);
$result->setFetchMode(PDO::FETCH_INTO, new Student);
while ($student = $result->fetch()) {
echo $student->getFullName()."\n";
}
// MySQLI, procedural way
if ($result = mysqli_query($mysqli, $query)) {
while ($student = mysqli_fetch_object($result, 'Student')) {
echo $student->getFullName()."\n";
}
}
// MySQLi, object oriented way
if ($result = $mysqli->query($query)) {
while ($student = $result->fetch_object('Student')) {
echo $student->getFullName()."\n";
}
}
Performance :
Both PDO and MySQLi are quite fast. For selects, MySQLi was about 2.5% faster for non-prepared statements and about 6.7% faster for prepared statements. Old MySQL extension is faster than both of these.
- answered 7 years ago
- Community wiki
Best Rated Questions
Your Answer