● PDO vs MySQLi - Which I think is better. hrm

Mon Jan 23, 2023 10:52 pm
Clan Leader
Top Dog
Nuke Dev / Coder
3015 Posts
coRpSE
Currently Offline
Offline

Most Played:
This week: 77.7hrs.
Total Played: 195hrs.


  
There is life outside of the game.
Reputation: 7317.9
votes: 7
[OPINION RANT]

Okay, this is a weird one, but I got into a conversation about which I like better, PDO or MySQLi. I have been keeping off this topic for a few years because it seems to be as much of an opinion topic than anything else. But in our conversation, it got pretty good, and I think I changed him a bit, but I figured I would make this post. To start off, I look at this topic like which is better, chocolate or vanilla ice cream, there is no right answer.

MySQL is a specific type of database that is commonly used and easy to use. PDO is a tool that helps you connect to different types of databases, including MySQL, but it may be less efficient than using MySQL directly. The choice between the two depends on whether your website will only use MySQL or if it may need to work with other types of databases in the future. I find that PDO is better for more of beginners, where MySQL is more for advanced users. In speed, they are pretty much the same, but the edge out is to MySQL since PDO, by default, uses client side prepared statements' emulation, which can slow it down.

Overall, I say this, here are my top 5 reasons why MySQL is better than PDO:
  1. MySQLi is specifically designed to work with the MySQL database, which means it can offer more efficient performance when working with this specific database.

  2. MySQLi provides more flexible programming options as it supports both object-oriented and procedural styles, which makes it easier to work with the database in a way that suits your needs. - [UpDated] I put more info on what this is at the bottom of the post.

  3. MySQLi has additional features such as the ability to execute multiple statements at once, and support for server-side prepared statements, which can make your database interactions more efficient.

  4. MySQLi uses a specific library called MYSQLND which can improve performance and memory usage over the standard MySQL client library, which can help keep your application running smoothly.

  5. MySQLi has a smaller memory footprint than PDO, making it more suitable for systems with limited resources, such as small servers or shared hosting. This means that your application will be less likely to run out of memory when using MySQLi.


Now, this is why I think PDO is better thank MySQL:
  1. PDO can be used with a variety of different databases, while MySQLi is only for the MySQL database. This means that if you are working on a project that needs to connect to multiple databases, PDO would be a better choice.

  2. PDO has a consistent way of interacting with different databases, which makes it much easier to switch between them or work with multiple databases at the same time.

  3. PDO has built-in protection against SQL injection attacks, which are a common security concern when working with databases. This means that your application could be more secure when using PDO. MySQLi is just as safe if it's prepared right.

  4. PDO has automatic error handling, which means it can catch and handle errors without you having to do it manually. On the other hand, MySQLi requires manual error checking.

  5. PDO has advanced features like being able to group and commit/rollback multiple database operations at once. This allows you to make multiple changes to the database at once and either save them all or undo them all with just one command.


When it comes down to it, which do I like better, I am going to say MySQL, but that's because I go with what I know. I have used PDO a little bit over the past couple of years, but I use MySQL all the time. I am not going to say MySQL is better than ..., or PDO is better than ..., because in my mind, each have their place and you should just use what is most comfortable for you and for your project. Furthermore, I hear people on both sides arguing over which is better, though most of the arguments are valid points, the valid points on the other side always level the playing ground. If you really want to know what is better, know what your project needs and what you're most comfortable with, and go with that.

What do you guys think? I know some of you have voiced opinions on this in the past, and I like to hear your thoughts on this subject.



[UPDATE]

I was told that some of you may not know what the difference between OOP and procedural, well, here is the skinny on them. This is a simple generic sample to show you the difference.

Object-oriented style:

PHP:  [ Select all ]

$mysqli = new mysqli("host""username""password""dbname");
$result $mysqli->query("SELECT * FROM users");
while(
$row $result->fetch_assoc()) {
 
  Â echo $row['username'];
}
$result->close();
$mysqli->close(); 

 

Procedural style:

PHP:  [ Select all ]

$conn mysqli_connect("host""username""password""dbname");
$result mysqli_query($conn"SELECT * FROM users");
while(
$row mysqli_fetch_assoc($result)) {
 
  Â echo $row['username'];
}
mysqli_free_result($result);
mysqli_close($conn); 

 
The main difference is that in the OOP style, you create an object, and then use its methods to interact with the database, whereas in the procedural style, you use functions that take the connection as an argument. In both cases, the code is connecting to a database, running a query, fetching the results, iterating through them and printing out the username of each user, then releasing the memory and closing the connection.


Expand
Tue Jan 24, 2023 6:43 am
Nuke Dev / Coder
98 Posts
Lonestar
Currently Offline
Offline

Most Played:
This week: 36.9hrs.
Total Played: 706hrs.


  
Is this your job?
Reputation: 733.2
Great post, I myself prefer PDO, I think it is much more secure and has a better variety of ways to do calls to and from the database, PHP8 had originally dropped support for "mysqli" but have since added that functionailty back in,  I would really prefer Xtreme itself, to work with both if possible, or we could just go with PDO, there would really be no main core changes if we switched, as the "DB" class can be handle the same as it is now with "mysqli", switching to PDO would just allow us to add more simple ways to do database queries.


Expand
Tue Jan 24, 2023 9:11 am
Original Poster
Clan Leader
Top Dog
Nuke Dev / Coder
3015 Posts
coRpSE
Currently Offline
Offline

Most Played:
This week: 77.7hrs.
Total Played: 195hrs.


  
There is life outside of the game.
Reputation: 7317.9
votes: 7
Thanks.

What I've looked up, the issue with PHP 8 not supporting MySQLi seems to have been a case of pushing the new version out before they were done. A removal of MySQLi would have been discussed in a public forum of some wort, and we would have had some noticed of its removal. On php.net github, there was a pull request for the PHP8 changes to the documentations that at that point, had not been added, which was added when they added MySQLi back in. So it looks like it was supposed to be in, they just weren't ready for it.


 
Forums ©