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.
LXXXVI. MySQL Improved Extension
简介
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.
需求
In order to have these functions available, you must compile PHP with support for the mysqli extension.
注: 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.
安装
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.
运行时配置
这些函数的行为受 php.ini 的影响。
表格 1. 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.
以下是配置选项的简要解释。
- 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.
预定义类
mysqli
Represents a connection between PHP and a MySQL database.
方法
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
use_result - transfers an unbuffered resultset from last query
thread_safe - returns whether thread safety is given or not
属性
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
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.
方法
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
result_metadata - retrieves a resultset from a prepared statement for metadata information
prepare - prepares a SQL query
send_long_data - sends data in chunks
reset - resets a prepared statement
store_result - buffers complete resultset from a prepared statement
属性
affected_rows - returns affected rows from last statement execution
errno - returns errorcode for last statement function
errno - returns errormessage for last statement function
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.
方法
close - closes resultset
data_seek - moves internal result pointer
fetch_field - gets column information from a resultset
fetch_fields - gets information for all columns from a resulset
fetch_field_direct - gets column information for specified column
fetch_array - fetches a result row as an associative array, a numeric array, or both.
fetch_assoc - fetches a result row as an associative array
fetch_object - fetches a result row as an object
fetch_row - gets a result row as an enumerated array
close - frees result memory
field_seek - set result pointer to a specified field offset
属性
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
预定义常量
表格 2. 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 an 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_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
- 目录
- 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_connect -- Open a connection to an embedded mysql server
- 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_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_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 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_opt -- Alias of mysqli_options()
- 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_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_free_result -- Frees stored result memory for the given statement handle
- mysqli_stmt_init -- Initializes a statement and returns an object for use with mysqli_stmt_prepare
- 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
add a note
User Contributed Notes
Docey
09-Jun-2006 02:11
09-Jun-2006 02:11
s dot s at terra dot com dot br
10-May-2006 04:47
10-May-2006 04:47
Compile error using any MySQL version before 5.0.20.
I think its becose the mysqli interface provided by PHP does not support SSL connection.
kronicade@yahoo
10-Mar-2006 03:42
10-Mar-2006 03:42
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
Michael <mstclaire at myprintflow dot com>
13-Jan-2006 11:10
13-Jan-2006 11:10
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.
Brad Marsh
09-Nov-2005 11:54
09-Nov-2005 11:54
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...
dgbaley27 at comcast dot net
29-Oct-2005 04:06
29-Oct-2005 04:06
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.
Ezbakhe Yassin <yassin88 at gmail dot com>
01-Sep-2005 07:03
01-Sep-2005 07:03
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>
arjen at mysql dot com
05-Aug-2005 04:26
05-Aug-2005 04:26
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/
john
18-Jun-2005 12:55
18-Jun-2005 12:55
Some benchmarks of mysqli vs the traditional mysql ext:
http://www.johnjawed.com/benchmarks/
john
30-Apr-2005 03:16
30-Apr-2005 03:16
MySQLi help/support on Google Groups:
http://groups-beta.google.com/group/MySQLi
hans at lintoo dot dk
23-Mar-2005 12:33
23-Mar-2005 12:33
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
jedifreeman at comcast dot net
22-Mar-2005 05:49
22-Mar-2005 05:49
It should be noted here that mysqli is a PHP 5 only extension.
Andrei Nazarenko
23-Feb-2005 03:21
23-Feb-2005 03:21
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-Feb-2005 06:15
11-Feb-2005 06:15
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
K dot Londenberg at librics dot de
11-Feb-2005 01:35
11-Feb-2005 01:35
Second part of the mysqli wrapper code since it didn't all get into one note
------ exception.php -------------
<?php
class FatalException extends Exception {};
class ErrorException extends Exception {};
class WarningException extends Exception {};
function exceptionErrorHandler($errno, $errstr, $errfile, $errline) {
switch ($errno) {
case E_USER_ERROR:
throw new FatalException($errstr, $errno);
break;
case E_USER_WARNING:
case E_WARNING:
throw new WarningException($errstr, $errno);
break;
case E_USER_NOTICE:
//throw new WarningException($errstr, $errno);
break;
default:
echo " - Unknown error - <b>$errstr</b>($errno) <br>";
break;
}
//return true;
}
$old_error_handler = null;
function install_exception_errorhandler() {
global $old_error_handler;
$old_error_handler = set_error_handler(exceptionErrorHandler);
}
function uninstall_exception_errorhandler() {
global $old_error_handler;
set_error_handler($old_error_handler);
$old_error_handler = null;
}
// returns formatted info-dump of an exception
function exception_dump(&$e) {
$res = "";
$res .= "<br><h2>".get_class($e)."</h2>\n";
$res .= "<h3>{$e->getMessage()} ({$e->getCode()})</h3>\n\n";
$res .= "file: {$e->getFile()}<br/>\n";
$res .= "line: {$e->getLine()}<br/>\n";
$res .= "<PRE>";
$res .= $e->getTraceAsString();
$res .= "</PRE><br>";
return $res;
}
?>
-------------------------------------------
Example-Code:
------ example.php ------------------------
<?php
require_once('safe_mysqli.php');
try {
$mysql = new safe_mysqli('locashost', 'user', 'pass', 'test');
$stmt = $mysql->prepare("SELECT city, city_accented, population, latitude, longitude from world_cities where city like ? order by population desc limit 5");
$stmt->bind_param('s', $a);
$stmt->bind_result($city, $city_accented, $population, $latitude, $longitude);
foreach ($anfangsbuchstaben as $a) {
$stmt->execute();
while ($stmt->fetch()) {
echo "<b>$city_accented</b>: pop $population - Lat. $latitude Long. $longitude<br>";
}
$stmt->free_result();
}
} catch (SQLException $e) {
die("SQL Error : ".$e->getSQLError()." in <hr><PRE>".$e->getSQL()."</PRE><hr>");
} catch (DBException $e) {
die("Database Error: ". $e->GetMessage());
} catch (Exception $e) {
die(exception_dump($e));
}
?>
-----------------------------------------
Hope this helps ...
K dot Londenberg at librics dot de
11-Feb-2005 01:34
11-Feb-2005 01:34
I wrote a wrapper class for mysqli which adds exception handling to the code. It's based on the second tutorial at zend.com
It partially relies on another small library which enables translation from php-warnings to exceptions, so I'm going to include this code as well.
-------safe_mysqli.php------------
<?php
//define('SQL_DEBUG', 1); // uncomment to always dump sql statement in Exception message (for debugging purposes only)
require_once('exceptions.php');
install_exception_errorhandler(); // Translates PHP Warnings and User-Errors into corresponding Exceptions
class DBException extends Exception {}; // Base class for Database Exceptions
class DBConnectException extends DBException {} // Thrown when a Database connection fails
// Thrown when problems with the execution or preparation of an SQL Query occurs.
class SQLException extends DBException {
private $sql;
private $sqlerrmsg;
function __construct($msg, $code, $sql) {
/* Pass all arguments passed to the constructor on to the parent's constructor */
$this->sqlerrmsg = $msg;
if (defined('SQL_DEBUG')) {
$msg .= " in '$sql'";
}
parent::__construct($msg, $code);
$this->sql = $sql;
}
function getSQL() {
return $this->sql;
}
function getSQLError() {
return $this->sqlerrmsg;
}
}
// Helper function for constructor
function add_single_quotes($arg)
{
return "'" . addcslashes($arg, "'\\") . "'";
}
// Written by Kai Londenberg K.Londenberg (at) librics (dot) de
// http://www.librics.de/ based on an example at zend.com
// mysqli wrapper class with exception support
class safe_mysqli extends mysqli {
function __construct() {
/* Pass all arguments passed to the constructor on to the parent's constructor */
$args = func_get_args();
try {
eval("parent::__construct(" . join(',', array_map('add_single_quotes', $args)) . ");");
} catch (WarningException $we) {
if(mysqli_connect_error()){
throw new DBConnectException(mysqli_connect_error(), mysqli_connect_errno());
/* Throw an error if the connection fails */
}
}
}
function query($query) {
$result = parent::query($query);
if(mysqli_error($this)){
throw new SQLException(mysqli_error($this), mysqli_errno($this), $query);
}
return $result;
}
function prepare($query) {
$result = parent::prepare($query);
if(mysqli_error($this)){
throw new SQLException(mysqli_error($this), mysqli_errno($this), $query);
}
return $result;
}
}
?>
php at arwin dot net
03-Feb-2005 07:32
03-Feb-2005 07:32
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.
roberto at spadim dot com dot br
07-Dec-2004 11:34
07-Dec-2004 11:34
Mysqli is slower them mysql extension if you not use pre compiled statments!
Yair Lapin
10-Nov-2004 11:28
10-Nov-2004 11:28
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
14-Sep-2004 01:44
14-Sep-2004 01:44
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.
severin dot kacianka at aon dot at
13-Aug-2004 06:55
13-Aug-2004 06:55
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
ajp at aripollak dot com
20-Jul-2004 07:50
20-Jul-2004 07:50
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()
philip
18-Jun-2004 06:51
18-Jun-2004 06:51
A couple tutorials on the subject of mysqli:
* http://www.zend.com/php5/articles/php5-mysqli.php
* http://www.zend.com/php5/articles/php5-mysqli2.php
phoeniks[at]nm[dot]ru
08-Apr-2004 06:21
08-Apr-2004 06:21
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);
}
?>