How do I convert a script using mysql_ functions to use mysqli_ functions?

mysql, mysqli, pdo, php

Solution

Note: Converting from `mysql_` to `mysqli_` may not be optimal. Consider PDO if you're prepared to convert all of your code to OOP.

It can be tempting to try to replace all instances of `mysql_` with `mysqli_` and pray it works. You'd be close but not quite on point.

Connecting to the database:

Fortunately, `mysqli_connect` works closely enough to `mysql_query` that you can just swap out their function names.

mysql_:

$con = mysql_connect($host, $username, $password);

mysqli_:

$con = mysqli_connect($host, $username, $password);

Selecting a database

Now, with most of the other functions in the `mysqli_` library, you'll need to pass `mysqli_select_db` the database connection as its first parameter. Most of the `mysqli_` functions require the connection object first.

For this function, you can just switch the order of the arguments you pass to the function. If you didn't pass it a connection object before, you have to add it as the first parameter now.

mysql_:

mysql_select_db($dbname, $con);

mysqli_:

mysqli_select_db($con, $dbname);

As a bonus, you can also pass the database name as the fourth parameter to `mysqli_connect` - bypassing the need to call `mysqli_select_db`.

$con = mysqli_connect($host, $username, $password, $dbname);

Sanitize user input

Using `mysqli_real_escape_string` is very similar to `mysql_real_escape_string`. You just need to pass the connection object as the first parameter.

mysql_:

$value1 = mysql_real_escape_string($input_string);

mysqli_:

$value1 = mysqli_real_escape_string($con, $input_string);

Very Important: Preparing and Running a Query

One reason the `mysql_` functions were deprecated to begin with was their inability to handle prepared statements. If you simply convert your code to `mysqli_` without taking this important step, you are subject to some of the largest weaknesses of the `mysql_` functions.

It's worth reading these articles on prepared statements and their benefits:

Wikipedia - Prepared Statements

PHP.net - MySQLi Prepared Statements

Note: When using prepared statements, it's best to explicitly list each column you're attempting to query, rather than using the `*` notation to query all columns. This way you can ensure you've accounted for all of the columns in your call to `mysqli_stmt_bind_result`.

mysql_:

$query = 'SELECT * FROM table1 WHERE table1.col1=' . $value1 . '';
$result = mysql_query($query, $con);
while($row = mysql_fetch_assoc*$result)
{
    $col1 = $row['col1'];
    $col2 = $row['col2'];

    echo $col1 . ' ' . $col2 . '<br />';
}

mysqli_:

$query = 'SELECT col1,col2 FROM table1 WHERE table1.col1=?';
if ($stmt = mysqli_prepare($link, $query)) {

    /* pass parameters to query */
    mysqli_stmt_bind_param($stmt, "s", $value1);

    /* run the query on the database */
    mysqli_stmt_execute($stmt);

    /* assign variable for each column to store results in */
    mysqli_stmt_bind_result($stmt, $col1, $col2);

    /* fetch values */
    while (mysqli_stmt_fetch($stmt)) {
        /*
            on each fetch, the values for each column 
            in the results are automatically stored in 
            the variables we assigned using 
            "mysqli_stmt_bind_result"
        */
        echo $col1 . ' ' . $col2 . '<br />';
    }

    /* close statement */
    mysqli_stmt_close($stmt);
}

Showing errors

Showing errors works a little differently with `mysqli_`. `mysqli_error` requires the connection object as its first parameter. But what if the connection failed? `mysqli_` introduces a small set of functions that don't require the connection object: the `mysqli_connect_*` functions.

mysql_:

if (!$con) {
    die('Could not connect: ' . mysql_error());
}

if (!$result) {
    die('SQL Error: ' . mysql_error());
}

mysqli_:

/* check connection error*/
if (mysqli_connect_errno()) {
    die( 'Could not connect: ' . mysqli_connect_error() );
}

/* check query error */
if ($stmt = mysqli_prepare($link, $query)) {

    // ... execute query

    if (mysqli_stmt_error($stmt)) {
        echo 'SQL Error: ' . mysqli_stmt_error($stmt);
    }
}

Problem

Original source