[TUTORIAL] Converting from MySQL to MySQLi
Results 1 to 2 of 2

Thread: [TUTORIAL] Converting from MySQL to MySQLi

  1. #1
    Junior Member
    Join Date
    Sep 2004
    Location
    Venice, Italy
    Posts
    12

    [TUTORIAL] Converting from MySQL to MySQLi

    Hello everyone.
    I am posting this in case someone was looking for the same issue.

    As many other developers, i am now switching my apps from the legacy mysql PHP extension to Mysqli.
    It seems that many developers are having troubles in doing it quickly and well.
    One of the main issues i read about is is that mysqli has no equivalents for the mysql "mysql_result" function.

    Evenif that function was deprecated in mysql too, it is so widely used in any case (i used that too), and so i had to find a way to rewrite as less code as possible (my apps are generally big!)

    This is what i did up to now to convert my app from MySQL to MySQLi: i'm still testing it, but seems to work fine.


    NOTE: as this solution is intended for medium/big apps, i assume you have a config.php or a function.php file that's included in all your scripts (generally, the file where you issue the mysql_connect command)

    First, let's define your own function to manage what you need from mysql.
    In my case, i was using in my app:
    - mysql_query
    - mysql_fetch_assoc
    - mysql_result
    - mysql_num_rows
    - mysql_data_seek
    - mysql_free_result
    - mysql_close

    In my editor, i performed a mass replace to be able to use my own functions.
    For example, i replced all "mysql_query" with "mysqltng_query".
    So, at this point, all my code is using these functions:
    - mysqltng_query
    - mysqltng_fetch_assoc
    - mysqltng_result
    - mysqltng_num_rows
    - mysqltng_data_seek
    - mysqltng_free_result
    - mysqltng_close

    Next step is to define those function into the config or function file that's included in all your pages.
    I decided for MySQLi's Object oriented style here.

    So, here's what you should place on top of that file:

    PHP Code:
    $DB_host 'mysql_host'// mysql host
    $DB_login "mysql_username"// mysql login
    $DB_password "mysql_password"// mysql password
    $DB_database "mysql_database"// the database which can be used by the script.

    $conn = new mysqli($DB_host,$DB_login,$DB_password,$DB_database);
    if (
    mysqli_connect_errno()) {
        
    printf("Can't connect to MySQL Server. Errorcode: %s\n"mysqli_connect_error());
        exit;
    }

    function 
    mysqltng_query($query,$connection='') {
        global 
    $conn;
        if (
    $connection==''$connection=$conn;
        return 
    $connection->query($query);
    }

    function 
    mysqltng_fetch_assoc($result) {
        return 
    $result->fetch_assoc();
    }

    function 
    mysqltng_result($result$pos$field) {
        
    $i=0;
        
    $retval='';
        while (
    $row $result->fetch_array(MYSQLI_BOTH)) {
            if (
    $i==$pos$retval=$row[$field];
            
    $i++;
        }
        return 
    $retval;
    }

    function 
    mysqltng_num_rows($result) {
        return 
    $result->num_rows;
    }

    function 
    mysqltng_data_seek($result,$offset) {
        
    $result->data_seek($offset);
    }

    function 
    mysqltng_free_result($result) {
        
    $result->free;
    }

    function 
    mysqltng_close($connection='') {
        global 
    $conn;
        if (
    $connection==''$connection=$conn;
        
    $connection->close;

    At the end, all is really simple:

    mysqltng_query and mysqltng_close allows you to eventually use another connection link (i added this becasue i used multiple links, sometimes).

    mysqltng_result, by keeping the exact params of original mysql_result, just tries to return a single row as expected.
    Naturally this piece of code may not solve all of your conversion issues, it just depends on your own needs.
    But i found that replacing function names to be able to redefine them as needed is a good way, if your code is really too big to be rewritten in human times.

    Hope this will help someone, let me eventually know your comments.
    Cheers!

  2. #2
    Junior Member
    Join Date
    Sep 2004
    Location
    Venice, Italy
    Posts
    12
    A small update for the function replacing mysql_result.
    I just realized a data seek was needed too, in case mysql_result is called more than once on the same result set.

    PHP Code:
    function mysqltng_result($result$pos$field) {
        
    $i=0;
        
    $retval='';
        
    $result->data_seek(0);
        while (
    $row $result->fetch_array(MYSQLI_BOTH)) {
            if (
    $i==$pos$retval=$row[$field];
            
    $i++;
        }
        return 
    $retval;


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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