Sql Injection | Tech Tips & Tricks

Which Is Better In PHP: PDO Or MySQLi?

Posted by blogmeister on

April 29, 2013

I am not sure if this is a matter of one’s preference but I believe from my experience PDO provides more coding flexibility that MySQLi.

Here is an example so you will better understand what I mean.

$query = 'select from tablename where id = ?';
$params = array($id);
if ($name != '') {
    $query .= ' and name = ?';
    array_push($params, $name);
}
$stmt = $db->prepare($query);
$stmt->execute($params);

$query = 'select from tablename where id = ?';

$params = array($id);

if ($name != '') {

$query .= ' and name = ?';

array_push($params, $name);

}

$stmt = $db->prepare($query);

$stmt->execute($params);

Now, if that were to be MySQLi, I would be doing it like this:

$query = 'select from tablename where id = ?';
if ($name != '') {
    $query .= ' and name = ?';
}
$stmt = $db->prepare($query);
$stmt->bind_param('s', $id);
if ($name != '') {
   $stmt->bind_param('s', $name);
}
$stmt->execute();

$query = 'select from tablename where id = ?';

if ($name != '') {

$query .= ' and name = ?';

}

$stmt = $db->prepare($query);

$stmt->bind_param('s', $id);

if ($name != '') {

$stmt->bind_param('s', $name);

}

$stmt->execute();

Notice the difference? I had to call if conditions twice. If my query is very long, then my code would also be long.

Plus, there is also the issue of mysqli_escape_string(). With PDO, you do not have to call one since it calls that function automatically when you bind values to the parameters.

When I started using prepared statements in MySQL because of the SQL injection issue, this is the main reason why I opted to use PDO rather than MySQLi.

Found this useful? Donations appreciated to help keep this blog alive.