As far as I know, there is mysql , mysqli , etc. ... I used to use mysql , but that was about 5 years ago. Now the situation has changed significantly, and many articles recommend using mysqli .

Dear PHP programming experts, help with experience, why is mysqli so good, or maybe you should use something else?

    3 answers 3

    The mysql extension is officially deprecated. This means that there are no guarantees of its further support (including from the point of view of security). Therefore, this extension can not be used in any new project.

    There remains the choice between mysqli and PDO .

    mysqli extension

    mysqli is the easiest replacement for mysql . Most of the functions and methods of mysqli have a syntax similar to the syntax of the mysql extension. This allows you to simply switch from one extension to another. For example:

     // mysql $link = mysql_connect(); $res = mysql_query('SELECT * FROM tbl', $link); var_dump(mysql_fetch_assoc($res)); // mysqli $link = mysqli_connect(); $res = mysqli_query($link, 'SELECT * FROM tbl'); var_dump(mysqli_fetch_assoc($res));

    At the same time, there are a number of improvements related to security (placeholders) and the object approach. The obvious minus mysqli - binding code to work with MySQL. In some cases, this may make it difficult to switch to using other databases (if this is of course necessary).

    PDO extension

    PDO is an additional level of abstraction above the database. Theoretically, the same PHP code can work with any SQL compatible database, if there is a corresponding PDO driver for it. (In practice, the problem with different databases still remains due to differences in SQL syntax.) PDO preaches an object approach, therefore, the code will differ significantly from code using mysql . For example:

     // mysql $link = mysql_connect('localhost', 'user', 'pass'); mysql_select_db('testdb', $link); $res = mysql_query('SELECT * FROM tbl', $link); var_dump(mysql_fetch_assoc($res)); // PDO $dbh = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass'); $stm = $dbh->prepare('SELECT * FROM tbl'); $stm->execute(); var_dump($stm->fetch(PDO::FETCH_ASSOC));

    Among other things, PDO provides a set of additional features related to security (placeholders) and the speed of the query (prepared requests). Although these features are not mysql , some of them are implemented in mysqli .

    I summarize : in most cases, I would recommend using PDO , since the interface for working with mysqli too low-level and often requires creating your own level of abstraction over the database.

    • At least you comment, for what you are minus :) - Dmitriy Simushev
    • I did not minus ... Give an example of using PDO, as mysql and mysqli made a difference. It is interesting to see. - GrayHoax
    • @GrayHoax, added sample code using PDO - Dmitriy Simushev

    The choice is very simple.

    If you understand the need to use an additional level of abstraction over low-level database access functions, and are able to write one, then you should use mysqli.

    Otherwise, PDO will be the only correct choice.

    In any case, the head of the error will be if you change the extension you will keep the old approach by adding variables to the query directly. The only reason to move to new extensions is to use prepared expressions. Otherwise, there is no sense to move - guano-codit can be continued on the old extension.

    In light of the above, do not be deceived by the apparent ease of switching to mysqli. Working with prepared expressions does not have any analogue in mysql, and, therefore, it does not provide any "simplicity" during the transition.

    • You are wrong to say that " The only reason for moving to new extensions is to use prepared expressions ." There is another reason - the reliability of the code. Tomorrow the PHP community will refuse to fix holes in mysql tomorrow, and then what? In emergency mode go to mysqli ? - Dmitriy Simushev
    • You probably forgot that mysqli has mysqli_query , which can be used in the same way as mysql_query : $res = mysqli_query($link, 'SELECT ...'); mysqli_fetch_assoc($res); $res = mysqli_query($link, 'SELECT ...'); mysqli_fetch_assoc($res); . It works without prepared expressions . Although this path is not ideologically correct, but with the help of it you can convert the mysql code into mysqli with minimal effort. - Dmitriy Simushev

    If you have the whole project implemented on the classes, I would recommend to look towards Doctrine 2 if not then PDO.

    It is believed that mysqli is a low-level extension that should be used to write libraries.

    • The "classiness" of the project does not imply the use of ORM in it. - Dmitriy Simushev
    • I agree. But the question is not about this but the existing extensions to work with the database. There is a Doctrine extension about which no one has written above. If the author needs answers to the questions in which cases it is better to choose an extension, I suppose he will create a separate question. - Vadim Bondarenko
    • You be careful with expressions. Firstly, Doctrine is not a PHP extension (like mysql, mysqli, PDO) but a separate library. Secondly, Doctrine (if you mean Doctrine DBAL) is built on top of PDO. If we are talking about Doctrine ORM, then it is built on top of Doctrine DBAL. - Dmitriy Simushev
    • And then, the author has already asked the question which extension (not the library) is better to choose. - Dmitriy Simushev