What is MySQLi and what are the differences to MySQL?

Dynamic websites generate their content from a combination of instructions in a programming language such as PHP, Python, or Perl, which retrieve content like texts or links from a database in MySQL, MariaDB, or - depending on the content - special formats like InfluxDB or Redis. The communication takes place through interfaces between the language interpreter and a server for the database, which in turn uses a storage subsystem (engine) like InnoDB for querying and manipulating the data. The efficiency of the transmission is significantly influenced by how this exchange takes place. In the case of the scripting language PHP, which is used by most Content Management Systems (CMS), this task was long handled by the integrated extension MySQL. However, with PHP 7.0, the programmers discontinued this after more than a decade of transition - replacing it with a new function called mysqli().

What does MySQLi stand for?

Since its first version in 1994, MySQL has evolved into an unofficial, but practically binding standard for web hosting due to its functionality and availability under the GNU GPL Open Source License. The combination of a Linux server, Apache web server, MySQL database, and PHP as the processing scripting language remains the most commonly used environment for websites to this day, serving as the foundation for most CMS platforms including WordPress, Joomla, as well as open-source or proprietary e-commerce systems. Responsible for the communication between PHP and the database server was the extension of the same name.

To adapt to changing requirements, developers created a new version called MySQLi - the abbreviation stands for "improved" MySQL and was intended to announce a new generation of the PHP driver for the database server. The introduction initially took place with PHP version 5.0 for Microsoft Windows, and since version 5.3, MySQLi has been the native driver. Support for the old syntax using the mysql() command instead of mysqli() was discontinued as of PHP 7.0 - old scripts are therefore no longer directly compatible. However, this only affects the programming and by no means restricts the use of databases in MySQL or MariaDB, or engines such as InnoDB or MyISAM.

What are the differences between MySQL and MySQLi?

The transition to MySQLi was a logical response to the growing popularity and importance of MySQL and PHP on the internet. It represented a change from the previous structure in more than one way. Some of the innovations introduced by MySQLi include:

• Possibility of object-oriented programming instead of purely procedural programming
• Introduction of universal "prepared statements" marked by placeholders
• Enhanced security through protection against SQL injection attacks
• Increased efficiency through leaner code and faster processing
• Significant speed improvement for all types of accesses
• Better support for universal code snippets
• Expanded range of functions for database manipulation

Essentially, there is no sensible reason today to continue using the old version - regardless of whether it serves as the engine InnoDB, MyISAM, or another storage subsystem. This option not only operates inefficiently and slowly by modern standards, but it also has numerous well-known security vulnerabilities that have been exploited multiple times in the past, making the programming of secure environments significantly more challenging.

Do current websites exclusively use MySQLi?

Without a doubt, MySQLi is the current standard for communication between PHP and a server. All CMS, e-commerce systems, and tools maintained by their developers and intended for productive use have been exclusively using MySQLi instead of MySQL for quite some time now. The situation is different for some older versions and plugins that have not been updated for a long time. However, for the reasons mentioned above - especially security and performance - these should be promptly switched to a current system. For commercial websites, failure to do so could result not only in competitive disadvantages but also in serious and possibly legal consequences if the operator fails to fulfil their duty of care for confidential data.

Photo: Gerd Altmann on Pixabay