I have searched this place and googled the internet to get good idea about what should I stick with ( I’m a one women kinda of a guy ) ever post I read is very old on this MySQLi vs PDO but most of them I saw most people leaning towards MySQLi and most of them it’s similar to MySQL with better security also feature wise there’s not much deference between MySQLi and PDO.
Also I don’t want to switch data bases I love mysql and I’m gonna stick with it. So can some one show me the light to enlightenment which one should I master PDO or MySQLi ( I did have a look at MySQLi and PDO coding looks similar but I think I understand MySQLi more) any way please help me to chose and learn one of these.
If you can be 101% sure that you’ll never, ever, ever use another server software apart from MySQL then you should be able to get away with using the mysqli extension. Which ever you finally decide upon make sure that you use prepared statements as they eliminate the risk of SQL injection attacks. PDO has the advantage of allowing the use of named parameters with prepared statements
can’t agree to that. I find PDO way more intuitive to use (esp. with Prepared Statements and error handling*) but most of all, PDO offers some neat options when fetching data (you want your result array indexed by the DB’s PK? want key-value pairs? fetch data into a user-defined class (with additional parameters)? fetch a single value? those are all no-brainers in PDO)
* - it takes an awful lot of time to find out how to get MySQLi throw exceptions (once you find out this is possible at all)
I used both and I wrote a couple of different articles on mysqli procedural and OOP style and PDO. IMO, PDO is easier to grasp than mysqli. Especially the procedural method with mysqli, it can quickly become confusing for beginners (you can see it in my article insert with mysqli and prepared statements).
Named parameters in your queries (like: $query = “SELECT * from users where username = :username”. Which is easier to read than “SELECT * FROM xyz WHERE username=?” IMO.
Client side prepared statements with PDO. Not really useful if you use only mysql, but can be handy with other DBMS.
The only thing about PDO is that it doesn’t have a procedural api… but, IMO, it’s still easier to learn for somebody that never used OOP than trying to get mysqli with its procedural style. Even if you only code in procedural style, I would style use PDO… easier to read, maintain, and it has a couple of nice features (named parameters and object mappings) that mysqli doesn’t have.
The biggest difference between mysqli and PDO on an ongoing basis is that mysqli supports only the mySQL database whereas PDO supports several different databases making your code more portable.
If you already have a site using procedural mysql calls then converting to mysqli is the quickest way to convert your existing code.
I do already have a site which uses MySQL legacy codes but I want use that learn one of these so I just want see which would be the best to invest my time in.
And most of the info on the net is very old I don’t like to make a decision based on old facts.
Both mysqli and PDO support an object oriented approach to database access.
mysqli also supports a procedural approach that is very similar to the old mysql way.
By choosing mysqli you can quickly convert your old sites to use procedural mysqli equivalents to the old code AND you can write new code using the object oriented mysqli interface.
If at a later date you want to switch to PDO the conversion from OO mysqli to PDO is not much harder than the conversion from procedural mysql to procedural mysqli
Just a quick tip: When you search on Google for PHP tutorials or whatever, you can filter the results to include the ones that are less than a year old. That way, you have less ‘old facts’… It doesn’t mean that everything will be right, but it’s a start
I agree, the age of the page/post by itself is not a good indicator of the quality of the information.
Although it’s slightly off-topic, this seems like a good point at which to mention www.phptherightway.com, which is a continually updated resource on PHP best practices. This is a good place to start when looking for up-to-date information.
With respect to OOP vs procedural, while PDO itself is OOP, using it does not require changing a procedural application to OOP. So it’s really a very minimal change. In place of calling a function to fetch results you would do something like $stmt->fetch().
In addition, the notion that PDO will allow you to change your database vendor is very misleading. There are significant differences between sql databases such as auto-increment vs sequences. It can be very complicated to move an existing application from one database to another with PDO being only one tiny piece of the puzzle. On the other hand, if you build your application from the ground up to support multiple databases then PDO works well.
As far as resources go, as you say it’s very difficult to know what to trust when searching the web. I’d suggest starting with the manual: http://us3.php.net/manual/en/book.pdo.php . You can pretty much count on it being up to date. And given that you already have a mysql application to play with, you really should not need much more.
