Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : MySQL Improved Extension

MySQL Improved Extension

Introduction

The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above. More information about the MySQL Database server can be found at » http://www.mysql.com/

Documentation for MySQL can be found at » http://dev.mysql.com/doc/.

Parts of this documentation included from MySQL manual with permissions of MySQL AB.

Requirements

In order to have these functions available, you must compile PHP with support for the mysqli extension.

Note:

The mysqli extension is designed to work with the version 4.1.3 or above of MySQL. For previous versions, please see the MySQL extension documentation.

Installation

To install the mysqli extension for PHP, use the --with-mysqli=mysql_config_path/mysql_config configuration option where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1.

If you would like to install the mysql extension along with the mysqli extension you have to use the same client library to avoid any conflicts.

Installation on Windows Systems

MySQLi is not enabled by default, so the php_mysqli.dll DLL must be enabled inside of php.ini. Also, PHP needs access to the MySQL client library. A file named libmysql.dll is included in the Windows PHP distribution and in order for PHP to talk to MySQL this file needs to be available to the Windows systems PATH. See the FAQ titled "How do I add my PHP directory to the PATH on Windows" for information on how to do this. Although copying libmysql.dll to the Windows system directory also works (because the system directory is by default in the system's PATH), it's not recommended.

Note:

For connecting to MySQL 5, it is recommended to download binaries from » http://dev.mysql.com/downloads/connector/php/.

As with enabling any PHP extension (such as php_mysqli.dll), the PHP directive extension_dir should be set to the directory where the PHP extensions are located. See also the Manual Windows Installation Instructions. An example extension_dir value for PHP 5 is c:\php\ext

Note:

If when starting the web server an error similar to the following occurs: "Unable to load dynamic library './php_mysqli.dll'", this is because php_mysqli.dll and/or libmysql.dll cannot be found by the system.

Runtime Configuration

The behaviour of these functions is affected by settings in php.ini.

Table 184. MySQLi Configuration Options

Name Default Changeable Changelog
mysqli.max_links "-1" PHP_INI_SYSTEM Available since PHP 5.0.0.
mysqli.default_port "3306" PHP_INI_ALL Available since PHP 5.0.0.
mysqli.default_socket NULL PHP_INI_ALL Available since PHP 5.0.0.
mysqli.default_host NULL PHP_INI_ALL Available since PHP 5.0.0.
mysqli.default_user NULL PHP_INI_ALL Available since PHP 5.0.0.
mysqli.default_pw NULL PHP_INI_ALL Available since PHP 5.0.0.


For further details and definitions of the above PHP_INI_* constants, see the chapter on configuration changes.

Here's a short explanation of the configuration directives.

mysqli.max_links integer

The maximum number of MySQL connections per process.

mysqli.default_port string

The default TCP port number to use when connecting to the database server if no other port is specified. If no default is specified, the port will be obtained from the MYSQL_TCP_PORT environment variable, the mysql-tcp entry in /etc/services or the compile-time MYSQL_PORT constant, in that order. Win32 will only use the MYSQL_PORT constant.

mysqli.default_socket string

The default socket name to use when connecting to a local database server if no other socket name is specified.

mysqli.default_host string

The default server host to use when connecting to the database server if no other host is specified. Doesn't apply in safe mode.

mysqli.default_user string

The default user name to use when connecting to the database server if no other name is specified. Doesn't apply in safe mode.

mysqli.default_pw string

The default password to use when connecting to the database server if no other password is specified. Doesn't apply in safe mode.

Predefined Classes

mysqli

Represents a connection between PHP and a MySQL database.

Constructor

  • mysqli - construct a new mysqli object

Methods

  • autocommit - turns on or off auto-commiting database modifications

  • change_user - changes the user of the specified database connection

  • character_set_name - returns the default character set for the database connection

  • close - closes a previously opened connection

  • commit - commits the current transaction

  • connect - opens a new connection to MySQL database server

  • debug - performs debugging operations

  • dump_debug_info - dumps debug information

  • get_client_info - returns client version

  • get_host_info - returns type of connection used

  • get_server_info - returns version of the MySQL server

  • get_server_version - returns version of the MySQL server

  • init - initializes mysqli object

  • info - retrieves information about the most recently executed query

  • kill - asks the server to kill a mysql thread

  • multi_query - performs multiple queries

  • more_results - check if more results exist from currently executed multi-query

  • next_result - reads next result from currently executed multi-query

  • options - set options

  • ping - pings a server connection or reconnects if there is no connection

  • prepare - prepares a SQL query

  • query - performs a query

  • real_connect - attempts to open a connection to MySQL database server

  • escape_string - escapes special characters in a string for use in a SQL statement, taking into account the current charset of the connection

  • rollback - rolls back the current transaction

  • select_db - selects the default database

  • set_charset - sets the default client character set

  • ssl_set - sets ssl parameters

  • stat - gets the current system status

  • stmt_init- initializes a statement for use with mysqli_stmt_prepare

  • store_result - transfers a resultset from last query

  • thread_safe - returns whether thread safety is given or not

  • use_result - transfers an unbuffered resultset from last query

Properties

  • affected_rows - gets the number of affected rows in a previous MySQL operation

  • client_info - returns the MySQL client version as a string

  • client_version - returns the MySQL client version as an integer

  • errno - returns the error code for the most recent function call

  • error - returns the error string for the most recent function call

  • field_count - returns the number of columns for the most recent query

  • host_info - returns a string representing the type of connection used

  • info - retrieves information about the most recently executed query

  • insert_id - returns the auto generated id used in the last query

  • protocol_version - returns the version of the MySQL protocol used

  • server_info - returns a string that represents the server version number

  • server_version - returns the version number of the server as an integer

  • sqlstate - returns a string containing the SQLSTATE error code for the last error

  • thread_id - returns the thread ID for the current connection

  • warning_count - returns the number of warnings generated during execution of the previous SQL statement

mysqli_stmt

Represents a prepared statement.

Methods

  • bind_param - binds variables to a prepared statement

  • bind_result - binds variables to a prepared statement for result storage

  • close - closes a prepared statement

  • data_seek - seeks to an arbitrary row in a statement result set

  • execute - executes a prepared statement

  • fetch - fetches result from a prepared statement into bound variables

  • free_result - frees stored result memory for the given statement handle

  • prepare - prepares a SQL query

  • reset - resets a prepared statement

  • result_metadata - retrieves a resultset from a prepared statement for metadata information

  • send_long_data - sends data in chunks

  • store_result - buffers complete resultset from a prepared statement

Properties

  • affected_rows - returns affected rows from last statement execution

  • errno - returns errorcode for last statement function

  • error - returns errormessage for last statement function

  • field_count - returns the number of columns in a result set

  • id - returns the statement identifier

  • insert_id - returns the value generated for an AUTO_INCREMENT column by the prepared statement

  • num_rows - returns the number of rows in the result set

  • param_count - returns number of parameter for a given prepare statement

  • sqlstate - returns a string containing the SQLSTATE error code for the last statement function

mysqli_result

Represents the result set obtained from a query against the database.

Methods

Properties

  • current_field - returns offset of current fieldpointer

  • field_count - returns number of fields in resultset

  • lengths - returns an array of columnlengths

  • num_rows - returns number of rows in resultset

  • type - returns MYSQLI_STORE_RESULT or MYSQLI_USE_RESULT

Predefined Constants

Table 185. MySQLi Constants

Name Description
MYSQLI_READ_DEFAULT_GROUP (integer) Read options from the named group from `my.cnf' or the file specified with MYSQLI_READ_DEFAULT_FILE
MYSQLI_READ_DEFAULT_FILE (integer) Read options from the named option file instead of from my.cnf
MYSQLI_OPT_CONNECT_TIMEOUT (integer) Connect timeout in seconds
MYSQLI_OPT_LOCAL_INFILE (integer) Enables command LOAD LOCAL INFILE
MYSQLI_INIT_COMMAND (integer) Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting.
MYSQLI_CLIENT_SSL (integer) Use SSL (encrypted protocol). This option should not be set by application programs; it is set internally in the MySQL client library
MYSQLI_CLIENT_COMPRESS (integer) Use compression protocol
MYSQLI_CLIENT_INTERACTIVE (integer) Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection. The client's session wait_timeout variable will be set to the value of the session interactive_timeout variable.
MYSQLI_CLIENT_IGNORE_SPACE (integer) Allow spaces after function names. Makes all functions names reserved words.
MYSQLI_CLIENT_NO_SCHEMA (integer) Don't allow the db_name.tbl_name.col_name syntax.
MYSQLI_CLIENT_MULTI_QUERIES (integer)
MYSQLI_STORE_RESULT (integer) For using buffered resultsets
MYSQLI_USE_RESULT (integer) For using unbuffered resultsets
MYSQLI_ASSOC (integer) Columns are returned into the array having the fieldname as the array index.
MYSQLI_NUM (integer) Columns are returned into the array having an enumerated index.
MYSQLI_BOTH (integer) Columns are returned into the array having both a numerical index and the fieldname as the associative index.
MYSQLI_NOT_NULL_FLAG (integer) Indicates that a field is defined as NOT NULL
MYSQLI_PRI_KEY_FLAG (integer) Field is part of a primary index
MYSQLI_UNIQUE_KEY_FLAG (integer) Field is part of a unique index.
MYSQLI_MULTIPLE_KEY_FLAG (integer) Field is part of an index.
MYSQLI_BLOB_FLAG (integer) Field is defined as BLOB
MYSQLI_UNSIGNED_FLAG (integer) Field is defined as UNSIGNED
MYSQLI_ZEROFILL_FLAG (integer) Field is defined as ZEROFILL
MYSQLI_AUTO_INCREMENT_FLAG (integer) Field is defined as AUTO_INCREMENT
MYSQLI_TIMESTAMP_FLAG (integer) Field is defined as TIMESTAMP
MYSQLI_SET_FLAG (integer) Field is defined as SET
MYSQLI_NUM_FLAG (integer) Field is defined as NUMERIC
MYSQLI_PART_KEY_FLAG (integer) Field is part of an multi-index
MYSQLI_GROUP_FLAG (integer) Field is part of GROUP BY
MYSQLI_TYPE_DECIMAL (integer) Field is defined as DECIMAL
MYSQLI_TYPE_NEWDECIMAL (integer) Precision math DECIMAL or NUMERIC field (MySQL 5.0.3 and up)
MYSQLI_TYPE_BIT (integer) Field is defined as BIT (MySQL 5.0.3 and up)
MYSQLI_TYPE_TINY (integer) Field is defined as TINYINT
MYSQLI_TYPE_SHORT (integer) Field is defined as INT
MYSQLI_TYPE_LONG (integer) Field is defined as INT
MYSQLI_TYPE_FLOAT (integer) Field is defined as FLOAT
MYSQLI_TYPE_DOUBLE (integer) Field is defined as DOUBLE
MYSQLI_TYPE_NULL (integer) Field is defined as DEFAULT NULL
MYSQLI_TYPE_TIMESTAMP (integer) Field is defined as TIMESTAMP
MYSQLI_TYPE_LONGLONG (integer) Field is defined as BIGINT
MYSQLI_TYPE_INT24 (integer) Field is defined as MEDIUMINT
MYSQLI_TYPE_DATE (integer) Field is defined as DATE
MYSQLI_TYPE_TIME (integer) Field is defined as TIME
MYSQLI_TYPE_DATETIME (integer) Field is defined as DATETIME
MYSQLI_TYPE_YEAR (integer) Field is defined as YEAR
MYSQLI_TYPE_NEWDATE (integer) Field is defined as DATE
MYSQLI_TYPE_ENUM (integer) Field is defined as ENUM
MYSQLI_TYPE_SET (integer) Field is defined as SET
MYSQLI_TYPE_TINY_BLOB (integer) Field is defined as TINYBLOB
MYSQLI_TYPE_MEDIUM_BLOB (integer) Field is defined as MEDIUMBLOB
MYSQLI_TYPE_LONG_BLOB (integer) Field is defined as LONGBLOB
MYSQLI_TYPE_BLOB (integer) Field is defined as BLOB
MYSQLI_TYPE_VAR_STRING (integer) Field is defined as VARCHAR
MYSQLI_TYPE_STRING (integer) Field is defined as CHAR
MYSQLI_TYPE_GEOMETRY (integer) Field is defined as GEOMETRY
MYSQLI_NEED_DATA (integer) More data available for bind variable
MYSQLI_NO_DATA (integer) No more data available for bind variable
MYSQLI_DATA_TRUNCATED (integer) Data truncation occurred. Available since PHP 5.1.0 and MySQL 5.0.5.


Examples

All Examples in the MySQLI documentation use the world database from MySQL AB. The world database can be found at » http://dev.mysql.com/get/Downloads/Manual/world.sql.gz/from/pick

Table of Contents

mysqli_affected_rows — Gets the number of affected rows in a previous MySQL operation
mysqli_autocommit — Turns on or off auto-commiting database modifications
mysqli_bind_param — Alias for mysqli_stmt_bind_param()
mysqli_bind_result — Alias for mysqli_stmt_bind_result()
mysqli_change_user — Changes the user of the specified database connection
mysqli_character_set_name — Returns the default character set for the database connection
mysqli_client_encoding — Alias of mysqli_character_set_name()
mysqli_close — Closes a previously opened database connection
mysqli_commit — Commits the current transaction
mysqli_connect_errno — Returns the error code from last connect call
mysqli_connect_error — Returns a string description of the last connect error
mysqli_connect — Open a new connection to the MySQL server
mysqli_data_seek — Adjusts the result pointer to an arbitary row in the result
mysqli_debug — Performs debugging operations
mysqli_disable_reads_from_master — Disable reads from master
mysqli_disable_rpl_parse — Disable RPL parse
mysqli_dump_debug_info — Dump debugging information into the log
mysqli_embedded_server_end
mysqli_embedded_server_start
mysqli_enable_reads_from_master — Enable reads from master
mysqli_enable_rpl_parse — Enable RPL parse
mysqli_errno — Returns the error code for the most recent function call
mysqli_error — Returns a string description of the last error
mysqli_escape_string — Alias of mysqli_real_escape_string()
mysqli_execute — Alias for mysqli_stmt_execute()
mysqli_fetch_array — Fetch a result row as an associative, a numeric array, or both
mysqli_fetch_assoc — Fetch a result row as an associative array
mysqli_fetch_field_direct — Fetch meta-data for a single field
mysqli_fetch_field — Returns the next field in the result set
mysqli_fetch_fields — Returns an array of objects representing the fields in a result set
mysqli_fetch_lengths — Returns the lengths of the columns of the current row in the result set
mysqli_fetch_object — Returns the current row of a result set as an object
mysqli_fetch_row — Get a result row as an enumerated array
mysqli_fetch — Alias for mysqli_stmt_fetch()
mysqli_field_count — Returns the number of columns for the most recent query
mysqli_field_seek — Set result pointer to a specified field offset
mysqli_field_tell — Get current field offset of a result pointer
mysqli_free_result — Frees the memory associated with a result
mysqli_get_charset — Returns a character set object
mysqli_get_client_info — Returns the MySQL client version as a string
mysqli_get_client_version — Get MySQL client info
mysqli_get_host_info — Returns a string representing the type of connection used
mysqli_get_metadata — Alias for mysqli_stmt_result_metadata()
mysqli_get_proto_info — Returns the version of the MySQL protocol used
mysqli_get_server_info — Returns the version of the MySQL server
mysqli_get_server_version — Returns the version of the MySQL server as an integer
mysqli_get_warnings
mysqli_info — Retrieves information about the most recently executed query
mysqli_init — Initializes MySQLi and returns a resource for use with mysqli_real_connect()
mysqli_insert_id — Returns the auto generated id used in the last query
mysqli_kill — Asks the server to kill a MySQL thread
mysqli_master_query — Enforce execution of a query on the master in a master/slave setup
mysqli_more_results — Check if there are any more query results from a multi query
mysqli_multi_query — Performs a query on the database
mysqli_next_result — Prepare next result from multi_query
mysqli_num_fields — Get the number of fields in a result
mysqli_num_rows — Gets the number of rows in a result
mysqli_options — Set options
mysqli_param_count — Alias for mysqli_stmt_param_count()
mysqli_ping — Pings a server connection, or tries to reconnect if the connection has gone down
mysqli_prepare — Prepare a SQL statement for execution
mysqli_query — Performs a query on the database
mysqli_real_connect — Opens a connection to a mysql server
mysqli_real_escape_string — Escapes special characters in a string for use in a SQL statement, taking into account the current charset of the connection
mysqli_real_query — Execute an SQL query
mysqli_report — Enables or disables internal report functions
mysqli_rollback — Rolls back current transaction
mysqli_rpl_parse_enabled — Check if RPL parse is enabled
mysqli_rpl_probe — RPL probe
mysqli_rpl_query_type — Returns RPL query type
mysqli_select_db — Selects the default database for database queries
mysqli_send_long_data — Alias for mysqli_stmt_send_long_data()
mysqli_send_query — Send the query and return
mysqli_server_end — Shut down the embedded server
mysqli_server_init — Initialize embedded server
mysqli_set_charset — Sets the default client character set
mysqli_set_local_infile_default — Unsets user defined handler for load local infile command
mysqli_set_local_infile_handler — Set callback functions for LOAD DATA LOCAL INFILE command
mysqli_set_opt — Alias of mysqli_options()
mysqli_slave_query — Force execution of a query on a slave in a master/slave setup
mysqli_sqlstate — Returns the SQLSTATE error from previous MySQL operation
mysqli_ssl_set — Used for establishing secure connections using SSL
mysqli_stat — Gets the current system status
mysqli_stmt_affected_rows — Returns the total number of rows changed, deleted, or inserted by the last executed statement
mysqli_stmt_attr_get
mysqli_stmt_attr_set
mysqli_stmt_bind_param — Binds variables to a prepared statement as parameters
mysqli_stmt_bind_result — Binds variables to a prepared statement for result storage
mysqli_stmt_close — Closes a prepared statement
mysqli_stmt_data_seek — Seeks to an arbitray row in statement result set
mysqli_stmt_errno — Returns the error code for the most recent statement call
mysqli_stmt_error — Returns a string description for last statement error
mysqli_stmt_execute — Executes a prepared Query
mysqli_stmt_fetch — Fetch results from a prepared statement into the bound variables
mysqli_stmt_field_count — Returns the number of field in the given statement
mysqli_stmt_free_result — Frees stored result memory for the given statement handle
mysqli_stmt_get_warnings
mysqli_stmt_init — Initializes a statement and returns an object for use with mysqli_stmt_prepare
mysqli_stmt_insert_id — Get the ID generated from the previous INSERT operation
mysqli_stmt_num_rows — Return the number of rows in statements result set
mysqli_stmt_param_count — Returns the number of parameter for the given statement
mysqli_stmt_prepare — Prepare a SQL statement for execution
mysqli_stmt_reset — Resets a prepared statement
mysqli_stmt_result_metadata — Returns result set metadata from a prepared statement
mysqli_stmt_send_long_data — Send data in blocks
mysqli_stmt_sqlstate — Returns SQLSTATE error from previous statement operation
mysqli_stmt_store_result — Transfers a result set from a prepared statement
mysqli_store_result — Transfers a result set from the last query
mysqli_thread_id — Returns the thread ID for the current connection
mysqli_thread_safe — Returns whether thread safety is given or not
mysqli_use_result — Initiate a result set retrieval
mysqli_warning_count — Returns the number of warnings from the last query for the given link

Code Examples / Notes » ref.mysqli

smurf

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


hans

While writing a class that extends mysqli I ran across some rather strange bugs,
that however did resove itself, when I found out that they were caused by:
<?php
parent::__construct( ... ... ... $this->dbase_password);
?>
so I changed it to the following:
<?php
/**
* Database class for handling database connections.
*
* @author Hans Duedal
* @version 22 Marts 2005
*/
class DBase extends mysqli {
protected $dbase_hostname;
protected $dbase_username;
protected $dbase_password;
protected $dbase_name;


/**
*  Consctructor for objects of class DBase
*
* @param File $settings_ini_file
*/
public function __construct(File $settings_ini_file) {
$ini_array = parse_ini_file($settings_ini_file->requireFilePath());
$this->dbase_hostname = $ini_array["dbase_hostname"];
$this->dbase_username = $ini_array["dbase_username"];
$this->dbase_password = $ini_array["dbase_password"];
$this->dbase_name = $ini_array["dbase_name"];

parent::__construct(
$ini_array["dbase_hostname"],
$ini_array["dbase_username"],
$ini_array["dbase_password"],
$ini_array["dbase_name"]);

if (mysqli_connect_error()) {
throw new Exception("Connect exception:\n".mysqli_connect_error());
}
}

/**
* Desctructor for objects of class DBase, closes the connection
*
*/
public function __destruct() {
parent::__destruct();
parent::close($this);
}

/**
* Accessor method to return protected variable $dbase_name
*
* @return string
*/
public function getName() {
return $this->dbase_name;
}

/**
* Raw MySQL query
* throws exception
*
* @param string $query
* @return mysqli_result
*/
public function query($query) {
$result = parent::query($query);
if (mysqli_error($this))
throw new Exception("Query exception:\n".mysqli_error($this));
return $result;
}
}
?>
btw the File class is my own as well... couldn't find any File object in PHP5 ? :S
They are both avaible for download at:
http://www.lintoo.dk/public/dbase_and_file_class.zip
Hans Duedal // hans@lintoo.dk


kronicade@yahoo

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


brad marsh

Notes for FreeBSD 6.0-RELEASE
MySQL 5.0.15 (source), Apache 2.0.55 (port), PHP 5.0.15 (source) - I used sources for MySQL and PHP because I could not get it all to work together using only ports.
First MySQL:
==================================
cd /usr/local/src
tar zxvf /path/to/mysql-5.0.15.tar.gz
./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --enable-assembler --with-mysqld-ldflags=-all-static CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti"
make
make install
Apache:
==================================
MAKE SURE YOUR PORTS ARE UP-TO-DATE - use cvsup
cd /usr/ports/www/apache2
make
make install
make clean
PHP:
==================================
cd /usr/local/src
tar zxvf /path/to/php-5.0.5.tar.gz
./configure --with-xml --with-zlib --with-mysql=/usr/local/mysql --with-mysqli=/usr/local/mysql/bin/mysql_config --with-apxs2=/usr/local/sbin/apxs
EXTRA STEP for mysql and mysqli extensions at the same time:
See this forum thread: http://www.kofler.cc/forum/forumthread.php?rootID=3571
In the PHP Makefile, I changed the line (note all the duplicates)
EXTRA_LIBS = -lcrypt -lcrypt -lmysqlclient -lz -lm -lxml2 -lz -liconv -lm -lxml2 -lz -liconv -lm -lmysqlclient -lz -lcrypt -lm -lxml2 -lz -liconv -lm -lcrypt -lxml2 -lz -liconv -lm -lcrypt
to
EXTRA_LIBS = -lcrypt -lmysqlclient -lz -lm -lxml2 -liconv
make
make install
Hope this helps somebody...


arjen

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/


docey

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.


jedifreeman

It should be noted here that mysqli is a PHP 5 only extension.

kenashkov

It seems that the mysqli class is overloaded.
get_class_vars(mysqli) returns nothing.
__set() and __get() are overloaded, but __isset() it not!!!
This means that the code:
<?
$mysqli_object = new mysqli();
if(isset($mysqli_object->client_info))
{
print $mysqli_object->client_info;
}
?>
will not print anything.
The workaround is:
<?
if($mysqli_object->client_info!==null)
{
print $mysqli_object->client_info;
}
?>


nathansquires

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.

andrei nazarenko

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.


dgbaley27

I wrote the following mysqli extension to make it a little easier to pull data for some peope.  It combines the query() and fetch_assoc() methods:
<?
class Sql extends mysqli {
function __construct($host, $user, $password, $table) {
parent::__construct($host, $user, $password, $table);
}
function data($SQL, $force=0) {
static $holdQuery;
static $result;
if ($SQL != $holdQuery) {
$result = $this->query($SQL);
$holdQuery = $SQL;
}
return $result->fetch_assoc();
}
}
?>
So to implement:
<?
$link = new Sql("host", "user", "password", "table");
$SQL = "SELECT * FROM anyTable";
while ($row = $link->data($SQL)) {
       // manipulate data
}
?>
This can be very much expanded upon but I'm a freak for minimal code and I'm sure there are pleanty of you out there like me.


michael
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.


severin dot kacianka

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


yair lapin

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


ezbakhe yassin
I made a simple class that extends mysqli. It has the following features:
- Use an XML file to store multiple connection parameters.
- Auto display DB error messages (you can easily disable this in a production server)
- Retrieve columns max lenght
- Retrieve column comments
- Retrieve ENUM|SET possible values as an array
You are free to extend/modify it, but please give me credit.
<?php
class mysqli2 extends mysqli
{
   private $dbHost, $dbUser, $dbPassword, $dbSchema;
   public function __construct($XMLFilePath, $connectionID)
   {
       $this->_getDBParameters($XMLFilePath, $connectionID);
       @parent::__construct($this->dbHost, $this->dbUser, $this->dbPassword, $this->dbSchema);
       if (mysqli_connect_errno())
       {
           die(sprintf("Can't connect to database. Error: %s", mysqli_connect_error()));
       }
   }
   public function __destruct()
   {
       if (!mysqli_connect_errno())
       {
           $this->close();
       }
   }
   private function _getDBParameters($XMLFilePath, $connectionID)
   {
       if (!(file_exists($XMLFilePath)))
       {
           return FALSE;
       }
       $connections = simplexml_load_file($XMLFilePath);
       foreach ($connections as $connection)
       {
           if ($connection['ID'] == $connectionID)
           {
               $this->dbHost     = $connection->dbHost;
               $this->dbUser     = $connection->dbUser;
               $this->dbPassword = $connection->dbPassword;
               $this->dbSchema   = $connection->dbSchema;
           }
       }
   }
   public function query($sql)
   {
       if (!($result = parent::query($sql)))
       {
           die(sprintf("Can't perform query on database. Error: %s", $this->error));
       }
       return $result;
   }
   public function get_columns_max_len($dbTable)
   {
       if (!($result = $this->query("DESCRIBE $dbTable")))
       {
           return FALSE;
       }
       while ($row = $result->fetch_array(MYSQLI_ASSOC))
       {
           if (ereg('^.+\((.+)\)', $row['Type'], $columnMaxLen))
           {
               $columnMaxLens[$row['Field']] = (int)$columnMaxLen[1];
           }
       }
       return $columnMaxLens;
   }
   public function get_columns_comments($dbTable)
   {
       if (!($result = $this->query("SHOW CREATE TABLE $dbTable")))
       {
           return FALSE;
       }
       $row = $result->fetch_row();
       $tableSQL = explode("\n", $row[1]);
       foreach ($tableSQL as $tableSQLLine)
       {
           if (ereg(".+ `(.+)` .+ COMMENT '(.+)'", $tableSQLLine, $columnCommentsBuffer))
           {
               $columnComments[$columnCommentsBuffer[1]] = $columnCommentsBuffer[2];
           }
       }
       return $columnComments;
   }
   public function get_enum_options($dbTable, $dbColumn)
   {
       if (!($result = $this->query("DESCRIBE $dbTable")))
       {
           return FALSE;
       }
       while ($row = $result->fetch_array(MYSQLI_ASSOC))
       {
           if ($row['Field'] == $dbColumn)
           {
               if (eregi('(enum|set)\((.+)\)', $row['Type'], $enumValues))
               {
                   $enumValues = explode(",", str_replace("'", NULL, $enumValues[2]));
               }
           }
       }
       return isset($enumValues) ? $enumValues : FALSE;
   }
}
?>
The XML file looks like:
<?xml version ='1.0' encoding ='UTF-8' ?>
<connections>
<connection ID="1">
<dbHost>*DATA*</dbHost>
<dbUser>*DATA*</dbUser>
<dbPassword>*DATA*</dbPassword>
<dbSchema>*DATA*</dbSchema>
</connection>
</connections>


neil

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


dan+php dot net-note-about-mysqli

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

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);
}
?>


ajp

From README.PHP4-TO-PHP5-THIN-CHANGES:
Be careful when porting from ext/mysql to ext/mysqli. The following
  functions return NULL when no more data is available in the result set
  (ext/mysql's functions return FALSE).
   - mysqli_fetch_row()
   - mysqli_fetch_array()
   - mysqli_fetch_assoc()


marcus

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.


php

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.


chris gerlt.net

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


philip


Change Language


Follow Navioo On Twitter
.NET Functions
Apache-specific Functions
Alternative PHP Cache
Advanced PHP debugger
Array Functions
Aspell functions [deprecated]
BBCode Functions
BCMath Arbitrary Precision Mathematics Functions
PHP bytecode Compiler
Bzip2 Compression Functions
Calendar Functions
CCVS API Functions [deprecated]
Class/Object Functions
Classkit Functions
ClibPDF Functions [deprecated]
COM and .Net (Windows)
Crack Functions
Character Type Functions
CURL
Cybercash Payment Functions
Credit Mutuel CyberMUT functions
Cyrus IMAP administration Functions
Date and Time Functions
DB++ Functions
Database (dbm-style) Abstraction Layer Functions
dBase Functions
DBM Functions [deprecated]
dbx Functions
Direct IO Functions
Directory Functions
DOM Functions
DOM XML Functions
enchant Functions
Error Handling and Logging Functions
Exif Functions
Expect Functions
File Alteration Monitor Functions
Forms Data Format Functions
Fileinfo Functions
filePro Functions
Filesystem Functions
Filter Functions
Firebird/InterBase Functions
Firebird/Interbase Functions (PDO_FIREBIRD)
FriBiDi Functions
FrontBase Functions
FTP Functions
Function Handling Functions
GeoIP Functions
Gettext Functions
GMP Functions
gnupg Functions
Net_Gopher
Haru PDF Functions
hash Functions
HTTP
Hyperwave Functions
Hyperwave API Functions
i18n Functions
IBM Functions (PDO_IBM)
IBM DB2
iconv Functions
ID3 Functions
IIS Administration Functions
Image Functions
Imagick Image Library
IMAP
Informix Functions
Informix Functions (PDO_INFORMIX)
Ingres II Functions
IRC Gateway Functions
PHP / Java Integration
JSON Functions
KADM5
LDAP Functions
libxml Functions
Lotus Notes Functions
LZF Functions
Mail Functions
Mailparse Functions
Mathematical Functions
MaxDB PHP Extension
MCAL Functions
Mcrypt Encryption Functions
MCVE (Monetra) Payment Functions
Memcache Functions
Mhash Functions
Mimetype Functions
Ming functions for Flash
Miscellaneous Functions
mnoGoSearch Functions
Microsoft SQL Server Functions
Microsoft SQL Server and Sybase Functions (PDO_DBLIB)
Mohawk Software Session Handler Functions
mSQL Functions
Multibyte String Functions
muscat Functions
MySQL Functions
MySQL Functions (PDO_MYSQL)
MySQL Improved Extension
Ncurses Terminal Screen Control Functions
Network Functions
Newt Functions
NSAPI-specific Functions
Object Aggregation/Composition Functions
Object property and method call overloading
Oracle Functions
ODBC Functions (Unified)
ODBC and DB2 Functions (PDO_ODBC)
oggvorbis
OpenAL Audio Bindings
OpenSSL Functions
Oracle Functions [deprecated]
Oracle Functions (PDO_OCI)
Output Control Functions
Ovrimos SQL Functions
Paradox File Access
Parsekit Functions
Process Control Functions
Regular Expression Functions (Perl-Compatible)
PDF Functions
PDO Functions
Phar archive stream and classes
PHP Options&Information
POSIX Functions
Regular Expression Functions (POSIX Extended)
PostgreSQL Functions
PostgreSQL Functions (PDO_PGSQL)
Printer Functions
Program Execution Functions
PostScript document creation
Pspell Functions
qtdom Functions
Radius
Rar Functions
GNU Readline
GNU Recode Functions
RPM Header Reading Functions
runkit Functions
SAM - Simple Asynchronous Messaging
Satellite CORBA client extension [deprecated]
SCA Functions
SDO Functions
SDO XML Data Access Service Functions
SDO Relational Data Access Service Functions
Semaphore
SESAM Database Functions
PostgreSQL Session Save Handler
Session Handling Functions
Shared Memory Functions
SimpleXML functions
SNMP Functions
SOAP Functions
Socket Functions
Standard PHP Library (SPL) Functions
SQLite Functions
SQLite Functions (PDO_SQLITE)
Secure Shell2 Functions
Statistics Functions
Stream Functions
String Functions
Subversion Functions
Shockwave Flash Functions
Swish Functions
Sybase Functions
TCP Wrappers Functions
Tidy Functions
Tokenizer Functions
Unicode Functions
URL Functions
Variable Handling Functions
Verisign Payflow Pro Functions
vpopmail Functions
W32api Functions
WDDX Functions
win32ps Functions
win32service Functions
xattr Functions
xdiff Functions
XML Parser Functions
XML-RPC Functions
XMLReader functions
XMLWriter Functions
XSL functions
XSLT Functions
YAZ Functions
YP/NIS Functions
Zip File Functions
Zlib Compression Functions
eXTReMe Tracker