MySQL or MySQLi

[phppup]

I've been away from PHP and MySQL for a while and have stumbled across MySQLi

 

Is SQLi an improved version?

Do I have to use it?

Do I want to use it?

Pros and cons????

[gizmola]

MySQL was removed from PHP.

 

Your choices are either Mysqli or PDO with the MySQL driver.

 

The consensus opinion of the vast majority of our staff and experienced php developers, is that PDO is easier to work with and better designed.

 

There is a complete and highly opinionated manual for using PDO here: https://phpdelusions.net/pdo

 

If you peruse that site, you'll also find a manual for using mysqli: https://phpdelusions.net/mysqli

 

I don't remember the exact location but there are places where there's some comparison between the two, particularly in support of binding parameters with a 'WHERE IN' queries.

 

In both cases, the common practice of creating SQL strings using PHP interpolation and user input is something that you no longer will want or need to do.

[phppup]

I don't know if I have gotten so deep as to be "creating SQL strings using PHP interpolation" but I guess I'll have to investigate.

 

Meanwhile, will "old" PHP code utilizing a MySQL database still function, or are there modifications required.

[Barand]

You are that deep if you are embedding the variables inside the query EG

 

" ... WHERE id = $id"

 

The old mysql_ library is not available in PHP7.0 onwards, so once you, or your host, upgrades you will have rewrite your db code. The sooner you switch to PDO the better.

[phppup]

Thanks.  So I guess I certainly AM in that deep.

Is the switch going to be painful?

I'm sure there will be more questions as I progress.

Thanks for the help.

[gizmola]

In my opinion the switch is not necessarily painful, but it depends on the size and scope of the original code. If you have lots and lots of existing mysql_ queries all over the place, you have to go through them, and while cut and dried, it still might take a while.

 

If you perhaps have a database wrapper class, things could be a lot easier.

 

Either way, once you have the jist of the differences, it's not complicated, but perhaps time consuming. At that point it also might be work retrofitting in a database wrapper like Doctrine2, and converting everything to DBAL calls.

[phppup]

Is there a recommended location that lists the old quieries and there corresponding replacements?

[Barand]

Examples in the PHP manual at php.net might be a good place to start.

[phreak3r]

Do you think there will eventually come a time where MySQLi won't be around?

[gizmola]

No I doubt mysqli will go away. There is a lot of incentive on both sides of the relationship (oracle/mysql & php) to have an api available. Furthermore there is lots of existing code at this point using mysqli.