70
With the mysql_* entering the state deprecated, the PHP documentation recommends using PDO and Mysqli.
What do you recommend for use? PDO seems to be best suited for working with object oriented only that at the same time I saw benchmarks that show that the performance using Mysqli would have a higher performance, the performance could impact a lot if compared to the use with the PDO?
50
mysql_*);Between the two options I give preference to PDO, even if it is a little slower (between 2%-7%). In my view, the fact that PDO communicate with more BD drivers and own Prepared statements, which is of great value when it comes to security, in my opinion makes this technology more interesting.
I think the answer to the Prepared statements, valuing PDO in something that is actually a disadvantage. PDO currently only simulates the Prepared statements , as long as the mysqli has natively, which is fundamental to reuse the work of Planner within repetitive operations (which is the real reason for existing prepare). Still, the OO interface is available in Mysqli besides PDO, it is not a "PDO advantage" as placed in the list (and yet, it does not help in anything pro PHP, it is mere matter of syntax).
45
If you are concerned about performance and have no interest in portability, the best option is always mysqli.
One of the differences is that with mysqli the prepared queries are implemented on the server side of the database, while with PDO they are emulated on the client side.
This means that each time you run a PHP script, with PDO the prepared queries have to be compiled again to generate a common unprepared query and PHP spends time recompiling the query and sending the entire query to the Mysql server.
With mysqli, PHP only sends the parameters every time the same query is sent to the server when it uses the same persistent connection, even if it is answering a different HTTP request.
The possible advantage of PDO portability often ends up being utopian because most applications when opting for mysql, end up not changing ever again.
Excellent, mainly about prepared consultations and the "legend" of portability.
Add-on: Even if persistent connections are not used, Mysqli Prepared queries can be very useful in loop operations, as only the parameters will be sent to query Planner from the second execution onwards. In the PDO all Planner’s work is lost between one query and another, even in these cases.
18
It will depend on your project, the PDO difference is not as big as stopping, as an example, the Drupal which is a lord CMS uses PDO, and as an example the eclipse uses the drupal in its market place.
My recommendation: Use PDO. Unless your application is vitally dependent on speed, in this case use Mysqli
18
The great advantage of PDO compared to mysqli is that it supports multiple databases and allows the use of parameterized names in the queries prepared while the mysqli supports only the Mysql and in consultations preparads is only used ?. In the manual there is a comparison of the PDO, mysqli and mysql functionalities in this link.
Follow other links that make the comparison:
16
I decided to answer, even knowing that this answer will never have more votes and it will seem that it is not so good, because there has been a lot of false information being spread around, the answers do not detail and because of the superficiality seem opinion, though they may be placed, and there begins to be false information being spread, even if it is unintentional, leaves room for people to misinterpret.
The accepted answer is even correct in the advantages and disadvantages, but put Prepared statements as advantage of PDO seems wrong to me, at least it puts on the disadvantages, well, or it is one or the other, and it is disadvantage because it is a simulation, as was said there and this is only bad, worse still because it tries to create the illusion that has something good. The worst thing that something does not exist is to exist and not do what is expected. See comment from Bacco.
It also puts an advantage that can be misinterpreted, because it lists databases that are accessed in the PDO. No PDO can also access all these databases, and always from (almost) better way (the most used Dbs is always better), so in the form that is written this is not even an advantage.
What this item actually means is that you can make your code access one database and then switch to another. And in fact that’s the only real advantage that people cite that PDO is superficially better, the rest is firula or is false.
Even this case is kind of fake. Strictly true, you can do this, but it’s an advantage like "I have a belly button and I can play with it".
The first falsehood about this and what causes another problem is that people think they can and should switch databases easily like this.
Abstractions are good, the ideal really is that certain aspects of technology are details of implementation and that if one day needs to change should be able to do it easily. But this is one of those rules that works better on paper than in practice. With database the implementation details make a difference in the overall functioning and although the exchange can allow it to work normally (in most cases) after the exchange, it does not mean that it will be good.
The first myth about this is that people switch databases, already said in another answer. They do not do this, this exchange is extremely rare. He usually has no technical reason to make the switch. And having a difficulty less to make exchange encourages exchanges for political motivations, you do not want to facilitate a political decision tampering with your system, right?
In general all relational databases and operated correctly meet any situation well. Oracle, SQL Server, DB2, Postgresql, Mysql and even some others work well in all situations. They scale well in any scenarios, all of them are used in the biggest websites in the world without problems. For this they are tunados to give the best performance, the code is made thinking of them, it is not a generic code that makes climb well, it is the opposite, so the biggest reason that people think they may need to exchange the database one day, the PDO will bring you problems, because it already has a natural slowness (nor will I say that PHP is slow so if the problem is the performance has already chosen the wrong language), it makes you write a more generic code for a DB (or let do a specific one the same way) and if changing this code will not be suitable for the new DB and will scale even worse.
I refuse to talk about ODBC, it’s too bad and using PDO over ODBC doesn’t make sense. In the rare cases that you need to access another database in isolation then do it with its native API and not by PDO or ODBC, it only has advantages.
I’m taking the Sqlite here because it doesn’t scale well in scenarios of great competing writings, but the rest scale very well, even better, but there are scenarios that is wrong choice, in general where it’s almost certain that will have a lot of really competing writing, absolutely rare on sites that are not insanely popular, so even Sqlite is suitable in most situations and if you are having problems is your code problem or modeling used.
So even if you chose wrong, in general changing the database is not the solution, join What you already have is the solution. In general the exchange would only bring good results in cases where the person knows very well what they are doing, and the most common thing is that people with this ability do not make wrong choices, and if they did they know well that it is better to work with what they have than to make the exchange. They only fall for the change of technology to improve the scale who is naive or some very radical case (type 1 in a million). So the naive people choose the PDO. I’m sorry if you don’t like this, but the intention is to make you less naive, is to make you evolve current thinking.
Another reason people think about switching from DB is because another can do things that the current can not.
First let’s kill this fallacy, all Dbs can do everything, what is different is that some give an extra ease and some do better something in the standard way. But everyone can do the same thing, so trading doesn’t give you a new ability.
If you make the choice for a DB right away you can take advantage of these differences, if you chose another and then decided to trade you can not use only because you made the exchange. You might even start messing around with all your code. Whoo, but wasn’t PDO used to not have to mess with all the code? So whoever changes because they think they will magically take advantage of the "new resources" is very wrong, you have already had the difficulty to have chosen wrong, the exchange will not help you now, on the contrary will bring you a lot of problem that you do not see yet, will make a decision based on false assumptions.
Some people even realize that to exchange the DB can not use all resources of their initial DB, IE, it levels down the initial code to facilitate the alleged future exchange. Few people do this, but those who do usually make mistakes and level down too much at some points and do not lower as much as would be necessary at some points, not solving the problem. People are terrible at predicting what will happen in the future (most make a mistake even talking about the past :) ). And realize that the problem you are having that makes you want to change SGDB is to have leveled down which is a premature generalization.
Have you identified a paradox there? To avoid having to change DB you cannot think that one day it will be changed. If you think that one day he will be changed you will be creating a situation that he needs to be changed, then you probably got a prophecy self-fulfilling.
The neighbor’s grass always looks greener on the other side of the fence
This is one of the most important things that people who work with IT (everyone actually) should know. People never know the problems they’re going to have when they switch technology, even knowing it, because they’ve never done it in the current scenario, and in general, people underestimate those problems and overestimate the ability of that to be the solution.
The human being has an incredible inability to appreciate what he is using (even if he has flaws that need to be recognized) and always think that going elsewhere, choosing something else will solve all his problems. It’s not the same thing, but it’s a form of Duning-Kruger effect, She believes that she knows that the exchange will magically solve her problems, even if she consciously doesn’t admit this defect. This is believing in Santa Claus.
The opposite is also true, often people get into things they should exchange, or at least have chosen better at first, and next time they should make a better choice, because they think their grass solves everything (in fact they don’t want to learn anything else). But the biggest problem is that the exchange won’t bring as much benefit, or if changing will have a cost you’ll have to bear.
This is the second myth.
I’m not saying that in all situations you won’t have advantages if you make the switch, I’m just saying that the exchanges don’t occur as much as people think they do that it doesn’t pay to make your software worse (using PDO) for something that almost never actually occurs, then you create a problem because you have a 0.1% (kicked number) chance to do what you’re hoping will happen.
If there was no downside to using the PDO I would recommend it, even if it’s not as advantageous as people think it is.
It turns out that to change the database, in the rare cases that this will bring some real advantage, it will only happen if you move the code too much. It is not only the implementation detail of the database itself that will have to work. The way to do the darlings will have to be switched to be efficient and have the larger scale that is waiting, in some cases will have to change radically, you will have to tinker with your entire code base to scale.
If you are going to use a specific feature of this DB you will have to write this, you will not use these resources magically without doing something. And if you used something specific from the first DB chosen will not work in the new one, then you will be forced to tinker to work. So if you have to mess with the whole code use the simplest, most performative, most secure technology and if you have to mess with it because you’ve changed the database, you mess with it.
Someone might claim that you wouldn’t need to trade in every part of the code, just a few. This may be true in some situations, but it’s also true if you use the mysqli. Contrary to what people imagine the code used by it can be very reused if you change databases, it may just be that you will have to exchange in some different places. And to tell you the truth, you shouldn’t trust it too much. If you make the exchange, then you should reevaluate all of them darlings, And the biggest job is evaluation, not changing them.
I didn’t even mention the physical remodeling of the database. A model that works well in one RDBMS may not work well in another, so it would have to change a lot in the code if it changes the model, and if it does not change it may be that the exchange is less and efficient, defeating the purpose.
People don’t realize all this difficulty because they never needed to trade, they talk about the advantage of being able to trade without seeing how much it costs to do this and they deceive people. People are not talking about something that they know in practice, they talk about a theory that she invented or copied from another, and then everybody goes around repeating this fallacy.
If there is something wrong and an exchange can solve, it is not the exchange of the database that will solve, the problem is architecture and will have to touch many more things.
What if the solution isn’t even to continue using a relational database, a DB that isn’t on the PDO list? In the rare cases that need to change DB has a great chance that you have to change the model and PDO helps in anything, it was an innocuous attempt to generalize.
Do you realize how much has to happen to be worth using the PDO? Will everything conspire in your case? Worth the sacrifice for something so uncertain?
One of the things the person is most concerned about is choosing the right technology, among them the most appropriate language. In the latter case I usually say that it matters little in most situations, it has small nuances that will influence, but any decision will solve her problem. One of the few nuances is whether the software will scale well in performance and maintenance, and only the languages of scripts are worse (they are good for simple things and individual use) than the "native" ones, but it is a choice of philosophy and not language itself.
Of course you will not try to make an operating system in Javascript (gives, already made, but do not do) and there is no reason to make a web backend Assembly that gives, but does not make sense. Of course it will make a stored Procedure in a DB most of them require a variation of SQL or some specific language by imposition of the platform. The browser already imposed the use of JS in the frontend, but this is no longer true and in many cases it pays to use something else (although it also has disadvantages). Out of platform obligation, language itself changes little, what changes is her general philosophy that there are two large groups.
The language itself changes little if the person or team dominates it, and this is more important. It is a myth that you choose the right language for the task. Of course some are better than others for certain tasks, but change little in most cases, especially the most common cases (except the question of choosing a static or dynamic typing language, said before).
Just in case, if you think your software will scale one day it pays to choose languages that scale better both in the sense of performance and in the sense of code base management (details). A well-created static typing language (do not use poorly created languages, PHP cof cof), with good modularization, and that perform natively will give you better tools for it. So if you’re worried about possible future scale, even if you don’t need it now, forget script (some are less than script that others), the differences will be quite large, in some brutal cases. I just stress that in most cases you won’t have the full scale that you think you will, and if you’ve probably made it worth changing everything after, we’ve seen it happen all the time, and it’s a good thing, it gives you a chance to pack up a lot of stuff that you now know more about the problem.
But in general you think about changing language one day if your application is bad in it? No, right?
Choosing a proper database makes a lot more difference than a language. There are Dbs that perform much better in certain situations than others, there are some that allow more flexible models and more suitable for your scenario. Understand your problem today and how it can evolve to make the right choice, this is the secret. Choosing the PDO is admitting that you’re already making the wrong decision, so review it.
But if choosing wrong at first makes little difference in the overwhelming majority of cases and no exchange will be necessary. And again, if you have to, all you get is your winnings if you mess with everything, it’s not the PDO that’s gonna save you. And you’ll probably get a bigger do right on the DB you’re already using. PDO does not save a bad developer by making his system bad, the solution is to fix what he did wrong and never blame the proven technology.
If opting for obscure technologies can happen she is blamed for her problems, unlikely, but can, in mature technology it just doesn’t happen to be on very high scales, Uber already had to change the DB, even so showed that just because they were doing something wrong, but in the your tiny scale doesn’t matter.
Choosing right is important, but if you make a mistake, the solution is not to change the DB, and even if it is, only if you decide to rewrite all access to the database, the PDO doesn’t help you, it just deludes you into thinking it helps.
I’m being repetitive because they repeated so many times the lie that changing DB is simple and easy that you need to keep well fixed that there are too many problems in doing this.
Just remembering that exchange Mysql for Mariadb (I advise) do not need to exchange anything in the code even using mysqli, or change the model or darlings, the gain will not be great but also will not have any loss, s[o will have a software that evolves better and is "community".
Contrary to what the answer accepts, the security of the PDO is no better. The fact of creating an abstraction increases the chance of the programmer to make a mistake without realizing it. There’s nothing in it that gives any extra security points, it’s just a baseless statement.
It was clear that you should always use mysqli, right? Well, it’s always an exaggeration, if you don’t care about performance, if you want to have a product that needs to be sold marketably with the ability to exchange the database (from an engineering point of view this doesn’t make sense) then it can be a good.
As the question has php I will say that this argument is not usually worth as much to a product/platform as the Drupal quoted in another answer, are you doing something to compete with Drupal? Even if you are making a CMS for your use it is not a platform. Note that Wordpress does not allow changing the PDO and nobody stops using it, and it does well to be so, it would be much worse if allowed.
To tell you the truth I never really saw a site in PHP need to change the DB, and I will not repeat that if you need it will have a huge cost that the PDO will not help you at all. Wikipedia adopted Mysql which is what almost everyone adopts in PHP and uses it today (now uses Mariadb, but PDO was not necessary), being one of the 5 most accessed sites in the world (note that the document model could be more suitable for it - I’m speculating).
Interestingly has language that allows you to exchange database in a simpler way, without the cost of PDO or something similar (but still have the problem you should have to rewrite the darlings).
mysqli will endI’ve seen people say that. It’s actually the API called mysql which is already over and should not be used because it is bad. A mysqli entered its place, it’s official and it doesn’t make any sense that it ends, including how you think it accesses Mysql?
People keep repeating this, but it’s a recommendation from her or some nobody that she read, there’s no recommendation from any official body, a technology owner (PHP for example) or even people considered reference on the subject, It’s the typical case of a lie repeated 1000 times that people have come to believe to be true. There is the recommendation that it can be used if it is more useful to you, which is rare, and it is better than using the API mysql, but not that it is recommended in relation to mysqli.
In fact the problem is generalization, unnecessary abstraction, this applies to other technologies that behave the same, even in other languages, and applies for example to most Orms.
mysqliNot all advantages were shown in the other answers. I don’t know if the PDO has improved on anything, but some things I’m going to put here might not even be advantages of mysqli at the time of these responses:
Use the mysqli unless you’re sure it’s not just speculation, the PDO will help you. Only use the PDO if you have a very large domain of what you’re doing, when you have "proof" that it will be useful, don’t get into what people said it’s good for, they’re not worried if it’s good for you, or even for them, are only repeating what they heard, do not know the subject in fact.
To be fair, if using the PDO most of the time will change very little, it will not be so bad, but it will be worse, and why opt for something clearly worse? The point is that you will use something worse, even a little, for a gain that doesn’t exist in practice.
The answers here point to the advantage of PDO by pure formalism, not true in practice, in 99.9% of the cases (anecdotal number). This answer was necessary because everywhere people speak truths and let the imagination of readers complete with false conclusions (almost 100% of people do this because they never question what they are reading).
Don’t you agree? Okay, make valid arguments, not fallacies.
@Cool, Wikipedia uses Mariadb (migration started in 2013).. I do not think that changes at all the context of the answer, I just thought it pertinent to leave as extra information. : D
It helps to show that Mariadb should be everyone’s preference :D
Browser other questions tagged php pdo mysqli
You are not signed in. Login or sign up in order to post.
If I were to give a short answer: "Depends on DB". If it’s Mysql, mysqli, otherwise, maybe PDO, if the chosen DB doesn’t have good native support. More details on response with technical grounds linked here
– Bacco