Switch to mysqli or stay with mysql?

Question

We have an app here that has been in development (and now in production) for more than a year. Which has in total over 500 mysql_* calls.

Is it worth it to switch all of the mysql_* in the code to mysqli_*

Is it worth chasing all the bugs that might (and most probably will) come about?

I see from questions like this: Changing this from MySQL to MySQLi? that just adding i after every mysql* call can lead me to alot of errors. Is it worth my time?

mysql_* will probably around for the long haul (even amongst rumors of deprecation), so it it really worth any programmers time to methodically switch over?

See also this discussion

PHP team is about to deprecate the `mysql_*` functions in future PHP releases. Read: http://news.php.net/php.internals/53799 — Buddy, Aug 26 '11 at 15:10
@Buddy, if by "about to" you mean "maybe, possibly, in several years, thinking about starting to consider to". Education through documentation is all that will happen in the short/medium term. — salathe, Aug 26 '11 at 15:25
@salathe, if by "short/medium term" you mean 1-2 years, you shouldn't use `musql_*` in new projects. Deprecation is all that will happen with this extension in PHP. — Buddy, Aug 26 '11 at 18:42
@Buddy, yes. Documentation changes only at first, maybe in the future deprecation then eventually PECLisation. For new code, or where convenient, use the more modern extensions. — salathe, Aug 26 '11 at 19:02

Michael Berkowski · Answer 1 · 2013-01-16T18:21:28.257

4

In my opinion, the benefit of MySQLi is when it is used in an object-oriented fashion, and with prepared statements. You get some additional versatility from it using the procedural style too, such as nice wrapper functions around transaction handling, but I think not enough to justify unless you rewrite lots of your code to make use of them.

And if you were to undertake the effort to convert to OO code or prepared statements, you might as well convert to the more flexible PDO instead of to MySQLi.

Update Jan 2013

Just found this old answer, and in the Aug 2011 comment thread below I said it wasn't worth it to convert mysql_query() calls to mysqli_query() absent an accompanying move to prepared statements. It now IS necessary to start moving in that direction, as the mysql_*() extension is deprecated as of PHP 5.5 and will eventually be removed.

edited Jan 16 '13 at 18:21

answered Aug 26 '11 at 15:00

Michael Berkowski

267,341
46
444
390

yes but is it worth my time to switch over an app with has over 500 `mysql_*` calls? – Naftali Aug 26 '11 at 15:02
@Neal I would say it isn't, unless you need to use MySQLi's improved wrappers around transaction commit/rollback, etc. – Michael Berkowski Aug 26 '11 at 15:03
PDO is a good choice, but in a shared server you can't use suhosin.sql.user_prefix http://www.hardened-php.net/suhosin/configuration.html#suhosin.sql.user_prefix – corretge Aug 26 '11 at 15:03
@Michael -- I am still able to do transactions with `mysql_*` at least it seems to work for me – Naftali Aug 26 '11 at 15:04
@Michael -- see this answer: http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples – Naftali Aug 26 '11 at 15:05
@Neal Transactions are tidier with MySQLi but if you are already doing them with `mysql_query("COMMIT")` there's not a good reason to switch to `mysqli_commit()`. – Michael Berkowski Aug 26 '11 at 15:05
Except parameterized statements. – dkretz Aug 26 '11 at 15:14

Gordon · Accepted Answer · 2012-11-22T23:03:50.323

Quoting the manual for ext/mysqli:

The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:

Object-oriented interface

Support for Prepared Statements

Support for Multiple Statements

Support for Transactions

Enhanced debugging capabilities

Embedded server support

Note: If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use this extension.

If you need just one of those features and can afford the refactoring, then yes, go for it. If you dont need any of those features then dont do it. There is no reason to refactor if there is no benefits.

On a sidenote, the rumors are true. ext/mysql will likely be deprecated (although no one can say when at the time of this writing. It certainly wont be deprecated with 5.4. and it will likely be available as a pecl extension forever) In any case, you shouldnt be starting any new projects with ext/mysql anymore when you have a superior extension to start with.

Also see http://blog.ulf-wendel.de/2012/php-mysql-why-to-upgrade-extmysql/

