Blindly Replacing mysql_ Functions with mysqli_: A Cautionary Tale

In PHP 5.5, the mysql_ functions were deprecated and have since been removed in PHP 7. This raises the question of whether one can simply replace all mysql_ functions with mysqli_ functions without encountering any adverse effects.

The answer is a resounding no.

Functional Differences

While the mysql_ and mysqli_ functions share a similar naming convention, they are not equivalent in functionality. For instance:

• Parameter order: mysqli_ functions expect the connection as the first argument, while mysql_ functions do not.
• Syntax: OO-style calls in mysqli_ require the use of -> notation (e.g., $mysqli->query()), whereas mysql_ functions use procedural style.
• Handling of special characters: mysqli_ requires escaped characters to be represented as escape sequences, unlike mysql_.
• Error reporting: mysqli_ provides more detailed error information compared to mysql_.

Recommendations

It is not advisable to blindly replace mysql_ functions with mysqli_. Instead, it is necessary to carefully update the code to use mysqli_ functions correctly. This involves:

1. Establishing a new connection: Use mysqli_connect() or mysqli::__construct() to create a connection and store it in a variable.
2. Modifying queries: Update queries to include the connection as the first argument (for procedural style) or using ->query() for OO style.
3. Adapting fetching methods: mysqli_ uses mysqli_fetch_assoc() and mysqli_result->fetch_assoc() for fetching associated arrays, respectively.
4. Closing the connection: Employ mysqli_close() or mysqli->__destruct() to close the connection.

Conversion Tool

To ease the migration process, there is a converter tool available: https://github.com/philip/MySQLConverterTool. However, it is important to note that the converted code still requires manual review and testing.

Conclusion

Replacing mysql_ functions with mysqli_ requires some effort and attention to detail. While the functions share the same function names, their internal implementations differ. By carefully updating the code and verifying its functionality, developers can ensure a smooth transition away from deprecated functions.