PHP 7.0.6 Released

    Aliases and deprecated Mysqli Functions

    Table of Contents

    User Contributed Notes

    dan+php dot net-note-about-mysqli at obluda dot cz
    9 years ago
    Hints for upgrading PHP code from MySQL to MySQLi:

    Note - MySQLi doesn't support persistent connection. If you need it, you must implement it by self. This case is not covered in this note.

    First - change all occurences of MYSQL_ to MYSQLI_ and mysql_ to mysqli_

    Most of changes is required because mysqli* functions has no implicit link argument, so it need to be added explicitly if not present already. Even if it is present, most mysqli_ functions require 'link' argument as first (and mandatory) argument instead of last (and optional) as required by mysql_ functions. So, we need to change order of arguments at least.

    So, you need to found names of all mysql_ functions used in your code, check if it need reoder of parameters or add the link parameter. Only *_connect() functions need review, but most scripts contain only few connect call.

    If you use functions deprecated in mysql, then may not be impemented in mysqli. Those need to be reimplemented if required. In most case, it's very simple. In advance, PHP script written by carefull programer should not contain deprecated calls, so it's not problem in most cases at all.

    Most of scripts I ported from mysql_ to mysqli_ can be converted by simple sed script followed by *_connect() function call review only. Especially when when programmer doesn't used the link as implicit argument and doesn't use deprecated functions the conversion of PHP source is trivial task and converted script work with no problem.

    Special handling of some functions:

    mysql_connect(),mysql_pconnect()
    -----------------------------------------
    Call with 3 or less parameters need not to be modified.
    4-parameters call - delete 4th parameter as mysqli is always non-persistent
    5-parameters - replace it by sequence of mysqli_init();mysqli_options();mysqli_real_connect()

    mysql_create_db(), mysql_drop_db(), mysql_list_dbs(), mysql_db_name(),mysql_list_fields(), mysql_list_processes(), mysql_list_tables(), mysql_db_query(),mysql_table_name()
    ------------------------------------------
    mysqli variant doesn't exist (those functions has been deprecated even in mysql_) but it's easy to reimplement each of it. Use apropropriate SQL command followed by standard loop for processing query results where applicable. If no connection to server yet you need to use explicit mysqli_connect call.

    mysql_result()
    -----------------
    mysqli variant doesn't exist. Use one of mysqli_fetch_* variant (depending of type of mysql_result() third argument)

    mysql_unbuffered_query()
    -----------------
    mysqli variant doesn't exist. Use mysqli_real_query()
    phoeniks[at]nm[dot]ru
    12 years ago
    Here is a little sample to do fast hierarchical queries using new prepared statements

    <?php
    // Root - lowest start element
    // Top  - most top element
    // stack - array for storing info

    function tree_rise($root, &$stack, $top = 0) {
          $mysqli = mysqli_connect('localhost', 'root');
          $top  = (int)$top;
          $stmt = mysqli_prepare($mysqli, "SELECT id, pid, title FROM news.strg WHERE id = ? LIMIT 1");
          mysqli_stmt_bind_param($stmt, "i", $root);
          mysqli_stmt_execute($stmt);
          mysqli_stmt_store_result($stmt);
          mysqli_bind_result($stmt, $id, $root, $title);

      while (    mysqli_fetch($stmt)) {
            $stack[$id] = $title;
        if (    $root != $top && !is_null($root)) {
              mysqli_stmt_execute($stmt);
              mysqli_stmt_store_result($stmt);
        }
      }
      return     count($stack);
    }
    ?>
    php at arwin dot net
    11 years ago
    For those having trouble compiling with both --with-mysql AND --with-mysqli, I found a solution at this URL:

    http://bugs.php.net/bug.php?id=29860&edit=1

    rjanson at msn dot com writes:

    OK, finally an answer!

    I checked Makefile and found the following line:

    EXTRA_LIBS = -lcrypt -lcrypt -lmysqlclient -lpng -lz -lz -lresolv -lm
    -ldl -lnsl -lxml2 -
    lz -lm -lxml2 -lz -lm -lmysqlclient -lcrypt -lnsl -lm -lz -lnss_files
    -lnss_dns -lresolv -lnss_files
    -lnss_dns -lresolv -lxml2 -lz -lm -lcrypt -lxml2 -lz -lm -lcrypt

    By removing one of the -lmysqlclient entries on this line I was able to
    successfully make and make install with both mysql and mysqli. As
    confirmed by phpInfo().

    I'm not sure why the other libs have multiple entries and don't cause
    make to crash.
    Yair Lapin
    11 years ago
    I successed to install php support for a mysql 4.1.7 database after several attempts because the instructions are not clear.
    I have a server with linux SuSe 9 enterprise with apache 1.3.31. I installed mysql from rpm files, i installed php 4.3.9 as dynamic library. this version database needs its own client library else the mysql functions in php will not work.
    configuration must be as following:

    ./configure --with-msqli=/usr/bin/mysql_config --with-mysql=/usr  --with-apxs=/usr/local/apache/bin/apxs

    Must be installed in your machine the correct mysql librery:

    MySQL-devel-4.1.7-0
    MySQL-shared-4.1.7-0
    nathansquires at pacbell dot net
    11 years ago
    If you want to build php with both the Mysql and mysqli extensions, make sure your mysql distribution comes with shared libraries or build mysql yourself with shared libraries. With only static mysql libraries the compile process will fail.
    Daniel Sowden
    8 years ago
    I had a problem compiling PHP 5.2.5 with mysqli enabled on Mac OS X. Whether this problem was unique to me I do not no, so I am posting this for anybody else that runs into the same problem. Configure would run right to the end, but I had it telling me something had failed and that it was likely messed up. Upon checking debug.log I noticed this:

    dyld: Library not loaded: /usr/local/mysql/lib/mysql/libmysqlclient.15.dylib
    Referenced from: /Users/daniel/desktop/php -5.2.5/sapi/cli/php
    Reason: image not found
    /bin/sh: line 1: /Users/daniel/desktop/php -5.2.5/tmp-php.ini: Permission denied
    dyld: Library not loaded: /usr/local/mysql/lib/mysql/libmysqlclient.15.dylib
    Referenced from: /Users/daniel/desktop/php -5.2.5/sapi/cli/php
    Reason: image not found
    make: [test] Error 133 (ignored)

    After screwing around for an hour and screaming many profanities at my computer I looked back at the error and noticed:

    "/usr/local/mysql/lib/<b>mysql/</b>libmysqlclient.15.dylib

    Why it was using this path I do not know, but I headslapped when I realised how simple it was! I solved the problem by creating a link called mysql that linked back on itself:

    cd /usr/local/mysql/lib
    sudo ln -s ./ mysql

    Problem solved! I probably should have figured out why it was doing this instead, but this was easier and saved me from allot more frustration.
    swong
    8 years ago
    If you want to extend mysqli and establish an connection with SSL authentication, the followings may be useful.
    (note: not all php-mysqli supports mysqli_ssl_set())

    class DataAccess extends mysqli
    {
        function __construct(
              $hostname, $username, $passwd=null, $dbname=null,
              $port=null, $socket=null, $flags=null,
              $privatekey=null, $certificate=null, $caCertificate=null)
         {
               $link = parent::init();
               if ($privatekey!=null && $certificate!=null && $caCertificate!=null)
                     $link->ssl_set($privatekey, $certificate, $caCertificate,
                     dirname($caCertificate),NULL);
                parent::__construct($hostname, $username,
                     $passwd, $dbname, $port, $socket);
          }

             .....
    }
    kronicade@yahoo
    10 years ago
    Same problem, no solution.  I'm running Solaris sparc with mysql 4.1.12.  I've tried every config command I can think of and have modified my PATH as follows:

    setenv PATH /usr/ccs/bin:
    /usr/local/bin:
    /usr/local/ipw/bin:
    /usr/local/ipw/contrib/bin:
    /sbin:/usr/sbin:
    /usr/bin:
    /usr/bin/X11:
    /usr/ucb:
    /usr/openwin/bin

    ./configure '--prefix=/usr/local/php' '--localstatedir=/usr/local'  '--mandir=/usr/share/man' '--with-mysqli=/usr/local/mysql/bin/mysql_config' '--includedir=/usr/lib' '--enable-shared=max'  '--enable-module=most'  '--with-imap=/usr/local/imap'  '--with-imap-ssl=/usr/local/ssl'  '--with-apxs2=/usr/local/apache2/bin/apxs'  '--enable-fastcgi'  '--enable-mbstring=all'  '--with-zlib-dir=/usr'  '--with-openssl=/usr/local/ssl'

    I think it's a compatibility issue with the version of mysql running 64-bit.

    -Andrew
    arjen at mysql dot com
    10 years ago
    John Coggeshall wrote a PHP5 ext/mysqli compatibility script for applications that still use the old ext/mysql functions. This prevents the hassle of trying to have both the mysql and mysqli extensions loaded in PHP5, which can be tricky.

    The script is at:
    http://www.coggeshall.org/oss/mysql2i/
    Andrei Nazarenko
    11 years ago
    If you are having trouble compiling PHP5 with MySQLi support on Linux and getting a message:
    "configure: error: wrong mysql library version or lib not found"
    and the config.log shows an error about -lnss_files being not found, here is the solution:

    edit your mysql_config file and *REMOVE* all  "-lnss_files" AND "-lnss_dns" entries from it.
    After that PHP should not complain anymore.
    neil
    11 years ago
    I have spent far too much time finding this answer:

    Since PHP5 many many people are having installation problems.  I found many apps were broken when I upgraded to version 5.  I tried to install mysql and mysqli and had problems.

    Here's the solution:

    After doing:

    ./configure --with-mysql=/path/to/mysql_config --with-mysqli=/path/to/mysql_config

    do this:

    "
        if you want to use both the old mysql and the new mysqli interface, load the Makefile into your editor and search for the line beginning with EXTRA_LIBS; it includes -lmysqlclient twice; remove the second instance
    "

    then you can:

    make
    make install

    ......
    Please note:  the mysql-dev must be installed or you won't have a mysql_config anywhere.  I installed the mysql-dev rpm

    Further note:  the information about the Makefile's duplicate instances of "-libmysqlclient" came from Michael Kofler.  Thanks should be directed to him.  He gives more details at the following link:

    http://www.kofler.cc/forum/forumthread.php?rootID=3571
    severin dot kacianka at aon dot at
    11 years ago
    I tryed Marco Kaiser's way of getting both mysql_* and mysqli_*, but it failed for me. However I could get it working this way:
    First of all I installed the MySQL 4.1.3 binary disribution acourding to the manual. Then I simply passed these ./configure arguments:

    --with-mysql=/path/to/mysql4.1.3
    --with-mysqli=/path/to/mysql4.1.3/bin/mysql_config

    This enabled both, the mysql_* and mysqli_* functions.

    Severin Kacianka
    nulled elitscripts2000 at yahoo dot com
    1 year ago
    If you need to install mysqli extension there is no need to recompile PHP5 pr PHP5-fpm...

    apt-get install php5-mysqlnd

    Will install mysql and mysqli and mysqlnd

    If you compile PHP from source then yes add the -use-mysqli in compile config.

    This is Debian and Ubuntu.  Or at least Ubuntu.
    smurf at smurf dot noris dot de
    8 years ago
    Yay. No more persistent connections. I am NOT happy about that.

    As soon as you get more than 250 requests per second (ever been slashdotted?) there will not be any more free TCP ports for talking to the database server, because each TCP port will be kept in TIME_WAIT for two minutes and there are only 30000 ports in the local range.

    And that's not the only problem. There's also more than twice the packet rate on the network link to the database. Frankly I'd like my CPUs to spend their cycles on something producive. :-/
    Docey
    9 years ago
    It should be noticed that mysqli does not support persistent
    connections, so do not bother about implementing them
    unless you got a million hits an hour on your website.

    also about using both mysql and mysqli, i want to note
    that under windows the best approach is to load either
    mysql or mysqli at runtime not both.

    i know that under php5 this is deprecated but it seems
    to work fine. i used the same appoach under debian-linux
    and seems to work aswell. just don't know for the
    speed cost. maybe someone can benchmark this out.

    just my 2 eurocents.
    marcus at synchromedia dot co dot uk
    8 years ago
    For those having trouble getting MySQLi to work, there is another way that is set to become much more common:

    http://dev.mysql.com/downloads/connector/php-mysqlnd/

    This is the new MySQL native driver which has been backported from PHP6 and replaces MySQLi with a version that doesn't require any local MySQL client libraries or binaries.
    chris <at> gerlt.net
    9 years ago
    For installing mysqli extension after PHP is installed on a Linux ubuntu ( or other linux distro with apt-get such as debian, etc...):

    # apt-get install php5-mysqli

    NOTE: after install, you need to reboot apache
    # apache2ctl graceful
    Michael <mstclaire at myprintflow dot com>
    10 years ago
    I was running into some random issues using the myqli extension with MySQL 5 on Mac OS X.

    I discovered that when I compiled php 5 with the --with-mysqli flag, it was building php with the pre-installed MySQL 4 client libraries.

    Heres how it fixed it to build php with the correct MySQL client libraries.

    ----------------
    I had installed the binary version of MySQL 5 from MySQL AB. It installs everything in the default location /usr/local/mysql, which is fine. The MySQL version that comes with OS X ( v.4.x, depends on your OS X version ) installs the mysql_config help utility at /usr/bin/mysql_config. When php configs, it uses that one by default, inheritently using the wrong MySQL client libs.

    No problem I thought, I just changed the --with-mysqli flag to --with-mysqli=/usr/local/mysql/bin/mysql_config ( or sudo find / -name mysql_config to find yours ). Nope, php throws a build error because it can't find the matching libraries. Hmmm...

    So i tested /usr/local/mysql/bin/mysql_config --version, and I am shown my most current MySQL install. The problem is that the binary editions for OS X DO NOT include the shared libs ( libmysqlclient.dylib ). Oh no, I did not want to compile MySQL myself, not because I don't know how, but because MySQL AB does not recommend it, and for good reasons. Trust me, I've found out the hard way.

    So what do you do? Download the source version of MySQL 5 that matches my binary version.

    Configure MySQL:

    ./configure --enable-shared ( it's listed as ON as default, but I want to be sure )

    Build MySQL:

    make ( requires Developer Tools, but you knew that )

    DO NOT make install !!! I repeat, DO NOT make install unless you really wish to overwrite your binary verions, which is not a good idea. ( You can configure MySQL with the --without-server flag, but I want to be certain I don't mess up )

    Ok, almost done. Go to the lib directory in your MySQL build location and go to the invisible directory, .libs . There you will find your shared libraries, namely libmysqlclient.15.0.0.dylib.

    Copy this to your /usr/local/mysql/lib directory. Now do the following from the lib directory:

    ln -s libmysqlclient.15.0.0.dylib libmysqlclient.15.dylib
    ln -s libmysqlclient.15.0.0.dylib libmysqlclient.dylib
    mkdir mysql
    cd mysql
    ln -s ../libmysqlclient.15.0.0.dylib libmysqlclient.15.0.0.dylib
    ln -s ../libmysqlclient.15.0.0.dylib libmysqlclient.15.dylib
    ln -s ../libmysqlclient.15.0.0.dylib libmysqlclient.dylib

    Now you can build your php with the correct library. After you build, check your phpinfo(); to validate the client version under the mysqli section. When I restarted Apache, it originally couldn't find my libraries, thus the /usr/local/mysql/lib/mysql directory.

    Hope this helped.
    To Top