SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,035
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)

    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 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:
    • PDO
    • OOP mysqli
    • other advantages mysqli has over deprecated mysql eg. prepared statements
    • etc.

    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.

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,035
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)

    Case Study - my old code

    In my old code I found use of 12 mysql functions. Below are example code lines, mysql syntax, mysqli syntax, and my appraisal
    Code:
    $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.

  3. #3
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,035
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)

    Case Study - WordPress Core file

    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.
    Code:
    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

  4. #4
    SitePoint Addict bronze trophy
    Join Date
    Apr 2013
    Location
    Ithaca
    Posts
    351
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •