Updating MYSQL to PDO or MySQLi

[mikero]

Hello, 

I've been tasked with updating our code base from mysql to something that will work with php 7.  It's older code that was written by 2 or 3 individuals before me and it's not at all object oriented.  There are about 540 different files that I will end up having to edit to make it all up to date.  I'm not a very experienced programmer myself, and I'm in need of some guidance.  There are some files that have functions that call and use mysql queries three or four files deep and going through and editing one function leads me to having to update 4 or 5 other files which breaks stuff somewhere else down the road.  I was wondering if anyone has some suggestions on ways to make this process less painful.  The other thing is that by using PDO in a not OO style, it seems like I'm losing a lot of its benefits.  Should I consider using MYSQLi instead of PDO considering the circumstances?  Are there any resources that you could point me to in order to help me learn the skills I need to make it through this upgrade?  Thanks for the help!

[mac_gyver]

use the PDO extension, use exceptions for errors and in most cases let php catch and handle the exception, and use prepared queries when supplying external/unknown data to the sql query. this will result in the simplest and most consistent code.

if the scope of your work is limited to just getting the database code to securely work, you would write user functions that use the PDO extension internally, then search/replace the mysql_xxxx functions with the new user written pdo_xxxx functions. for the  new pdo_query() function, it would have an optional call-time array parameter for the prepared query input data. if this input parameter is empty, the function code would just call the query method. if the input parameter is not empty, the function code would first call the prepare method, then supply the array of input data when it calls the execute method. for any query that has external/unknown data being put directly into it, you would need to convert it to be a prepared query by removing the variables, any quotes around the variables, any {} around the variables, and any concatenation dots, and replace each value with just a ? place-holder. the variables that got removed would be supplied as the array call-time parameter to the pdo_query() function call. if any of the queries are being dynamically built, you will need to address changing them to be prepared queries on a case by case basis.

of concern are the number of files you have. this typically indicates that the code is not general purpose and could stand to be re-factored to use a content management system (CMS) and a data driven design. perhaps something to budget time for in the future?

[Barand]

I'll second that - use PDO.

I originally switched from MySQL_ functions to mysqli_ ones . Some time later I decided to give PDO a go and wished I'd done it years earlier. 

EDIT:

mysqli looks tempting because looks as though all you have to do is add the letter "I" to the function calls - you can't.

With mysqli, if you use the query() function you get a "result" object. If you use prepare() you get a "statement" object. The functions you use to process the query results from these two object types are different ( Confusing, you get the impression it was developed by two separate teams who didn't speak to one another).

With PDO you get the same object type with both methods and therefore only one set of processing functions to worry about. What's more, they are easier to use than the equivalent mysqli versions.

Edited October 11, 2019 by Barand

[dalecosp]

The quid pro quos of going to "MySQLI" include changing your connection string and the query syntax.  The one big "gotcha" to me when converting old to new (MySQL to MySQLI) was the lack of a mysqli_result() function in the older days, but now you can do something like this:

$data = $result->fetch_row()[0];

... which seems fairly handy to me in place of mysql_result.  I'm not sure when this syntax became valid, off the top of my head.

PDO is generally touted as great for security and portability.  I'm not totally convinced, but the use of prepared statements is preferable to its alternative.

I had written quite a bit more here, but I think at this point a good read or two would be in order.  I'm not sure there's any 'one size fits all' answer to this issue.

This is a thoughtful and well-informed take on the subject.    The Reddit comments on it are also kind of interesting.

SitePoint discussion.

Jim Westergren tested, and prefers, PDO with ATTR_EMULATE_PREPARES.

Quora seems to prefer PDO, but there's some really bad info on this page also.

I've been in your shoes, and our management wanted quick-n-dirty.  I modified everything to MySQLI with a few search/replace operations in the IDE, adapting the connection code, and replacing calls to mysqli_result().  Took very little time at all.

If they preferred PDO for security and portability, I would've done that. though.

Edited October 11, 2019 by dalecosp

[joshualjohnson]

mysqli_ is backwards compatible with mysql_. It should be a pretty easy change for you.

Edited October 15, 2019 by requinix
removing link

[Barand]

36 minutes ago, joshualjohnson said:

mysqli_ is backwards compatible with mysql_.

In what respect?