Difference between mysql & mysqli

Difference between mysql & mysqli

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Difference Between mysql and mysqli

When maintaining legacy PHP applications, you will inevitably encounter code using the mysql_* functions. These are part of the original MySQL extension that was included with PHP for a long time. However, they were deprecated in PHP 5.5 and removed entirely in PHP 7.0. The modern replacement is the mysqli_* functions, which offer significant improvements over their predecessors. Understanding the technical distinctions between these two interfaces is critical for writing secure, maintainable code.

The most immediate difference lies in connectivity management. The legacy mysql_* extension was designed around a simple connection model where you established a link once and used it throughout the script's lifetime. In contrast, mysqli (MySQL Improved) supports persistent connections and multiple independent connections within a single script. This architectural shift allows for better resource management in long-running processes or web servers handling high traffic, ensuring that database handles are not held open indefinitely without purpose.

A more critical distinction is data type handling. The original mysql_* functions operated exclusively on strings. When you fetched a result, the function would return the value as a string regardless of whether the column in the database was defined as an integer, a float, or a boolean. This lack of native type safety forced developers to perform manual casting later in their logic, increasing the risk of type-related bugs. The mysqli extension introduces true data type awareness; when fetching rows via methods like fetch_object() or fetch_assoc(), it returns values with the appropriate PHP types (integers for integers, floats for decimals). This reduces the cognitive load on developers and prevents subtle errors caused by implicit type coercion.

Perhaps the most significant technical difference is support for prepared statements. While you can use placeholders in legacy mysql_* code using a function called mysql_real_escape_string() to prevent SQL injection, this approach is fundamentally flawed. Escaping individual values does not protect against complex injection vectors involving string concatenation or logical operators that bypass simple escaping rules. The mysqli extension provides a built-in prepared statement mechanism (prepare(), execute()) that separates SQL logic from data. This ensures that data sent to the query engine is always treated strictly as data, never as executable code, effectively neutralizing SQL injection attacks without any manual escaping logic.

Consider this implementation example using mysqli:

$mysqli = new mysqli("localhost", "user", "password", "database");

// Prepare a statement with placeholders
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");
$email = $_POST['email']; // User input
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();

This pattern is not only more secure but also often more performant because the database server can cache the execution plan for the query structure. The legacy mysql_* approach required constructing the entire SQL string dynamically, which prevented such optimizations and introduced security vulnerabilities that are difficult to audit.

Beyond syntax, there is a crucial difference in error handling. The mysql_* extension relied on global variables like $mysql_error to report issues, which could lead to unpredictable behavior if multiple connections were ever attempted (which they weren't supposed to be). mysqli offers both procedural and object-oriented interfaces with robust error reporting mechanisms. You can use $mysqli->error or enable exceptions via mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) to halt execution immediately upon connection failure or query error, preventing the application from continuing in a broken state.

If you are working within the Laravel ecosystem, these underlying differences are abstracted away by the Eloquent ORM and PDO bindings, but understanding them remains valuable for debugging raw queries or when interfacing with legacy systems. For instance, if you must migrate a monolithic script to a framework, rewriting raw mysql_* calls to mysqli is a mandatory step before any framework integration can occur safely.

Our network