Why are mysql_* Functions Removed and What to Do?

If your code has mysql_connect(), mysql_query() and other mysql_* functions, they will not work anymore in the latest version of PHP 7.

MySQL extension (ext/mysql) with all mysql_* functions has been deprecated in PHP 5.5 and removed in PHP 7.

How to fix it?

In PHP there are multiple ways to access database (PDO, ORMs, or mysqli for MySQL and MariaDB databases). Fixing legacy and old code is recommended and shouldn’t be very difficult and time consuming task by refactoring it to PDO_MySQL extension or mysqli.

Here is an example of writing code in the old way by using mysql_* functions:

$link = mysql_connect('localhost', 'db_user', 'db_password');
if (!$link) {
    die('Connection failed: ' . mysql_error());
}
$database = mysql_select_db('db_name', $link);

$firstName = filter_has_var(INPUT_GET, 'firstName') ? filter_input(INPUT_GET, 'firstName', FILTER_SANITIZE_STRING) : false;

$query = sprintf("SELECT username FROM friends
    WHERE first_name='%s'",
    mysql_real_escape_string($firstName));

$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result) {
    echo $row['username'];
}

Let’s refactor above into PDO - the modern and future proof way to access database. PDO’s prepared statements below take care also of SQL injections:

$pdo = new PDO('mysql:host=localhost;dbname=db_name', 'db_user', 'db_password');

$firstName = filter_has_var(INPUT_GET, 'firstName') ? filter_input(INPUT_GET, 'firstName', FILTER_SANITIZE_STRING) : false;

$params = [':firstName' => $firstName];

$sth = $pdo->prepare('
    SELECT username FROM friends
    WHERE first_name = :firstName');
$sth->bindParam(':firstName', $firstName, PDO::PARAM_STR);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
    echo $row['username'];
}

MySQL Improved Extension - MySQLi

In case of MySQL database there is also MySQLi extension available which works perfectly fine and is simple enough to use and migrate your mysql_* based code to work on PHP 7. But you will be limited to only one database (if you change your mind later and want to move to some other database for instance PostgreSQL) and also be deprived of some other functionalities in PDO. So, recommended way is to just use PDO.

For migration to mysqli you could use MySQL Converter tool but to be sure better go through the code manually. MySQLi offers two APIs - OOP and procedural.

Let’s refactor above code into mysqli procedural way and prepared statements (for avoiding SQL injection):

$link = mysqli_connect('localhost', 'db_user', 'db_password', 'db_name');

if (mysqli_connect_errno()) {
    die('Connect failed: ' . mysqli_connect_error());
}

$firstName = filter_has_var(INPUT_GET, 'firstName') ? filter_input(INPUT_GET, 'firstName', FILTER_SANITIZE_STRING) : false;

$query = "SELECT username FROM friends WHERE first_name=?";

if ($stmt = mysqli_prepare($link, $query)) {
    mysqli_stmt_bind_param($stmt, 's', $firstName);
    mysqli_stmt_execute($stmt);

    // bind result variables
    mysqli_stmt_bind_result($stmt, $username);

    // fetch values
    while (mysqli_stmt_fetch($stmt)) {
        echo $username;
    }

    // close statement
    mysqli_stmt_close($stmt);
}

Let’s refactor above code into mysqli object oriented way:

$mysqli = new mysqli('localhost', 'db_user', 'db_password', 'db_name');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

$firstName = filter_has_var(INPUT_GET, 'firstName') ? filter_input(INPUT_GET, 'firstName', FILTER_SANITIZE_STRING) : false;

$stmt = $mysqli->prepare("SELECT username FROM friends WHERE first_name=?");
$stmt->bind_param('s', $firstName);
$stmt->execute();
$stmt->bind_result($username);
while ($stmt->fetch()) {
    echo $username;
}
$stmt->close();

Why is MySQL extension deprecated?

MySQL extension has been in PHP core from the very early 2.0 version - it was over 15 years old. One of the main reasons for removal was difficult and complicated maintenance in the PHP core. MySQL extension also doesn’t provide all the latest features and benefits of the MySQL database.

See Also

GitHub OctocatFound a typo? Something wrong with this content? Just fork and edit it.

Content of this work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license. Code snippets in examples are published under the CC0 1.0 Universal (CC0 1.0). Thanks to all the contributors.