DRIVER SUPPORT
- backend switch in future made easy
- PDO supports upto 12 drivers like mysql,ms sql sever,informix,oracle etc..
- MYSQLi supports only mysql
NAMED PARAMETERS
- makes binding values to the query easier
example:
$params = array(':username' => 'test', ':email' => $mail);
PDO:
$pdo->prepare('
SELECT * FROM users
WHERE username = :username
AND email = :email
');
$pdo->execute($params);
MYSQLI
$query = $mysqli->prepare('
SELECT * FROM users
WHERE username = ?
AND email = ?
');
$query->bind_param('sss', 'test', $mail);
$query->execute();
PERFORMANCE
As always there are debates going deciding which one is quicker, even if PDO is bit slower we can compromise it for its rich driver support and named parameters etc...
COMMON FEATURES
prepared statements
The query only needs to be parsed ( or prepared ) once, but can be executed multiple times with the
same or different parameters,
so while using multiple inserts / selects with different params will be executed much faster.
besides that it also reduces sql injection because the query parsed and fixed,
we will supply only the data to the query by using bindParam method.
example:
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
// insert one row
$name = 'one';
$value = 1;
$stmt->execute();
// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>
stored procedures
Both api supports stored procedure