I can already do transactions (be it slowly): http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples and the code seems to be working fine — Naftali, Aug 26 '11 at 15:12
OOP support is lame, prepared statements are slow, multiple statements are injection-prone and embedded server is ridiculous. Honestly, there are no real benefits. — Your Common Sense, Aug 26 '11 at 15:13
@Gordon, **will** should be replaced with **is very likely to**. You can't say what **will** happen in PHP-land any more than we can. — salathe, Aug 26 '11 at 15:30

score 1 · Answer 3 · answered Aug 26 '11 at 15:09

MySQLi has some performance benefits over MySQL. Actually it's recommended to use MySQLi instead of MySQL. You can do the procedural style as well.

You could create a new branch of your app and change the code to mysqli_* functions. This should be pretty straight forward and while doing so you would review your database access code which might help in going on after the switch to mysqli to continue refactoring. If it's all too much hassle, you already benefit from the improved version of the client library in your database access code.

"MySQLi has some performance benefits over MySQL"? That's news, where did you get that info from? It always seems to be the other way round. — Pacerier, Oct 17 '14 at 20:02

Your Common Sense · Answer 4 · 2011-08-26T15:29:11.097

Deprecation of ext/mysql is not a rumor.

However, it is not your real problem.

Your main problem is that you're using naked API calls all over the code instead of using some intelligent library to handle SQL queries.

So, you'd better start to develop such a library or get a ready made one, and then rewrite your code to use it's calls.

Don't you see that all this repeated stuff

$res=mysql_query("SELECT STUFF");
while($row = mysql_fetch){
    $var=$row['col'];
}

being incredibly boring?
why not to use some one-liner like

$data = $db->getRow("SELECT stuff");

which is shorter and can have a lot of features like query logging, counting, debugging, error handling and such?

And, as a side effect of using such a library, the only place where you will need to change whatever API calls will be only this library code.

score 0 · Answer 5 · answered Aug 26 '11 at 15:08

0

In my opinion, You should not use mysql_* function directly in your logic. You should write a wrapper. So when you want to switch mysql_* to mysqli_*, you only have to change the code of the wrapper.

answered Aug 26 '11 at 15:08

xdazz

158,678
38
247
274

Preventing bugs depends on your test. – xdazz Aug 26 '11 at 15:16
@Neal What I means is the same as Col. Shrapnel's answer, your problem is that you're using naked API calls all over the code... – xdazz Aug 26 '11 at 15:21
so your saying to make a mysql class? – Naftali Aug 26 '11 at 15:21
@Neal Make a db access class with driver class, or something similar. – xdazz Aug 26 '11 at 15:24

score 0 · Answer 6 · answered Aug 26 '11 at 15:11

0

If you have that many calls then I suggest you start by implementing (selecting or building) an abstraction layer for your database calls and then convert that.

The biggest benefit (a substantial one) in my similar exercise was the fact that mysqli offers parameterized statements allowing you to leave mysql_real_escape_string etc. behind. But the map from one to the other isn't nearly one-to-one.

answered Aug 26 '11 at 15:11

dkretz

37,399
13
80
138

you are the second one to say to use an `abstraction layer`, how do I do that? – Naftali Aug 26 '11 at 15:13
PDO is an abstraction layer. It offers a class with methods and properties that are closer to what happens in a procedural language, and typically richer and more consistent conceptually. Or for example the one I wrote is a class from which I do everything I need with $db->SqlExecute($sql), SqlRows($sql), SqlOneRow($sql), SqlExists($sql), and SqlOneValue($sql). Just encapsulates patterns you are confortable with. My personal preference is to have a very thin filter between SQL and PHP. – dkretz Aug 26 '11 at 15:23
@Neal PDO is very lame abstraction layer. It's better to find something more reliable – Your Common Sense Aug 26 '11 at 15:30
Not recommending or judging, just giving examples. Obviously I don't use it - I think the conceptual abstractions are awkward. I don't want a third thinking level - I want to interface SQL and PHP (which are already necessary) as closely as possible without conceptual overhead. – dkretz Aug 26 '11 at 15:35

score 0 · Answer 7 · answered Aug 26 '11 at 15:40

Advice: If you're going to do a switch, do not switch to mysqli, instead switch to using PDO. If you must switch to mysqli, make sure you use it in the PHP5 OO way. Don't use mysqli OO and old mysql at the same time or the old mysql and PDO at the same time unless you like unexpected connection squashing.

Switch to mysqli or stay with mysql?

7 Answers7

Linked