PDO or MySQLI

Beginners - Learning PHP
#1

Hi, as an ex Stackoverflow member I am nervous asking a simple question like this and incurring the wrath of downvoters and those that use their knowledge as a bludgeon to beat down beginners, I am hoping this site is more user friendly :slight_smile:

My question :- I understand PDO can interface with more databases, but if I am working only with MySQL / Maria databases are there any particular benefits of PDO over MySQLi with prepared statements, particularly with regards to ease of programming and security.

Many thanks

PDO vs Mysqli
#2

The PDO extension is simpler and more consistent than the mysqli extension. It takes less statements to accomplish a prepared query and you can operate on the result from prepared and non-prepared queries the same. Also, since both explicate and implicate binding is per-parameter, it is easy to dynamically form and execute queries, with simple code, whereas the mysqli variable call-time parameter binding takes some messy and slow code to dynamically and generically deal with prepared queries.

1 Like
#3

Thanks for your clear and helpful reply! So basically if I am historically a MySQL guy and need to get into PHP7, a more modern database interface and prepared statements - go straight to PDO and skip MySQLi.

#4

Yes. Using PDO, prepared queries when supplying external/unknown data to the sql query statement, and using exceptions to handle database statement errors (connection, query, prepare, and execute) will result in the simplest code and simplest sql query syntax.

1 Like
#5

Thanks again, apologies if this is beyond scope of original question, but is that sufficiently secure on its own, prepared statements with PDO or should I still look at escaping and quoting strings - thanks again

#6

The main point of prepared queries is to separate the sql query syntax from the data values, to make it impossible for the data to be operated on as sql syntax.

All the code you may have had with the old mysql_ extension to protect against sql special characters in the data from breaking the sql query syntax goes away.

Your application code just needs to validate the data to make sure it meets the needs of the application (not empty, proper format for things like email addresses, …) and using a prepared query takes care of protecting the database.

The PDO extension does have emulated prepared queries, which you should turn off when you make the database connection.

1 Like
#7

Could you explain this further and how I do it please

#8

I must say this, I prefer mysqli to pdo perhaps because I started with it and till now, I have not found any task I cannot complete easily with mysqli. It is easy for me to read and implement prepared, binding and execute statements. Also I don’t fancy too many of => Maybe mysqli has improved recently without the knowledge of pdo lovers. Just like jQuery can handle easily so many tasks of some libraries now.
Don’t be like me though. Spend time to learn and enjoy pdo and maybe you will be a pdo lover

#9 
$dbName = 'your_db_name';
$hostId = '127.0.0.1';
$dbUser = 'your_db_user_name';
$dbPass = 'your_db_user_password';
$options = array(
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
  );

  $dbh = new PDO("mysql:host=$hostId; dbname=$dbName; charset=utf8mb4", $dbUser, $dbPass, $options);

I prefer to specify driver options ($options) with database handler ($dbh).
Notice the option labeled “PDO::ATTR_EMULATE_PREPARES” in this example.
Setting this option to false will disable emulated prepared queries.

1 Like
#10

You still benefit from the option to switch the database without changing the layer - never say never - and why bind yourself? You may have to alter the statements themself, so it’s a time bonus.

Also you don’t have to learn an interface twice.

You can mockup data easily with sqlite.

At least I never had a mysql-bound project for the last years, most of them migrate to postgres now.

And I won’t have to charge my customers for this decision.

Plus you know how to handle other databases in the future, less time wasted.

1 Like
#11

Many thanks to everybody - this is a great resource!

Sponsor our Newsletter | Privacy Policy | Terms of Service