Converting MYSQL functions to MYSQLI With PHP

Converting mysql to mysqli

The time has come and many functions like mysql_query are deprecated and will eventually become totally unusable as PHP evolves from version to version.

With this in mind, you have multiple options for coding or recoding your new and existing applications; especially where mySQL is concerned. For starters, you can use the mysqli extension which offers both an object oriented and procedural approach to doing this. Alternatively, you can use PDO.

If you plan to use the secure PDO or mysqli, you need to have the extension enabled in your PHP settings. It is good idea to have both enabled since you may need to use both of them with various web applications.

PDO

With PDO, you would need to rewrite a lot of code and the conversion would be quite time consuming. For new applications, you may just want to use it as your default means for writing queries.

You can see various select, insert, update and delete statements at:
http://lampload.com/sqlite-vs-mysql.html
http://lampload.com/connecting-to-mysql-with-pdo.html
http://lampload.com/oop-login-class-with-php.html

MYSQLI

With mysqli, it is very easy to convert from functions that use mysql; such as mysql_query(), mysql_fetch_assoc(), mysql_real_escape_string(), mysql_num_rows(). The steps below will you the slight differences to make a successful conversion.

1) Do a search and replace mysql_ with mysqli_

2) Change database connection file; if necessary. The code below will work with mysqli. It is not necessary to use the function mysqli_select_db().

function db_connect() {
    
    $host = "localhost";
    $user = "user";
    $pw = "password";
    $database = "database_name";

    $db = mysqli_connect($host, $user, $pw, $database) or die("Cannot connect to mySQL.");    

    return $db;
}

$db = db_connect();

3) Do a find and search for all mysqli_query in your code. With the mysqli_query() function you need 2 parameters; the database connection and the the query. The order is very important and opposite the mysql_query() function where the query string would be the first parameter and the optional second parameter would be the database variable.

$command = "SELECT * FROM tablename";
$result2 = mysqli_query($db, $command2);

4) Do a search for mysqli_real_escape_string. This function needs 2 parameters; unlike one that is required for mysql_real_escape_string(). The first parameter is the database variable while the second is the string.

You have had some code where this function was written within a mysql query. Now, you could filter the variable with the function outside the query. Then, you simply use the variable within the mysql query.

$password = mysqli_real_escape_string($db, $_POST['password']);

5) If you have any more trouble, you should check for errors. The errors and Google normally help solve the issues. Also, keep in mind that other lesser used functions will need the database variable parameter in order to work properly.  For example, mysql_affected_rows() will need to be mysqli_affected_rows($db).