MySQL, MySQLi, or PDO

[csdude55]

My site was rebuilt in 2012 using MySQL. Now that I'm rebuilding everything, I'm considering the switch.

I know that MySQL has been deprecated since, what, 2009? But here we are 8 years later and it still works, so I don't know if there's any real urgency to changing.

I've read that MySQL benchmarks slightly faster than either MySQLi or PDO, so that's a +1 for sticking with MySQL.

Both MySQLi and PDO allow named parameters, which is probably better from a security standpoint, but I've never had any problems just using mysql_real_escape_string() so I don't know if I really care about that too much.

So, other than the fact that MySQL might, some day, be removed (which I suspect is unlikely; most server providers will keep it bundled as a legacy system so as to not inconvenience customers), what is the advantage to switching to MySQLi or PDO?

It looks like changing to MySQLi will be easier from a coding point of view (most everything is just adding the "i" to the command, and sometimes adding $dbh to the parameters for seemingly arbitrary reasons; eg, mysql_query($query) becomes mysqli_query($dbh, $query)). So if I'm switching to one of them from MySQL, then why would I want to use PDO over MySQLi?

[phranque]

csdude55 , thinking this thread might get more attention in the PHP forum...

[csdude55]

At this point, I went ahead and started coding the new rebuild in MySQLi, mainly because it's virtually identical to MySQL so it was an easy switch.

I do NOT understand the logic behind calling the filehandle in mysqli_real_escape_string(), though, that just makes no sense to me.

If there's an advantage to using PDO instead of MySQLi, though, I'd love to hear about it!

[Peter_S]

MySQL functions still work because you are still using PHP 5.x. but these functions have been totally removed from PHP 7.x (there is no PHP 6.x). And the PHP 5.x branch is now no longer maintained, excepting for huge security problems.

So, yes, it's better to switch to MySQLi. Imagine if tomorrow, you switch host, and end with a PHP 7.x, all your scripts won't work.

As you mentionned switching to MySQLi is easy, since it's the same functions.

I don't know (yet) if there is an advantage to use PDO over MySQLi , IF you are only using MySQL. PDO is great when you deal with different SQL database software because you don't need to change your code.The only difference is at the level of the initialization.

As to the "mysqli_real_escape_string", I assume that it's because, it's querying the MySQL server to get the escape string. Before, it was PHP which was escaping strings itself. Now, I think it's the MySQL server which is doing it. It might be safer to let MySQL escapes string itself, than PHP. For example, the PHP function might not be aware of all special characters.

[robzilla]

I assume that it's because, it's querying the MySQL server to get the escape string.

I thought the link identifier is only required because the function "tak[es] into account the current character set of the connection" mysqli_real_escape_string [php.net], and different links can have different character sets. I haven't been able to find a conclusive answer as to whether or not the MySQL server is queried in the process... which would add network latency.

[Peter_S]

Yeah, you are right robzilla ! Since I made my own PHP extension, I have the PHP source code, I should have look through it, I have no excuse for this mistake :)

[csdude55]

Ahh, that makes sense, then. I was concerned that I was making a different connection each time, which is rough since some queries have 20+ of those little mysqli_real_escape_string() commands! lol