The first thing we need to look at is that MySQL is a resource and MySQLi is an object. To migrate our code, we really do not need to understand the technical difference, however we must understand that they are different.
The first thing we usually do with MySQL is to connect to and select a database, so let's take a look at mysql_connect and mysql_select_db.
$connection = mysql_connect( 'host', 'username', 'password', new_link,flags);
$database = mysql_select_db( 'database', $link);
$connection is a MySQL link identifier to the resource and $database is just a boolean variable that will contain true on success or false on failure. In most situations your host will be localhost and you will only have supplied your username and password.
mysqli_connect in PHP
Now let's take a look at its counter-part in MySQLi, mysqli_connect.
$connection = mysqli_connect( 'host', 'username', 'password', 'database', 'port', 'socket');
What Do You to Change to Use the mysqli_connect PHP function
$connection is a link to the MySQLi object for this connection. If your connection using mysql only uses the host, username and password, then updating your code is as simple as changing mysql_connect to mysqli_connect.
You could also go ahead and add the database to connect to right there in the mysqli_connect parameters and eliminate mysql_select_db. This is OK if there is no variable to store the result, however if a variable was used it is possible that there may be logic somewhere deep in the code that will be using this variable to check the valid connection to the database. In these instances I recommend using mysqli_select_db.
$database = mysqli_select_db($link, 'database');
With MySQL, you where not required to provide a link, the last opened connection was used if the link was not specified. When using MySQLi, the link is required and as you can see, it is now the first parameter.
Using our examples, this $link is connection and our database name would remain the same. $database is still a boolean variable, so if it is referenced anywhere else in the code, it will operate as expected.
In case your connection is not the simple standard one we have just gone through, we need to go back and look at mysql_connect again. The host parameter may contain a port number, localhost:3307, or a socket, localhost:/path/to/mysql.sock. When migrating these to mysqli_connect, you would simply move the port or socket to the port and socket parameters.
You may have the new_link flag set, which allowed MySQL to open a new connection instead of using the one previously opened. Then whichever link was being used would be named as the link parameter. When migrating these, we simply create a new MySQLi object with the same link name. To illustrate this...
$connection2 = mysql_connect( 'host', 'username', 'password', true);
would become
$connection2 = mysqli_connect( 'host', 'username', 'password');
You may also have client flags set, MySQLi does not use these and they can be safely removed when generating the MySQLi connection.
You may have a variation to the mysql_connect to establish a persistent connection, which is mysql_pconnect. To establish the same persistent connection in MySQLi, you simply prepend the host with a p: prefix, so localhost becomes p:localhost.
In MySQL we could use mysql_error and mysql_errno to determine if there was an error connecting. Since the MySQLi replacements for these use the link to the object and even if there was a problem connecting an object is returned, we have to use mysqli_connect_error and mysql_connect_errno.
With both of these you do not provide a link, which allows them to be used to check the last connection attempt.
OK, I did promise this would be simple and now that we have gotten a proper MySQLi connection, we have the hardest part out of the way.
MySQLi procedural methods use a parameter that references either an object link or a result object. We have seen the reference to the object link when we dealt with mysqli_select_db. The result object is similar to a MySQL result returned from a query, for example.
Many of the methods in MySQL have very similar procedural methods in MySQLi, and are as simple to migrate as adding the i to mysql and adding or moving the link or result to the first parameter. Remember that MySQLi requires the link for those methods that reference a link. In the following list, the MySQL statement is followed by the replacement MySQLi procedural method.
mysql_affected_rows -> mysqli_affected_rows($link)
mysql_close -> mysqli_close($link)
mysql_data_seek -> mysqli_data_seek( $result, $offset)
mysql_errno -> mysqli_errno( $link)
mysql_error -> mysqli_error( $link)
mysql_fetch_array -> mysqli_fetch_array( $result, $type)
mysql_fetch_assoc -> mysqli_fetch_assoc( $result)
mysql_fetch_lengths -> mysqli_fetch_lengths( $result )
mysql_fetch_object -> mysqli_fetch_object( $result, $class, $params)
mysql_fetch_row -> mysqli_fetch_row( $result)
mysql_field_seek -> mysqli_field_seek( $result, $number)
mysql_free_result -> mysqli_free_result(result)
mysql_get_client_info -> mysqli_get_client_info( $link)
mysql_get_host_info -> mysqli_get_host_info( $link)
mysql_get_proto_info -> mysqli_get_proto_info( $link)
mysql_get_server_info -> mysqli_get_server_info( $link)
mysql_info -> mysqli_info( $link)
mysql_insert_id -> mysqli_insert_id( $link)
mysql_num_rows -> mysqli_num_rows( $result)
mysql_ping -> mysqli_ping( $link)
mysql_query -> mysqli_query( $link, $query)
mysql_real_escape_string -> mysqli_real_escape_string( $link)
mysql_select_db - > mysqli_select_db( $link, $database)
mysql_set_charset -> mysqli_set_charset( $link, $charset)
mysql_stat -> mysqli_stat( $link)
mysql_thread_id -> mysqli_thread_id( $link)
The bad news, not all methods are easy to migrate as the ones listed above. The good news, these methods are not that common so you may not even have to deal with them. These more difficult methods will require some discussion, so we will go through them one at a time.
mysql_client_encoding -> mysqli_character_set_name( $link)
This is a simple name change.
mysql_create_db
This statement is replaced with the mysqli_query method using the CREATE DATABASE sql...
$result = mysqli_query( $link, 'CREATE DATABASE database_name' );
mysql_db_name
This statement is used in conjunction with the mysql_list_dbs statement to get the requested row from a given result. To migrate it to MySQLi, we have to use the mysqli_data_seek method to locate the requested row and then mysqli_fetch_row to return requested row.
$data = mysql_db_name( $result, $row);
becomes
mysqli_data_seek( $result, $row);
$fetch = mysql_fetch_row( $result );
$data = $fetch[0];
mysql_db_query
In MySQL, this statement selects a database and runs the query. To migrate it to MySQLi, we use the mysqli_select_db method to select the database and then the mysqli_query method to run the query and return the result.
$result = mysql_db_query( 'database', 'query');
becomes
mysqli_select_db( 'database' );
$result = mysqli_query( 'query' );
mysql_drop_db
This statement is replaced with the mysqli_query method using the DROP DATABASE sql...
$result = mysqli_query( $link, 'DROP DATABASE database_name');
mysql_escape_string -> mysql_real_escape_string( $link, 'string')
This is a simple name change.
mysql_fetch_field -> mysqli_fetch_field( $result )
If this statement does not contain the optional offset parameter, then it is a simple name replacement to migrate. If the offset parameter is included, then we have to loop through the result until we find the requested offset.
$fetch = mysql_fetch_field( $result, 5);
becomes
for(x=0; x<5; x++) {
mysqli_fetch_field( $result );
}
$fetch = mysqli_fetch_field( $result );mysql_field_len
mysql_field_name
mysql_field_table
In MySQL, these statements return the length, name or table of the specified field. To migrate it we use the MySQLi method mysqli_fetch_field_direct to return an object containing the field data and then return the field length, name or table from that object.
$length = mysql_field_len( $result, 5);
$name = mysql_field_name( $result, 5);
$table = mysql_field_table( $result, 5)
becomes
$fieldInfo = mysqli_fetch_field_direct( $result, 5);
$length = $fieldInfo->length;
$name = $fieldInfo->name;
$table = $fieldInfo->table;
mysql_list_dbs
This statement is replaced with the mysqli_query method using the SHOW DATABASES sql...
$result = mysqli_query( $link, 'SHOW DATABASES');
mysql_list_fields
This statement is replaced with the mysqli_query method using the SHOW COLUMNS FROM sql...
$result = mysqli_query( $link, 'SHOW COLUMNS FROM table_name' );
mysql_list_processes -> mysqli_thread_id( $link )
This is a simple name change.
mysql_list_tables
This statement is replaced with the mysqli_query method using the SHOW TABLES FROM sql...
$result = mysqli_query( $link, 'SHOW TABLES FROM database_name');
mysql_num_fields -> mysqli_field_count( $link )
This statement references the result in MySQL and is replaced with the mysql_field_count method which references the link.
mysql_result
In MySQL, this statement fetches a specified row and optional field from a given result. To migrate it we use the mysqli_data_seek to locate the row and loop through the fields using mysqli_fetch_field to return the field.
$fetch = mysql_result( $result, 3, 'field');
becomes
mysql_data_seek($result, 3);
if( !empty($field) ) {
while($finfo = mysqli_fetch_field( $result )) {
if( $field == $finfo->name ) {
$f = mysqli_fetch_assoc( $result );
$fetch = $f[ $field ];
}
}
} else {
$f = mysqli_fetch_array( $result );
$fetch = $f[0];
}
mysql_tablename
In MySQL, this statement returns the table name in the row of a specified result. To migrate it we use the mysqli_data_seek method to locate the specified row and fetch the name using the mysqli_fetch_array method.
$name = mysql_tablename( $result, 3 );
becomes
mysqli_data_seek( $result, 3 );
$f = mysql_fetch_array( $result );
$fetch = $f[0];
mysql_unbuffered_query
This statement is replaced with the mysqli_query method with the result mode set to MYSQLI_USE_RESULT...
$result = mysqli_query($link, 'query', MYSQLI_USE_RESULT);
Comments
Post a Comment