STOP using mysql - migrating to procedural mysqli

If you haven’t already, you should migrate from PHP’s deprecated mysql to mysqli (MySQL Improved) or [URL=“http://php.net/manual/en/book.pdo.php”]PDO (PHP Data Objects) now!
Here in the SitePoint forums we still see a lot of code examples using PHP mysql functions despite the fact that they have been deprecated as of PHP version 5.5 start:rfc:mysqldeprecation
True, they may only throw E_DEPRECATED warnings for now, but at some point they will most likely become unsupported. You have been given notice, and unless you have an old version of MySQL (before version 4.1) why wait until your code breaks?

There are many strong reasons why your database code should be using PDO - Migrate From the mysql extension to PDO - perhaps the most compelling reason being that the code can work with databases other than MySQL.
But even if you only feel comfortable writing procedural code, you really should at least be using mysqli and not using deprecated mysql
Avoid the Original MySQL Extension, Part 1
Avoid the Original MySQL Extension, Part 2

The mysqli functions are in some ways a mid-way between depecated mysql and PDO, many functions can be written in either Procedural or Object Oriented style.
Many of the the mysqli functions are the same as the now deprecated mysql functions. Though there may be some differences in parameters and/or syntax and/or return values, in many cases you will probably simply need to add the “i” without needing to do any non-trivial code rewriting to use them. Check the documentation to be sure.

To keep things simple, this thread does not cover:

[LIST][]PDO
[
]OOP mysqli
[]other advantages mysqli has over deprecated mysql eg. prepared statements
[
]etc.[/LIST]
its focus is aimed at migrating deprecated procedural mysql to procedural mysqli

I’ve stopped using the mysql functions when writing new code for some time now. But just how hard is it to migrate old code?
To find out I used my text editor to find files in my site that used “mysql_” functions.

Interestingly, not only did I find instances of its use in my old code, but I discovered that current releases of both WordPress and phpMyAdmin still use deprecated mysql code. i.e.
WordPress:
wp-includes/wp-db.php
phpMyAdmin:
libraries/config/validate.lib.php
libraries/dbi/mysql.dbi.lib.php

To be fair. I won’t attempt to judge their decisions, as indeed I know WordPress has been working on this for some time - Use PDO or mysqli for MySQL queries when available
In fact, if you want to help, consider trying WP DB Driver

I won’t get into how I feel about turning off E_DEPRECATED and using @ error suppression, but I’ll say that I won’t be using either in my own code unless at some point I find that I have no other choice.

In my old code I found use of 12 mysql functions. Below are example code lines, mysql syntax, mysqli syntax, and my appraisal

$connection = mysql_connect($server,$username,$password);
	resource mysql_connect ( [string $server [, string $username [, string $password [, bool $new_link [, int $client_flags]]]]] )
	object mysqli mysqli_connect ( [string $host [, string $username [, string $passwd [, string $dbname [, int $port [, string $socket]]]]]] )
// DIFFERENT ARGUMENTS
// first 3 SAME, $new_link and $client_flags GONE, $dbname and $port and $socket NEW not used in my code

$select = mysql_select_db($database);
	bool mysql_select_db ( string $database_name [, resource $link_identifier] )
	bool mysqli_select_db ( mysqli $link, string $dbname )
// $args switched order, $link NO LONGER optional

$query_result = mysql_query($query, $connection) or die ('<p>Query Error : ' .mysql_error() . '</p>');
	resource mysql_query ( string $query [, resource $link_identifier] )
	mixed mysqli_query ( mysqli $link, string $query [, int $resultmode] )
// $args switched order, $link NO LONGER optional, new optional $resultmode not used in my code
	string mysql_error ( [resource $link_identifier] )
	string mysqli_error ( mysqli $link )
// EQUIVALENT

$numrows = mysql_num_rows($query_result);// check files for numrows typo
	int mysql_num_rows ( resource $result )
	int mysqli_num_rows ( mysqli_result $result )
// EQUIVALENT

$fields = mysql_list_fields($database, $table, $connection);
	resource mysql_list_fields ( string $database_name, string $table_name [, resource $link_identifier] )
// no mysqli_ equivalent -> use query
// SHOW COLUMNS FROM table

$columns = mysql_num_fields($query_result);
	int mysql_num_fields ( resource $result )
	int mysqli_num_fields ( mysqli_result $result )
// EQUIVALENT

if ( (mysql_result($query_result,$row,'field_name')
	string mysql_result ( resource $result, int $row [, mixed $field] )
// NO mysqli_ equivalent -> use
array mysqli_fetch_assoc ( mysqli_result $result )

All of my uses of mysql_result() were to get at field values returned from mysql_query() eg.
$query_result = mysql_query($query, $connection)
and then either explicitly specified the $row or incremented it in a loop eg.
$variable = mysql_result($query_result,$row,'field_name');

With mysqli I need to do things a bit differently to get the field value:
$query_result = mysqli_query($connection, $query)
$row = mysqli_fetch_assoc($query_result);
$variable = $row['field_name'],
or for example
while ($row = mysqli_fetch_assoc($query_result)) {
        $variable = $row['field_name'];
    }

mysql_free_result($query_result);
	bool mysql_free_result ( resource $result )
	void mysqli_free_result ( mysqli_result $result )
// EQUIVALENT

while ($row = mysql_fetch_array($query_result, MYSQL_ASSOC))
	array mysql_fetch_array ( resource $result [, int $result_type] )
	mixed mysqli_fetch_array ( mysqli_result $result [, int $resulttype] )
// EQUIVALENT

mysql_close($connection);
	bool mysql_close ( [resource $link_identifier] )
	bool mysqli_close ( mysqli $link )
// EQUIVALENT

if (mysql_affected_rows()!=1) {
	int mysql_affected_rows ( [resource $link_identifier] )
	int mysqli_affected_rows ( mysqli $link )
// EQUIVALENT

The following 7 need only an “i” to be added
mysql_error
mysql_num_rows
mysql_num_fields
mysql_free_result
mysql_fetch_array
mysql_close
mysql_affected_rows

and 2 others need to also have the arguments switch position
mysql_select_db
mysql_query

But what about the other 3?
mysql_connect
mysql_list_fields
mysql_result

mysql_connect - For my code, which used only the first 3 arguments, no problem, I would just need to add an “i”
mysql_list_fields - This function was DEPRECATED before mysql as a whole was. The documentation suggested using a query instead.
mysql_result - Probably one of the mysql functions I used the most, and one needing a little more code rewriting.

In the WordPress wp-db.php file I found use of 13 mysql functions. Below are example code lines, mysql syntax, mysqli syntax, and my appraisal.

Line 640: 			if ( function_exists( 'mysql_set_charset' ) && $this->has_cap( 'set_charset' ) ) {
Line 641: 				mysql_set_charset( $charset, $dbh );
	bool mysql_set_charset ( string $charset [, resource $link_identifier] )
	bool mysqli_set_charset ( mysqli $link, string $charset )
// $args switched order, $link NO LONGER optional

Line 646: 				mysql_query( $query, $dbh );
	resource mysql_query ( string $query [, resource $link_identifier] )
	mixed mysqli_query ( mysqli $link, string $query [, int $resultmode] )
// $args switched order, $link NO LONGER optional, new optional $resultmode not used by WP

Line 833: 		if ( !@mysql_select_db( $db, $dbh ) ) {
	bool mysql_select_db ( string $database_name [, resource $link_identifier] )
	bool mysqli_select_db ( mysqli $link, string $dbname )
// $args switched order, $link NO LONGER optional

Line 880: 			return mysql_real_escape_string( $string, $this->dbh );
	string mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier] )
	string mysqli_real_escape_string ( mysqli $link, string $escapestr )
// $args switched order, $link NO LONGER optional

Line 1022: 			$str = mysql_error( $this->dbh );
	string mysql_error ( [resource $link_identifier] )
	string mysqli_error ( mysqli $link )
// EQUIVALENT

Line 1126: 			mysql_free_result( $this->result );
	bool mysql_free_result ( resource $result )
	void mysqli_free_result ( mysqli_result $result )
// EQUIVALENT

Line 1142: 			$this->dbh = mysql_connect( $this->dbhost, $this->dbuser, $this->dbpassword, $new_link, $client_flags );
Line 1144: 			$this->dbh = @mysql_connect( $this->dbhost, $this->dbuser, $this->dbpassword, $new_link, $client_flags );
	resource mysql_connect ( [string $server [, string $username [, string $password [, bool $new_link [, int $client_flags]]]]] )
	object mysqli mysqli_connect ( [string $host [, string $username [, string $passwd [, string $dbname [, int $port [, string $socket]]]]]] )
// DIFFERENT ARGUMENTS
// first 3 SAME, $new_link and $client_flags GONE (defined as true and 0 in this file only, ?plugins/themes?), $dbname and $port and $socket NEW not used by WP

Line 1205: 		$this->result = @mysql_query( $query, $this->dbh );
	resource mysql_query ( string $query [, resource $link_identifier] )
	mixed mysqli_query ( mysqli $link, string $query [, int $resultmode] )
// $args switched order, $link NO LONGER optional, new optional $resultmode not used by WP

Line 1212: 		if ( $this->last_error = mysql_error( $this->dbh ) ) {
	string mysql_error ( [resource $link_identifier] )
	string mysqli_error ( mysqli $link )
// EQUIVALENT

Line 1224: 			$this->rows_affected = mysql_affected_rows( $this->dbh );
	int mysql_affected_rows ( [resource $link_identifier] )
	int mysqli_affected_rows ( mysqli $link )
// EQUIVALENT

Line 1227: 				$this->insert_id = mysql_insert_id($this->dbh);
	int mysql_insert_id ( [resource $link_identifier] )
	int mysqli_insert_id ( mysqli $link )
// EQUIVALENT

Line 1233: 			while ( $row = @mysql_fetch_object( $this->result ) ) {
	object mysql_fetch_object ( resource $result [, string $class_name [, array $params]] )
	object mysqli_fetch_object ( mysqli_result $result [, string $class_name [, array $params]] )
// EQUIVALENT

Line 1577: 		for ( $i = 0; $i < @mysql_num_fields( $this->result ); $i++ ) {
	int mysql_num_fields ( resource $result )
	int mysqli_num_fields ( mysqli_result $result )
// EQUIVALENT

Line 1578: 			$this->col_info[ $i ] = @mysql_fetch_field( $this->result, $i );
	object mysql_fetch_field ( resource $result [, int $field_offset] )
	object mysqli_fetch_field ( mysqli_result $result )
// MISSING optional $field_offset ARGUMENT instead need
	object mysqli_fetch_field_direct ( mysqli_result $result, int $fieldnr )
	$this->col_info[ $i ] = mysqli_fetch_field_direct($this->result, $i);
// EQUIVALENT

Line 1750: 		return preg_replace( '/[^0-9.].*/', '', mysql_get_server_info( $this->dbh ) );
	string mysql_get_server_info ( [resource $link_identifier] )
	string mysqli_get_server_info ( mysqli $link )
// EQUIVALENT

The following 7 need only an “i” to be added
mysql_error
mysql_free_result
mysql_affected_rows
mysql_insert_id
mysql_fetch_object
mysql_num_fields
mysql_get_server_info

and 4 others need to also have the arguments switch position
mysql_set_charset
mysql_query
mysql_select_db
mysql_real_escape_string

But what about the other 2?
mysql_connect - WordPress uses the optional $new_link and $client_flags. Though not used by any WordPress Core files it may be that a number of plugins and themes do. This is likely to be a reason why WordPress has not moved away from using mysql.
mysql_fetch_field - Though mysqli_fetch_field differs from mysql_fetch_field, using mysqli_fetch_field_direct here would be an equivalent

I personally believe its a better idea to tell them just to migrate to object oriented mysqli or PDO instead. The point is that, well, for most of aspiring PHP programmers this will be a transition they will have to experience anyway in future, it may seem a bit more complex atm but will actually solve a lot of problems for the future. Its just what I believe though, I understand that not everyone is capable of going through this transition smoothly but it never hurts to try.