| PHP is a server-side, HTML-embedded scripting language that may be used to create dynamic Web pages. It is available for most operating systems and Web servers, and can access most common databases, including MySQL. PHP may be run as a separate program or compiled as a module for use with a Web server. PHP provides three different MySQL API extensions: Section 22.9.3, "MySQL Improved Extension (Mysqli)": Stands for "MySQL, Improved"; this extension is available as of PHP 5.0.0. It is intended for use with MySQL 4.1.1 and later. This extension fully supports the authentication protocol used in MySQL 5.0, as well as the Prepared Statements and Multiple Statements APIs. In addition, this extension provides an advanced, object-oriented programming interface. Section 22.9.4, "MySQL Functions (PDO_MYSQL) (MySQL (PDO))": Not its own API, but instead it's a MySQL driver for the PHP database abstraction layer PDO (PHP Data Objects). The PDO MySQL driver sits in the layer below PDO itself, and provides MySQL-specific functionality. This extension is available as of PHP 5.1.0. Section 22.9.2, "Original MySQL API (Mysql)": Available for PHP versions 4 and 5, this extension is intended for use with MySQL versions prior to MySQL 4.1. This extension does not support the improved authentication protocol used in MySQL 4.1, nor does it support prepared statements or multiple statements. To use this extension with MySQL 4.1, you will likely configure the MySQL server to set the old_passwords system variable to 1 (see Section C.5.2.4, "Client does not support authentication protocol"). This extension is deprecated as of PHP 5.5.0, and its use is highly discouraged. All users are encouraged to migrate to either mysqli or PDO_MySQL. For further information, see Section 22.9.1.3, "Choosing an API".
The PHP distribution and documentation are available from the PHP Web site. Portions of this section are Copyright (c) 1997-2012 the PHP Documentation Group This material may be distributed only subject to the terms and conditions set forth in the Creative Commons Attribution 3.0 License or later. A copy of the Creative Commons Attribution 3.0 license is distributed with this manual. The latest version is presently available at http://creativecommons.org/licenses/by/3.0/. 22.9.1. Overview of the MySQL PHP drivers Copyright 1997-2012 the PHP Documentation Group. There are three PHP APIs for accessing the MySQL database. This guide explains the terminology used to describe each API, information about choosing which API to use, and also information to help choose which MySQL library to use with the API. 22.9.1.2. Terminology overview Copyright 1997-2012 the PHP Documentation Group. This section provides an introduction to the options available to you when developing a PHP application that needs to interact with a MySQL database. What is an API? An Application Programming Interface, or API, defines the classes, methods, functions and variables that your application will need to call in order to carry out its desired task. In the case of PHP applications that need to communicate with databases the necessary APIs are usually exposed via PHP extensions. APIs can be procedural or object-oriented. With a procedural API you call functions to carry out tasks, with the object-oriented API you instantiate classes and then call methods on the resulting objects. Of the two the latter is usually the preferred interface, as it is more modern and leads to better organized code. When writing PHP applications that need to connect to the MySQL server there are several API options available. This document discusses what is available and how to select the best solution for your application. What is a Connector? In the MySQL documentation, the term connector refers to a piece of software that allows your application to connect to the MySQL database server. MySQL provides connectors for a variety of languages, including PHP. If your PHP application needs to communicate with a database server you will need to write PHP code to perform such activities as connecting to the database server, querying the database and other database-related functions. Software is required to provide the API that your PHP application will use, and also handle the communication between your application and the database server, possibly using other intermediate libraries where necessary. This software is known generically as a connector, as it allows your application to connect to a database server. What is a Driver? A driver is a piece of software designed to communicate with a specific type of database server. The driver may also call a library, such as the MySQL Client Library or the MySQL Native Driver. These libraries implement the low-level protocol used to communicate with the MySQL database server. By way of an example, the PHP Data Objects (PDO) database abstraction layer may use one of several database-specific drivers. One of the drivers it has available is the PDO MYSQL driver, which allows it to interface with the MySQL server. Sometimes people use the terms connector and driver interchangeably, this can be confusing. In the MySQL-related documentation the term "driver" is reserved for software that provides the database-specific part of a connector package. What is an Extension? In the PHP documentation you will come across another term - extension. The PHP code consists of a core, with optional extensions to the core functionality. PHP's MySQL-related extensions, such as the mysqli extension, and the mysql extension, are implemented using the PHP extension framework. An extension typically exposes an API to the PHP programmer, to allow its facilities to be used programmatically. However, some extensions which use the PHP extension framework do not expose an API to the PHP programmer. The PDO MySQL driver extension, for example, does not expose an API to the PHP programmer, but provides an interface to the PDO layer above it. The terms API and extension should not be taken to mean the same thing, as an extension may not necessarily expose an API to the programmer. 22.9.1.3. Choosing an API Copyright 1997-2012 the PHP Documentation Group. PHP offers three different APIs to connect to MySQL. Below we show the APIs provided by the mysql, mysqli, and PDO extensions. Each code snippet creates a connection to a MySQL server running on "example.com" using the username "user" and the password "password". And a query is run to greet the user. Example 22.13. Comparing the three MySQL APIs <?php// mysqli$mysqli = new mysqli("example.com", "user", "password", "database");$result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");$row = $result->fetch_assoc();echo htmlentities($row['_message']);// PDO$pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password');$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");$row = $statement->fetch(PDO::FETCH_ASSOC);echo htmlentities($row['_message']);// mysql$c = mysql_connect("example.com", "user", "password");mysql_select_db("database");$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");$row = mysql_fetch_assoc($result);echo htmlentities($row['_message']);?> Recommended API It is recommended to use either the mysqli or PDO_MySQL extensions. It is not recommended to use the old mysql extension for new development, as it has been deprecated as of PHP 5.5.0 and will be removed in the future. A detailed feature comparison matrix is provided below. The overall performance of all three extensions is considered to be about the same. Although the performance of the extension contributes only a fraction of the total run time of a PHP web request. Often, the impact is as low as 0.1%. Feature comparison 22.9.1.4. Choosing a library Copyright 1997-2012 the PHP Documentation Group. The mysqli, PDO_MySQL and mysql PHP extensions are lightweight wrappers on top of a C client library. The extensions can either use the mysqlnd library or the libmysql library. Choosing a library is a compile time decision. The mysqlnd library is part of the PHP distribution since 5.3.0. It offers features like lazy connections and query caching, features that are not available with libmysql, so using the built-in mysqlnd library is highly recommended. See the mysqlnd documentation for additional details, and a listing of features and functionality that it offers. Example 22.14. Configure commands for using mysqlnd or libmysql // Recommended, compiles with mysqlnd$ ./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd// Not recommended, compiles with libmysql$ ./configure --with-mysqli=/path/to/mysql_config --with-pdo-mysql=/path/to/mysql_config--with-mysql=/path/to/mysql_config Library feature comparison It is recommended to use the mysqlnd library instead of the MySQL Client Server library (libmysql). Both libraries are supported and constantly being improved. Copyright 1997-2012 the PHP Documentation Group. These concepts are specific to the MySQL drivers for PHP. 22.9.1.5.1. Buffered and Unbuffered queries Copyright 1997-2012 the PHP Documentation Group. Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP in is then kept in the memory of the PHP process. This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer. It also allows issuing further queries on the same connection while working on the result set. The downside of the buffered mode is that larger result sets might require quite a lot memory. The memory will be kept occupied till all references to the result set are unset or the result set was explicitly freed, which will automatically happen during request end the latest. The terminology "store result" is also used for buffered mode, as the whole result set is stored at once. Note When using libmysql as library PHP's memory limit won't count the memory used for result sets unless the data is fetched into PHP variables. With mysqlnd the memory accounted for will include the full result set. Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as "use result". Following these characteristics buffered queries should be used in cases where you expect only a limited result set or need to know the amount of returned rows before reading all rows. Unbuffered mode should be used when you expect larger results. Because buffered queries are the default, the examples below will demonstrate how to execute unbuffered queries with each API. Example 22.15. Unbuffered query example: mysqli <?php$mysqli = new mysqli("localhost", "my_user", "my_password", "world");$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);if ($uresult) { while ($row = $uresult->fetch_assoc()) { echo $row['Name'] . PHP_EOL; }}$uresult->close();?>Example 22.16. Unbuffered query example: pdo_mysql <?php$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);$uresult = $pdo->query("SELECT Name FROM City");if ($uresult) { while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) { echo $row['Name'] . PHP_EOL; }}?>Example 22.17. Unbuffered query example: mysql <?php$conn = mysql_connect("localhost", "my_user", "my_pass");$db = mysql_select_db("world");$uresult = mysql_unbuffered_query("SELECT Name FROM City");if ($uresult) { while ($row = mysql_fetch_assoc($uresult)) { echo $row['Name'] . PHP_EOL; }}?>22.9.1.5.2. Character sets Copyright 1997-2012 the PHP Documentation Group. Ideally a proper character set will be set at the server level, and doing this is described within the Character Set Configuration section of the MySQL Server manual. Alternatively, each MySQL API offers a method to set the character set at runtime. The character set and character escaping The character set should be understood and defined, as it has an affect on every action, and includes security implications. For example, the escaping mechanism (e.g., mysqli_real_escape_string for mysqli, mysql_real_escape_string for mysql, and PDO::quote for PDO_MySQL) will adhere to this setting. It is important to realize that these functions will not use the character set that is defined with a query, so for example the following will not have an effect on them: Example 22.18. Problems with setting the character set with SQL <?php$mysqli = new mysqli("localhost", "my_user", "my_password", "world");// Will not affect $mysqli->real_escape_string();$mysqli->query("SET NAMES utf8");// Will not affect $mysqli->real_escape_string();$mysqli->query("SET CHARACTER SET utf8");// But, this will affect $mysqli->real_escape_string();$mysqli->set_charset('utf8');?> Below are examples that demonstrate how to properly alter the character set at runtime using each each API. Example 22.19. Setting the character set example: mysqli <?php$mysqli = new mysqli("localhost", "my_user", "my_password", "world");if (!$mysqli->set_charset('utf8')) { printf("Error loading character set utf8: %s\n", $mysqli->error);} else { printf("Current character set: %s\n", $mysqli->character_set_name());}print_r( $mysqli->get_charset() );?>Example 22.20. Setting the character set example:pdo_mysql Note: This only works as of PHP 5.3.6. <?php$pdo = new PDO("mysql:host=localhost;dbname=world;charset=utf8", 'my_user', 'my_pass');?>Example 22.21. Setting the character set example: mysql <?php$conn = mysql_connect("localhost", "my_user", "my_pass");$db = mysql_select_db("world");if (!mysql_set_charset('utf8', $conn)) { echo "Error: Unable to set the character set.\n"; exit;}echo 'Your current character set is: ' . mysql_client_encoding($conn);?>22.9.2. Original MySQL API (Mysql) Copyright 1997-2012 the PHP Documentation Group. This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API. These functions allow you to access MySQL database servers. More information about MySQL can be found at http://www.mysql.com/. Documentation for MySQL can be found at http://dev.mysql.com/doc/. 22.9.2.1. Installing/Configuring Copyright 1997-2012 the PHP Documentation Group. Copyright 1997-2012 the PHP Documentation Group. For compiling, simply use the --with-mysql[=DIR] configuration option where the optional [DIR] points to the MySQL installation directory. Although this MySQL extension is compatible with MySQL 4.1.0 and greater, it doesn't support the extra functionality that these versions provide. For that, use the MySQLi extension. 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. 22.9.2.1.2.1. Installation on Linux Systems Copyright 1997-2012 the PHP Documentation Group. Note: [DIR] is the path to the MySQL client library files (headers and libraries), which can be downloaded from MySQL. Table 22.32. ext/mysql compile time support matrix | PHP Version | Default | Configure Options: mysqlnd | Configure Options: libmysql | Changelog |
|---|
| 4.x.x | libmysql | Not Available | --without-mysql to disable | MySQL enabled by default, MySQL client libraries are bundled | | 5.0.x, 5.1.x, 5.2.x | libmysql | Not Available | --with-mysql=[DIR] | MySQL is no longer enabled by default, and the MySQL client libraries are nolonger bundled | | 5.3.x | libmysql | --with-mysql=mysqlnd | --with-mysql=[DIR] | mysqlnd is now available | | 5.4.x | mysqlnd | --with-mysql | --with-mysql=[DIR] | mysqlnd is now the default |
22.9.2.1.2.2. Installation on Windows Systems Copyright 1997-2012 the PHP Documentation Group. 22.9.2.1.2.2.2. PHP 5.0.x, 5.1.x, 5.2.x Copyright 1997-2012 the PHP Documentation Group. MySQL is no longer enabled by default, so the php_mysql.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. As with enabling any PHP extension (such as php_mysql.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_mysql.dll'", this is because php_mysql.dll and/or libmysql.dll cannot be found by the system. 22.9.2.1.2.3. MySQL Installation Notes Copyright 1997-2012 the PHP Documentation Group. Warning Crashes and startup problems of PHP may be encountered when loading this extension in conjunction with the recode extension. See the recode extension for more information. Note If you need charsets other than latin (default), you have to install external (not bundled) libmysql with compiled charset support. 22.9.2.1.3. Runtime Configuration Copyright 1997-2012 the PHP Documentation Group. The behaviour of these functions is affected by settings in php.ini. Table 22.33. MySQL Configuration Options Here's a short explanation of the configuration directives. - mysql.allow_local_infile integer
Allow accessing, from PHP's perspective, local files with LOAD DATA statements - mysql.allow_persistent boolean
Whether to allow persistent connections to MySQL. - mysql.max_persistent integer
The maximum number of persistent MySQL connections per process. - mysql.max_links integer
The maximum number of MySQL connections per process, including persistent connections. - mysql.trace_mode boolean
Trace mode. When mysql.trace_mode is enabled, warnings for table/index scans, non free result sets, and SQL-Errors will be displayed. (Introduced in PHP 4.3.0) - mysql.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. - mysql.default_socket string
The default socket name to use when connecting to a local database server if no other socket name is specified. - mysql.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 SQL safe mode. - mysql.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 SQL safe mode. - mysql.default_password string
The default password to use when connecting to the database server if no other password is specified. Doesn't apply in SQL safe mode. - mysql.connect_timeout integer
Connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server.
22.9.2.1.4. Resource Types Copyright 1997-2012 the PHP Documentation Group. There are two resource types used in the MySQL module. The first one is the link identifier for a database connection, the second a resource which holds the result of a query. Copyright 1997-2012 the PHP Documentation Group. The following changes have been made to classes/functions/methods of this extension. General Changelog for the ext/mysql extension This changelog references the ext/mysql extension. Changes to existing functions The following list is a compilation of changelog entries from the ext/mysql functions. The following is a list of changes to the entire ext/mysql extension. 22.9.2.3. Predefined Constants Copyright 1997-2012 the PHP Documentation Group. The constants below are defined by this extension, andwill only be available when the extension has eitherbeen compiled into PHP or dynamically loaded at runtime. Since PHP 4.3.0 it is possible to specify additional client flags for the mysql_connect and mysql_pconnect functions. The following constants are defined: Table 22.34. MySQL client constants | Constant | Description |
|---|
| MYSQL_CLIENT_COMPRESS | Use compression protocol | | MYSQL_CLIENT_IGNORE_SPACE | Allow space after function names | | MYSQL_CLIENT_INTERACTIVE | Allow interactive_timeout seconds (instead of wait_timeout) of inactivity before closing the connection. | | MYSQL_CLIENT_SSL | Use SSL encryption. This flag is only available with version 4.x of the MySQL client library or newer. Version 3.23.x is bundledboth with PHP 4 and Windows binaries of PHP 5. |
The function mysql_fetch_array uses a constant for the different types of result arrays. The following constants are defined: Table 22.35. MySQL fetch constants | Constant | Description |
|---|
| MYSQL_ASSOC | Columns are returned into the array having the fieldname as the array index. | | MYSQL_BOTH | Columns are returned into the array having both a numerical index and the fieldname as the array index. | | MYSQL_NUM | Columns are returned into the array having a numerical index to the fields. This index starts with 0, the first field in theresult. |
Copyright 1997-2012 the PHP Documentation Group. 22.9.2.4.1. MySQL extension overview example Copyright 1997-2012 the PHP Documentation Group. This simple example shows how to connect, execute a query, print resulting rows and disconnect from a MySQL database. Example 22.22. MySQL extension overview example <?php// Connecting, selecting database$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not connect: ' . mysql_error());echo 'Connected successfully';mysql_select_db('my_database') or die('Could not select database');// Performing SQL query$query = 'SELECT * FROM my_table';$result = mysql_query($query) or die('Query failed: ' . mysql_error());// Printing results in HTMLecho "<table>\n";while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { echo "\t<tr>\n"; foreach ($line as $col_value) { echo "\t\t<td>$col_value</td>\n"; } echo "\t</tr>\n";}echo "</table>\n";// Free resultsetmysql_free_result($result);// Closing connectionmysql_close($link);?>22.9.2.5. MySQL Functions Copyright 1997-2012 the PHP Documentation Group.
Note Most MySQL functions accept link_identifier as the last optional parameter. If it is not provided, last opened connection is used. If it doesn't exist, connection is tried to establish with default parameters defined in php.ini. If it is not successful, functions return FALSE . 22.9.2.5.1. mysql_affected_rows Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description int mysql_affected_rows( resource link_identifier= =NULL); Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns the number of affected rows on success, and -1 if the last query failed. If the last query was a DELETE query with no WHERE clause, all of the records will have been deleted from the table but this function will return zero with MySQL versions prior to 4.1.2. When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows may not actually equal the number of rows matched, only the number of rows that were literally affected by the query. The REPLACE statement first deletes the record with the same primary key and then inserts the new record. This function returns the number of deleted records plus the number of inserted records. In the case of "INSERT ... ON DUPLICATE KEY UPDATE" queries, the return value will be 1 if an insert was performed, or 2 for an update of an existing row. Examples Example 22.23. mysql_affected_rowsexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}mysql_select_db('mydb');/* this should return the correct numbers of deleted records */mysql_query('DELETE FROM mytable WHERE id < 10');printf("Records deleted: %d\n", mysql_affected_rows());/* with a where clause that is never true, it should return 0 */mysql_query('DELETE FROM mytable WHERE 0');printf("Records deleted: %d\n", mysql_affected_rows());?> The above example will output something similar to: Records deleted: 10Records deleted: 0 Example 22.24. mysql_affected_rowsexample using transactions <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}mysql_select_db('mydb');/* Update records */mysql_query("UPDATE mytable SET used=1 WHERE id < 10");printf ("Updated records: %d\n", mysql_affected_rows());mysql_query("COMMIT");?> The above example will output something similar to: Updated Records: 10 Notes Transactions If you are using transactions, you need to call mysql_affected_rows after your INSERT, UPDATE, or DELETE query, not after the COMMIT. SELECT Statements To retrieve the number of rows returned by a SELECT, it is possible to use mysql_num_rows. Cascaded Foreign Keys mysql_affected_rows does not count rows affected implicitly through the use of ON DELETE CASCADE and/or ON UPDATE CASCADE in foreign key constraints. See Also 22.9.2.5.2. mysql_client_encoding Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_client_encoding( resource link_identifier= =NULL); Retrieves the character_set variable from MySQL. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns the default character set name for the current connection. Examples Example 22.25. mysql_client_encodingexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$charset = mysql_client_encoding($link);echo "The current character set is: $charset\n";?> The above example will output something similar to: The current character set is: latin1 See Also Copyright 1997-2012 the PHP Documentation Group. mysql_close Close MySQL connection
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description bool mysql_close( resource link_identifier= =NULL); mysql_close closes the non-persistent connection to the MySQL server that's associated with the specified link identifier. If link_identifier isn't specified, the last opened link is used. Using mysql_close isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. See also freeing resources. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns TRUE on success or FALSE on failure. Examples Example 22.26. mysql_closeexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);?> The above example will output: Connected successfully Notes See Also 22.9.2.5.4. mysql_connect Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description resource mysql_connect( string server= =ini_get("mysql.default_host"), string username= =ini_get("mysql.default_user"), string password= =ini_get("mysql.default_password"), bool new_link= =false, int client_flags= =0); Opens or reuses a connection to a MySQL server. Parameters - server
The MySQL server. It can also include a port number. e.g. "hostname:port" or a path to a local socket e.g. ":/path/to/socket" for the localhost. If the PHP directive mysql.default_host is undefined (default), then the default value is 'localhost:3306'. In SQL safe mode, this parameter is ignored and value 'localhost:3306' is always used. - username
The username. Default value is defined by mysql.default_user. In SQL safe mode, this parameter is ignored and the name of the user that owns the server process is used. - password
The password. Default value is defined by mysql.default_password. In SQL safe mode, this parameter is ignored and empty password is used. - new_link
If a second call is made to mysql_connect with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect always open a new link, even if mysql_connect was called before with the same parameters. In SQL safe mode, this parameter is ignored. - client_flags
The client_flags parameter can be a combination of the following constants: 128 (enable LOAD DATA LOCAL handling), MYSQL_CLIENT_SSL , MYSQL_CLIENT_COMPRESS , MYSQL_CLIENT_IGNORE_SPACE or MYSQL_CLIENT_INTERACTIVE . Read the section about Table 22.34, "MySQL client constants" for further information. In SQL safe mode, this parameter is ignored.
Return Values Returns a MySQL link identifier on success or FALSE on failure. Changelog Examples Example 22.27. mysql_connectexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);?>Example 22.28. mysql_connectexample using hostname:port syntax <?php// we connect to example.com and port 3307$link = mysql_connect('example.com:3307', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);// we connect to localhost at port 3307$link = mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);?>Example 22.29. mysql_connectexample using ":/path/to/socket" syntax <?php// we connect to localhost and socket e.g. /tmp/mysql.sock// variant 1: omit localhost$link = mysql_connect(':/tmp/mysql', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);// variant 2: with localhost$link = mysql_connect('localhost:/tmp/mysql.sock', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($link);?> Notes Note Whenever you specify "localhost" or "localhost:port" as server, the MySQL client library will override this and try to connect to a local socket (named pipe on Windows). If you want to use TCP/IP, use "127.0.0.1" instead of "localhost". If the MySQL client library tries to connect to the wrong local socket, you should set the correct path as mysql.default_host string in your PHP configuration and leave the server field blank. Note The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close. Note You can suppress the error message on failure by prepending a @ to the function name. Note Error "Can't create TCP/IP socket (10106)" usually means that the variables_order configure directive doesn't contain character E. On Windows, if the environment is not copied the SYSTEMROOT environment variable won't be available and PHP will have problems loading Winsock. See Also 22.9.2.5.5. mysql_create_db Copyright 1997-2012 the PHP Documentation Group. mysql_create_db Create a MySQL database
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description bool mysql_create_db( string database_name, resource link_identifier= =NULL); mysql_create_db attempts to create a new database on the server associated with the specified link identifier. Parameters - database_name
The name of the database being created. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns TRUE on success or FALSE on failure. Examples Example 22.30. mysql_create_dbalternative example The function mysql_create_db is deprecated. It is preferable to use mysql_query to issue an sql CREATE DATABASE statement instead. <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}$sql = 'CREATE DATABASE my_db';if (mysql_query($sql, $link)) { echo "Database my_db created successfully\n";} else { echo 'Error creating database: ' . mysql_error() . "\n";}?> The above example will output something similar to: Database my_db created successfully Notes Note For backward compatibility, the following deprecated alias may be used: mysql_createdb Note This function will not be available if the MySQL extension was built against a MySQL 4.x client library. See Also 22.9.2.5.6. mysql_data_seek Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description bool mysql_data_seek( resource result, int row_number); mysql_data_seek moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to a MySQL fetch function, such as mysql_fetch_assoc, would return that row. row_number starts at 0. The row_number should be a value in the range from 0 to mysql_num_rows - 1. However if the result set is empty (mysql_num_rows == 0), a seek to 0 will fail with a E_WARNING and mysql_data_seek will return FALSE . Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - row_number
The desired row number of the new result pointer.
Return Values Returns TRUE on success or FALSE on failure. Examples Example 22.31. mysql_data_seekexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}$db_selected = mysql_select_db('sample_db');if (!$db_selected) { die('Could not select database: ' . mysql_error());}$query = 'SELECT last_name, first_name FROM friends';$result = mysql_query($query);if (!$result) { die('Query failed: ' . mysql_error());}/* fetch rows in reverse order */for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) { if (!mysql_data_seek($result, $i)) { echo "Cannot seek to row $i: " . mysql_error() . "\n"; continue; } if (!($row = mysql_fetch_assoc($result))) { continue; } echo $row['last_name'] . ' ' . $row['first_name'] . "<br />\n";}mysql_free_result($result);?> Notes See Also 22.9.2.5.7. mysql_db_name Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_db_name( resource result, int row, mixed field= =NULL); Retrieve the database name from a call to mysql_list_dbs. Parameters - result
The result pointer from a call to mysql_list_dbs. - row
The index into the result set. - field
The field name.
Return Values Returns the database name on success, and FALSE on failure. If FALSE is returned, use mysql_error to determine the nature of the error. Changelog Examples Example 22.32. mysql_db_nameexample <?phperror_reporting(E_ALL);$link = mysql_connect('dbhost', 'username', 'password');$db_list = mysql_list_dbs($link);$i = 0;$cnt = mysql_num_rows($db_list);while ($i < $cnt) { echo mysql_db_name($db_list, $i) . "\n"; $i++;}?> Notes Note For backward compatibility, the following deprecated alias may be used: mysql_dbname See Also 22.9.2.5.8. mysql_db_query Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description resource mysql_db_query( string database, string query, resource link_identifier= =NULL); mysql_db_query selects a database, and executes a query on it. Warning This function has beenDEPRECATED as of PHP 5.3.0. Relying on this feature ishighly discouraged. Parameters - database
The name of the database that will be selected. - query
The MySQL query. Data inside the query should be properly escaped. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns a positive MySQL result resource to the query result, or FALSE on error. The function also returns TRUE / FALSE for INSERT/UPDATE/DELETE queries to indicate success/failure. Changelog Examples Example 22.33. mysql_db_queryalternative example <?phpif (!$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) { echo 'Could not connect to mysql'; exit;}if (!mysql_select_db('mysql_dbname', $link)) { echo 'Could not select database'; exit;}$sql = 'SELECT foo FROM bar WHERE id = 42';$result = mysql_query($sql, $link);if (!$result) { echo "DB Error, could not query the database\n"; echo 'MySQL Error: ' . mysql_error(); exit;}while ($row = mysql_fetch_assoc($result)) { echo $row['foo'];}mysql_free_result($result);?> Notes Note Be aware that this function does NOT switch back to the database you were connected before. In other words, you can't use this function to temporarily run a sql query on another database, you would have to manually switch back. Users are strongly encouraged to use the database.table syntax in their sql queries or mysql_select_db instead of this function. See Also 22.9.2.5.9. mysql_drop_db Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: | Execute a DROP DATABASE query |
Description bool mysql_drop_db( string database_name, resource link_identifier= =NULL); mysql_drop_db attempts to drop (remove) an entire database from the server associated with the specified link identifier. This function is deprecated, it is preferable to use mysql_query to issue an sql DROP DATABASE statement instead. Parameters - database_name
The name of the database that will be deleted. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns TRUE on success or FALSE on failure. Examples Example 22.34. mysql_drop_dbalternative example <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}$sql = 'DROP DATABASE my_db';if (mysql_query($sql, $link)) { echo "Database my_db was successfully dropped\n";} else { echo 'Error dropping database: ' . mysql_error() . "\n";}?> Notes Warning This function will not be available if the MySQL extension was built against a MySQL 4.x client library. Note For backward compatibility, the following deprecated alias may be used: mysql_dropdb See Also Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description int mysql_errno( resource link_identifier= =NULL); Returns the error number from the last MySQL function. Errors coming back from the MySQL database backend no longer issue warnings. Instead, use mysql_errno to retrieve the error code. Note that this function only returns the error code from the most recently executed MySQL function (not including mysql_error and mysql_errno), so if you want to use it, make sure you check the value before calling another MySQL function. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns the error number from the last MySQL function, or 0 (zero) if no error occurred. Examples Example 22.35. mysql_errnoexample <?php$link = mysql_connect("localhost", "mysql_user", "mysql_password");if (!mysql_select_db("nonexistentdb", $link)) { echo mysql_errno($link) . ": " . mysql_error($link). "\n";}mysql_select_db("kossu", $link);if (!mysql_query("SELECT * FROM nonexistenttable", $link)) { echo mysql_errno($link) . ": " . mysql_error($link) . "\n";}?> The above example will output something similar to: 1049: Unknown database 'nonexistentdb'1146: Table 'kossu.nonexistenttable' doesn't exist See Also Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_error( resource link_identifier= =NULL); Returns the error text from the last MySQL function. Errors coming back from the MySQL database backend no longer issue warnings. Instead, use mysql_error to retrieve the error text. Note that this function only returns the error text from the most recently executed MySQL function (not including mysql_error and mysql_errno), so if you want to use it, make sure you check the value before calling another MySQL function. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns the error text from the last MySQL function, or '' (empty string) if no error occurred. Examples Example 22.36. mysql_errorexample <?php$link = mysql_connect("localhost", "mysql_user", "mysql_password");mysql_select_db("nonexistentdb", $link);echo mysql_errno($link) . ": " . mysql_error($link). "\n";mysql_select_db("kossu", $link);mysql_query("SELECT * FROM nonexistenttable", $link);echo mysql_errno($link) . ": " . mysql_error($link) . "\n";?> The above example will output something similar to: 1049: Unknown database 'nonexistentdb'1146: Table 'kossu.nonexistenttable' doesn't exist See Also 22.9.2.5.12. mysql_escape_string Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_escape_string( string unescaped_string); This function will escape the unescaped_string, so that it is safe to place it in a mysql_query. This function is deprecated. This function is identical to mysql_real_escape_string except that mysql_real_escape_string takes a connection handler and escapes the string according to the current character set. mysql_escape_string does not take a connection argument and does not respect the current charset setting. Warning This function has beenDEPRECATED as of PHP 5.3.0. Relying on this feature ishighly discouraged. Parameters - unescaped_string
The string that is to be escaped.
Return Values Returns the escaped string. Changelog Examples Example 22.37. mysql_escape_stringexample <?php$item = "Zak's Laptop";$escaped_item = mysql_escape_string($item);printf("Escaped string: %s\n", $escaped_item);?> The above example will output: Escaped string: Zak\'s Laptop Notes See Also 22.9.2.5.13. mysql_fetch_array Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description array mysql_fetch_array( resource result, int result_type= =MYSQL_BOTH); Returns an array that corresponds to the fetched row and moves the internal data pointer ahead. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - result_type
The type of array that is to be fetched. It's a constant and can take the following values: MYSQL_ASSOC , MYSQL_NUM , and MYSQL_BOTH .
Return Values Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The type of returned array depends on how result_type is defined. By using MYSQL_BOTH (default), you'll get an array with both associative and number indices. Using MYSQL_ASSOC , you only get associative indices (as mysql_fetch_assoc works), using MYSQL_NUM , you only get number indices (as mysql_fetch_row works). If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you must use the numeric index of the column or make an alias for the column. For aliased columns, you cannot access the contents with the original column name. Examples Example 22.38. Query with aliased duplicate field names SELECT table1.field AS foo, table2.field AS bar FROM table1, table2 Example 22.39. mysql_fetch_array withMYSQL_NUM <?phpmysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error());mysql_select_db("mydb");$result = mysql_query("SELECT id, name FROM mytable");while ($row = mysql_fetch_array($result, MYSQL_NUM)) { printf("ID: %s Name: %s", $row[0], $row[1]); }mysql_free_result($result);?>Example 22.40. mysql_fetch_array withMYSQL_ASSOC <?phpmysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error());mysql_select_db("mydb");$result = mysql_query("SELECT id, name FROM mytable");while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { printf("ID: %s Name: %s", $row["id"], $row["name"]);}mysql_free_result($result);?>Example 22.41. mysql_fetch_array withMYSQL_BOTH <?phpmysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error());mysql_select_db("mydb");$result = mysql_query("SELECT id, name FROM mytable");while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { printf ("ID: %s Name: %s", $row[0], $row["name"]);}mysql_free_result($result);?> Notes Performance An important thing to note is that using mysql_fetch_array is not significantly slower than using mysql_fetch_row, while it provides a significant added value. Note Field names returned by this functionare case-sensitive. Note This function sets NULL fields tothe PHP NULL value. See Also 22.9.2.5.14. mysql_fetch_assoc Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description array mysql_fetch_assoc( resource result); Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_assoc is equivalent to calling mysql_fetch_array with MYSQL_ASSOC for the optional second parameter. It only returns an associative array. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query.
Return Values Returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows. If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you either need to access the result with numeric indices by using mysql_fetch_row or add alias names. See the example at the mysql_fetch_array description about aliases. Examples Example 22.42. An expanded mysql_fetch_assocexample <?php$conn = mysql_connect("localhost", "mysql_user", "mysql_password");if (!$conn) { echo "Unable to connect to DB: " . mysql_error(); exit;}if (!mysql_select_db("mydbname")) { echo "Unable to select mydbname: " . mysql_error(); exit;}$sql = "SELECT id as userid, fullname, userstatus FROM sometable WHERE userstatus = 1";$result = mysql_query($sql);if (!$result) { echo "Could not successfully run query ($sql) from DB: " . mysql_error(); exit;}if (mysql_num_rows($result) == 0) { echo "No rows found, nothing to print so am exiting"; exit;}// While a row of data exists, put that row in $row as an associative array// Note: If you're expecting just one row, no need to use a loop// Note: If you put extract($row); inside the following loop, you'll// then create $userid, $fullname, and $userstatuswhile ($row = mysql_fetch_assoc($result)) { echo $row["userid"]; echo $row["fullname"]; echo $row["userstatus"];}mysql_free_result($result);?> Notes Performance An important thing to note is that using mysql_fetch_assoc is not significantly slower than using mysql_fetch_row, while it provides a significant added value. Note Field names returned by this functionare case-sensitive. Note This function sets NULL fields tothe PHP NULL value. See Also 22.9.2.5.15. mysql_fetch_field Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description object mysql_fetch_field( resource result, int field_offset= =0); Returns an object containing field information. This function can be used to obtain information about fields in the provided query result. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - field_offset
The numerical field offset. If the field offset is not specified, the next field that was not yet retrieved by this function is retrieved. The field_offset starts at 0.
Return Values Returns an object containing field information. The properties of the object are: - name - column name
- table - name of the table the column belongs to, which is the alias name if one is defined
- max_length - maximum length of the column
- not_null - 1 if the column cannot be NULL
- primary_key - 1 if the column is a primary key
- unique_key - 1 if the column is a unique key
- multiple_key - 1 if the column is a non-unique key
- numeric - 1 if the column is numeric
- blob - 1 if the column is a BLOB
- type - the type of the column
- unsigned - 1 if the column is unsigned
- zerofill - 1 if the column is zero-filled
Examples Example 22.43. mysql_fetch_fieldexample <?php$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$conn) { die('Could not connect: ' . mysql_error());}mysql_select_db('database');$result = mysql_query('select * from table');if (!$result) { die('Query failed: ' . mysql_error());}/* get column metadata */$i = 0;while ($i < mysql_num_fields($result)) { echo "Information for column $i:<br />\n"; $meta = mysql_fetch_field($result, $i); if (!$meta) { echo "No information available<br />\n"; } echo "<pre>blob: $meta->blobmax_length: $meta->max_lengthmultiple_key: $meta->multiple_keyname: $meta->namenot_null: $meta->not_nullnumeric: $meta->numericprimary_key: $meta->primary_keytable: $meta->tabletype: $meta->typeunique_key: $meta->unique_keyunsigned: $meta->unsignedzerofill: $meta->zerofill</pre>"; $i++;}mysql_free_result($result);?> Notes Note Field names returned by this functionare case-sensitive. Note If field or tablenames are aliased in the SQL query the aliased name will be returned. The original name can be retrieved for instance by using mysqli_result::fetch_field. See Also 22.9.2.5.16. mysql_fetch_lengths Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description array mysql_fetch_lengths( resource result); Returns an array that corresponds to the lengths of each field in the last row fetched by MySQL. mysql_fetch_lengths stores the lengths of each result column in the last row returned by mysql_fetch_row, mysql_fetch_assoc, mysql_fetch_array, and mysql_fetch_object in an array, starting at offset 0. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query.
Return Values An array of lengths on success or FALSE on failure. Examples Example 22.44. A mysql_fetch_lengthsexample <?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;}$row = mysql_fetch_assoc($result);$lengths = mysql_fetch_lengths($result);print_r($row);print_r($lengths);?> The above example will output something similar to: Array( [id] => 42 [email] => [email protected])Array( [0] => 2 [1] => 16) See Also 22.9.2.5.17. mysql_fetch_object Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description object mysql_fetch_object( resource result, string class_name, array params); Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - class_name
The name of the class to instantiate, set the properties of and return. If not specified, a stdClass object is returned. - params
An optional array of parameters to pass to the constructor for class_name objects.
Return Values Returns an object with string properties that correspond to the fetched row, or FALSE if there are no more rows. Changelog Examples Example 22.45. mysql_fetch_objectexample <?phpmysql_connect("hostname", "user", "password");mysql_select_db("mydb");$result = mysql_query("select * from mytable");while ($row = mysql_fetch_object($result)) { echo $row->user_id; echo $row->fullname;}mysql_free_result($result);?>Example 22.46. mysql_fetch_objectexample <?phpclass foo { public $name;}mysql_connect("hostname", "user", "password");mysql_select_db("mydb");$result = mysql_query("select name from mytable limit 1");$obj = mysql_fetch_object($result, 'foo');var_dump($obj);?> Notes Note mysql_fetch_object is similar to mysql_fetch_array, with one difference - an object is returned, instead of an array. Indirectly, that means that you can only access the data by the field names, and not by their offsets (numbers are illegal property names). Note Field names returned by this functionare case-sensitive. Note This function sets NULL fields tothe PHP NULL value. See Also 22.9.2.5.18. mysql_fetch_row Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description array mysql_fetch_row( resource result); Returns a numerical array that corresponds to the fetched row and moves the internal data pointer ahead. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query.
Return Values Returns an numerical array of strings that corresponds to the fetched row, or FALSE if there are no more rows. mysql_fetch_row fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0. Examples Example 22.47. Fetching one row withmysql_fetch_row <?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;}$row = mysql_fetch_row($result);echo $row[0]; // 42echo $row[1]; // the email value?> Notes Note This function sets NULL fields tothe PHP NULL value. See Also 22.9.2.5.19. mysql_field_flags Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_field_flags( resource result, int field_offset); mysql_field_flags returns the field flags of the specified field. The flags are reported as a single word per flag separated by a single space, so that you can split the returned value using explode. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - field_offset
The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
Return Values Returns a string of flags associated with the result or FALSE on failure. The following flags are reported, if your version of MySQL is current enough to support them: "not_null", "primary_key", "unique_key", "multiple_key", "blob", "unsigned", "zerofill", "binary", "enum", "auto_increment" and "timestamp". Examples Example 22.48. A mysql_field_flagsexample <?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;}$flags = mysql_field_flags($result, 0);echo $flags;print_r(explode(' ', $flags));?> The above example will output something similar to: not_null primary_key auto_incrementArray( [0] => not_null [1] => primary_key [2] => auto_increment) Notes Note For backward compatibility, the following deprecated alias may be used: mysql_fieldflags See Also 22.9.2.5.20. mysql_field_len Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description int mysql_field_len( resource result, int field_offset); mysql_field_len returns the length of the specified field. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - field_offset
The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
Return Values The length of the specified field index on success or FALSE on failure. Examples Example 22.49. mysql_field_lenexample <?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;}// Will get the length of the id field as specified in the database// schema. $length = mysql_field_len($result, 0);echo $length;?> Notes Note For backward compatibility, the following deprecated alias may be used: mysql_fieldlen See Also 22.9.2.5.21. mysql_field_name Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_field_name( resource result, int field_offset); mysql_field_name returns the name of the specified field index. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - field_offset
The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
Return Values The name of the specified field index on success or FALSE on failure. Examples Example 22.50. mysql_field_nameexample <?php/* The users table consists of three fields: * user_id * username * password. */$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect to MySQL server: ' . mysql_error());}$dbname = 'mydb';$db_selected = mysql_select_db($dbname, $link);if (!$db_selected) { die("Could not set $dbname: " . mysql_error());}$res = mysql_query('select * from users', $link);echo mysql_field_name($res, 0) . "\n";echo mysql_field_name($res, 2);?> The above example will output: user_idpassword Notes Note Field names returned by this functionare case-sensitive. Note For backward compatibility, the following deprecated alias may be used: mysql_fieldname See Also 22.9.2.5.22. mysql_field_seek Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description bool mysql_field_seek( resource result, int field_offset); Seeks to the specified field offset. If the next call to mysql_fetch_field doesn't include a field offset, the field offset specified in mysql_field_seek will be returned. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - field_offset
The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
Return Values Returns TRUE on success or FALSE on failure. See Also 22.9.2.5.23. mysql_field_table Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_field_table( resource result, int field_offset); Returns the name of the table that the specified field is in. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - field_offset
The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
Return Values The name of the table on success. Examples Example 22.51. A mysql_field_tableexample <?php$query = "SELECT account.*, country.* FROM account, country WHERE country.name = 'Portugal'AND account.country_id = country.id";// get the result from the DB$result = mysql_query($query);// Lists the table name and then the field namefor ($i = 0; $i < mysql_num_fields($result); ++$i) { $table = mysql_field_table($result, $i); $field = mysql_field_name($result, $i); echo "$table: $field\n";}?> Notes Note For backward compatibility, the following deprecated alias may be used: mysql_fieldtable See Also 22.9.2.5.24. mysql_field_type Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_field_type( resource result, int field_offset); mysql_field_type is similar to the mysql_field_name function. The arguments are identical, but the field type is returned instead. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - field_offset
The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
Return Values The returned field type will be one of "int", "real", "string", "blob", and others as detailed in the MySQL documentation. Examples Example 22.52. mysql_field_typeexample <?phpmysql_connect("localhost", "mysql_username", "mysql_password");mysql_select_db("mysql");$result = mysql_query("SELECT * FROM func");$fields = mysql_num_fields($result);$rows = mysql_num_rows($result);$table = mysql_field_table($result, 0);echo "Your '" . $table . "' table has " . $fields . " fields and " . $rows . " record(s)\n";echo "The table has the following fields:\n";for ($i=0; $i < $fields; $i++) { $type = mysql_field_type($result, $i); $name = mysql_field_name($result, $i); $len = mysql_field_len($result, $i); $flags = mysql_field_flags($result, $i); echo $type . " " . $name . " " . $len . " " . $flags . "\n";}mysql_free_result($result);mysql_close();?> The above example will output something similar to: Your 'func' table has 4 fields and 1 record(s)The table has the following fields:string name 64 not_null primary_key binaryint ret 1 not_nullstring dl 128 not_nullstring type 9 not_null enum Notes Note For backward compatibility, the following deprecated alias may be used: mysql_fieldtype See Also 22.9.2.5.25. mysql_free_result Copyright 1997-2012 the PHP Documentation Group. mysql_free_result Free result memory
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description bool mysql_free_result( resource result); mysql_free_result will free all memory associated with the result identifier result. mysql_free_result only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script's execution. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query.
Return Values Returns TRUE on success or FALSE on failure. If a non-resource is used for the result, an error of level E_WARNING will be emitted. It's worth noting that mysql_query only returns a resource for SELECT, SHOW, EXPLAIN, and DESCRIBE queries. Examples Example 22.53. A mysql_free_resultexample <?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;}/* Use the result, assuming we're done with it afterwards */$row = mysql_fetch_assoc($result);/* Now we free up the result and continue on with our script */mysql_free_result($result);echo $row['id'];echo $row['email'];?> Notes Note For backward compatibility, the following deprecated alias may be used: mysql_freeresult See Also 22.9.2.5.26. mysql_get_client_info Copyright 1997-2012 the PHP Documentation Group. mysql_get_client_info Get MySQL client info
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_get_client_info(); mysql_get_client_info returns a string that represents the client library version. Return Values The MySQL client version. Examples Example 22.54. mysql_get_client_infoexample <?phpprintf("MySQL client info: %s\n", mysql_get_client_info());?> The above example will output something similar to: MySQL client info: 3.23.39 See Also 22.9.2.5.27. mysql_get_host_info Copyright 1997-2012 the PHP Documentation Group. mysql_get_host_info Get MySQL host info
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_get_host_info( resource link_identifier= =NULL); Describes the type of connection in use for the connection, including the server host name. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns a string describing the type of MySQL connection in use for the connection or FALSE on failure. Examples Example 22.55. mysql_get_host_infoexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}printf("MySQL host info: %s\n", mysql_get_host_info());?> The above example will output something similar to: MySQL host info: Localhost via UNIX socket See Also 22.9.2.5.28. mysql_get_proto_info Copyright 1997-2012 the PHP Documentation Group. mysql_get_proto_info Get MySQL protocol info
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description int mysql_get_proto_info( resource link_identifier= =NULL); Retrieves the MySQL protocol. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns the MySQL protocol on success or FALSE on failure. Examples Example 22.56. mysql_get_proto_infoexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}printf("MySQL protocol version: %s\n", mysql_get_proto_info());?> The above example will output something similar to: MySQL protocol version: 10 See Also 22.9.2.5.29. mysql_get_server_info Copyright 1997-2012 the PHP Documentation Group. mysql_get_server_info Get MySQL server info
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_get_server_info( resource link_identifier= =NULL); Retrieves the MySQL server version. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns the MySQL server version on success or FALSE on failure. Examples Example 22.57. mysql_get_server_infoexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}printf("MySQL server version: %s\n", mysql_get_server_info());?> The above example will output something similar to: MySQL server version: 4.0.1-alpha See Also Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_info( resource link_identifier= =NULL); Returns detailed information about the last query. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns information about the statement on success, or FALSE on failure. See the example below for which statements provide information, and what the returned value may look like. Statements that are not listed will return FALSE . Examples Example 22.58. Relevant MySQL Statements Statements that return string values. The numbers are only for illustrating purpose; their values will correspond to the query. INSERT INTO ... SELECT ...String format: Records: 23 Duplicates: 0 Warnings: 0 INSERT INTO ... VALUES (...),(...),(...)...String format: Records: 37 Duplicates: 0 Warnings: 0 LOAD DATA INFILE ...String format: Records: 42 Deleted: 0 Skipped: 0 Warnings: 0 ALTER TABLEString format: Records: 60 Duplicates: 0 Warnings: 0 UPDATEString format: Rows matched: 65 Changed: 65 Warnings: 0 Notes Note mysql_info returns a non- FALSE value for the INSERT ... VALUES statement only if multiple value lists are specified in the statement. See Also 22.9.2.5.31. mysql_insert_id Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description int mysql_insert_id( resource link_identifier= =NULL); Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT). Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established. Examples Example 22.59. mysql_insert_idexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}mysql_select_db('mydb');mysql_query("INSERT INTO mytable (product) values ('kossu')");printf("Last inserted record has id %d\n", mysql_insert_id());?> Notes Caution mysql_insert_id will convert the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT (64 bits) the conversion may result in an incorrect value. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query. For more information about PHP's maximum integer values, please see the integer documentation. Note The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries. See Also 22.9.2.5.32. mysql_list_dbs Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: | SQL Query: SHOW DATABASES |
Description resource mysql_list_dbs( resource link_identifier= =NULL); Returns a result pointer containing the databases available from the current mysql daemon. Warning This function has been DEPRECATED as of PHP 5.4.0. Relyingon this function is highly discouraged. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns a result pointer resource on success, or FALSE on failure. Use the mysql_tablename function to traverse this result pointer, or any function for result tables, such as mysql_fetch_array. Examples Example 22.60. mysql_list_dbsexample <?php// Usage without mysql_list_dbs()$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$res = mysql_query("SHOW DATABASES");while ($row = mysql_fetch_assoc($res)) { echo $row['Database'] . "\n";}// Deprecated as of PHP 5.4.0$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$db_list = mysql_list_dbs($link);while ($row = mysql_fetch_object($db_list)) { echo $row->Database . "\n";}?> The above example will output something similar to: database1database2database3 Notes Note For backward compatibility, the following deprecated alias may be used: mysql_listdbs See Also 22.9.2.5.33. mysql_list_fields Copyright 1997-2012 the PHP Documentation Group. mysql_list_fields List MySQL table fields
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: | SQL Query: SHOW COLUMNS FROM sometable |
Description resource mysql_list_fields( string database_name, string table_name, resource link_identifier= =NULL); Retrieves information about the given table name. This function is deprecated. It is preferable to use mysql_query to issue an SQL SHOW COLUMNS FROM table [LIKE 'name'] statement instead. Parameters - database_name
The name of the database that's being queried. - table_name
The name of the table that's being queried. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values A result pointer resource on success, or FALSE on failure. The returned result can be used with mysql_field_flags, mysql_field_len, mysql_field_name and mysql_field_type. Examples Example 22.61. Alternate to deprecatedmysql_list_fields <?php$result = mysql_query("SHOW COLUMNS FROM sometable");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;}if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_assoc($result)) { print_r($row); }}?> The above example will output something similar to: Array( [Field] => id [Type] => int(7) [Null] => [Key] => PRI [Default] => [Extra] => auto_increment)Array( [Field] => email [Type] => varchar(100) [Null] => [Key] => [Default] => [Extra] =>) Notes Note For backward compatibility, the following deprecated alias may be used: mysql_listfields See Also 22.9.2.5.34. mysql_list_processes Copyright 1997-2012 the PHP Documentation Group. mysql_list_processes List MySQL processes
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description resource mysql_list_processes( resource link_identifier= =NULL); Retrieves the current MySQL server threads. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values A result pointer resource on success or FALSE on failure. Examples Example 22.62. mysql_list_processesexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$result = mysql_list_processes($link);while ($row = mysql_fetch_assoc($result)){ printf("%s %s %s %s %s\n", $row["Id"], $row["Host"], $row["db"], $row["Command"], $row["Time"]);}mysql_free_result($result);?> The above example will output something similar to: 1 localhost test Processlist 04 localhost mysql sleep 5 See Also 22.9.2.5.35. mysql_list_tables Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: | SQL Query: SHOW TABLES FROM sometable |
Description resource mysql_list_tables( string database, resource link_identifier= =NULL); Retrieves a list of table names from a MySQL database. This function is deprecated. It is preferable to use mysql_query to issue an SQL SHOW TABLES [FROM db_name] [LIKE 'pattern'] statement instead. Parameters - database
The name of the database - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values A result pointer resource on success or FALSE on failure. Use the mysql_tablename function to traverse this result pointer, or any function for result tables, such as mysql_fetch_array. Changelog Examples Example 22.63. mysql_list_tablesalternative example <?php$dbname = 'mysql_dbname';if (!mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) { echo 'Could not connect to mysql'; exit;}$sql = "SHOW TABLES FROM $dbname";$result = mysql_query($sql);if (!$result) { echo "DB Error, could not list tables\n"; echo 'MySQL Error: ' . mysql_error(); exit;}while ($row = mysql_fetch_row($result)) { echo "Table: {$row[0]}\n";}mysql_free_result($result);?> Notes Note For backward compatibility, the following deprecated alias may be used: mysql_listtables See Also 22.9.2.5.36. mysql_num_fields Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description int mysql_num_fields( resource result); Retrieves the number of fields from a query. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query.
Return Values Returns the number of fields in the result set resource on success or FALSE on failure. Examples Example 22.64. A mysql_num_fieldsexample <?php$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");if (!$result) { echo 'Could not run query: ' . mysql_error(); exit;}/* returns 2 because id,email === two fields */echo mysql_num_fields($result);?> Notes Note For backward compatibility, the following deprecated alias may be used: mysql_numfields See Also 22.9.2.5.37. mysql_num_rows Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description int mysql_num_rows( resource result); Retrieves the number of rows from a result set. This command is only valid for statements like SELECT or SHOW that return an actual result set. To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use mysql_affected_rows. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query.
Return Values The number of rows in a result set on success or FALSE on failure. Examples Example 22.65. mysql_num_rowsexample <?php$link = mysql_connect("localhost", "mysql_user", "mysql_password");mysql_select_db("database", $link);$result = mysql_query("SELECT * FROM table1", $link);$num_rows = mysql_num_rows($result);echo "$num_rows Rows\n";?> Notes Note For backward compatibility, the following deprecated alias may be used: mysql_numrows See Also 22.9.2.5.38. mysql_pconnect Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description resource mysql_pconnect( string server= =ini_get("mysql.default_host"), string username= =ini_get("mysql.default_user"), string password= =ini_get("mysql.default_password"), int client_flags= =0); Establishes a persistent connection to a MySQL server. mysql_pconnect acts very much like mysql_connect with two major differences. First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection. Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close will not close links established by mysql_pconnect). This type of link is therefore called 'persistent'. Parameters - server
The MySQL server. It can also include a port number. e.g. "hostname:port" or a path to a local socket e.g. ":/path/to/socket" for the localhost. If the PHP directive mysql.default_host is undefined (default), then the default value is 'localhost:3306' - username
The username. Default value is the name of the user that owns the server process. - password
The password. Default value is an empty password. - client_flags
The client_flags parameter can be a combination of the following constants: 128 (enable LOAD DATA LOCAL handling), MYSQL_CLIENT_SSL , MYSQL_CLIENT_COMPRESS , MYSQL_CLIENT_IGNORE_SPACE or MYSQL_CLIENT_INTERACTIVE .
Return Values Returns a MySQL persistent link identifier on success, or FALSE on failure. Changelog Notes Note Note, that these kind of links only work if you are using a module version of PHP. See the Persistent Database Connections section for more information. Warning Using persistent connections can require a bit of tuning of your Apache and MySQL configurations to ensure that you do not exceed the number of connections allowed by MySQL. Note You can suppress the error message on failure by prepending a @ to the function name. See Also Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description bool mysql_ping( resource link_identifier= =NULL); Checks whether or not the connection to the server is working. If it has gone down, an automatic reconnection is attempted. This function can be used by scripts that remain idle for a long while, to check whether or not the server has closed the connection and reconnect if necessary. Note Automatic reconnection is disabled by default in versions of MySQL >= 5.0.3. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns TRUE if the connection to the server MySQL server is working, otherwise FALSE . Examples Example 22.66. A mysql_pingexample <?phpset_time_limit(0);$conn = mysql_connect('localhost', 'mysqluser', 'mypass');$db = mysql_select_db('mydb');/* Assuming this query will take a long time */$result = mysql_query($sql);if (!$result) { echo 'Query #1 failed, exiting.'; exit;}/* Make sure the connection is still alive, if not, try to reconnect */if (!mysql_ping($conn)) { echo 'Lost connection, exiting after query #1'; exit;}mysql_free_result($result);/* So the connection is still alive, let's run another query */$result2 = mysql_query($sql2);?> See Also Copyright 1997-2012 the PHP Documentation Group. mysql_query Send a MySQL query
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description resource mysql_query( string query, resource link_identifier= =NULL); mysql_query sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier. Parameters - query
An SQL query The query string should not end with a semicolon. Data inside the query should be properly escaped. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query returns a resource on success, or FALSE on error. For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query returns TRUE on success or FALSE on error. The returned result resource should be passed to mysql_fetch_array, and other functions for dealing with result tables, to access the returned data. Use mysql_num_rows to find out how many rows were returned for a SELECT statement or mysql_affected_rows to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement. mysql_query will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query. Examples Example 22.67. Invalid Query The following query is syntactically invalid, so mysql_query fails and returns FALSE . <?php$result = mysql_query('SELECT * WHERE 1=1');if (!$result) { die('Invalid query: ' . mysql_error());}?>Example 22.68. Valid Query The following query is valid, so mysql_query returns a resource. <?php// This could be supplied by a user, for example$firstname = 'fred';$lastname = 'fox';// Formulate Query// This is the best way to perform an SQL query// For more examples, see mysql_real_escape_string()$query = sprintf("SELECT firstname, lastname, address, age FROM friends WHERE firstname='%s' AND lastname='%s'", mysql_real_escape_string($firstname), mysql_real_escape_string($lastname));// Perform Query$result = mysql_query($query);// Check result// This shows the actual query sent to MySQL, and the error. Useful for debugging.if (!$result) { $message = 'Invalid query: ' . mysql_error() . "\n"; $message .= 'Whole query: ' . $query; die($message);}// Use result// Attempting to print $result won't allow access to information in the resource// One of the mysql result functions must be used// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.while ($row = mysql_fetch_assoc($result)) { echo $row['firstname']; echo $row['lastname']; echo $row['address']; echo $row['age'];}// Free the resources associated with the result set// This is done automatically at the end of the scriptmysql_free_result($result);?> See Also 22.9.2.5.41. mysql_real_escape_string Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_real_escape_string( string unescaped_string, resource link_identifier= =NULL); Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query. If binary data is to be inserted, this function must be used. mysql_real_escape_string calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. This function must always (with few exceptions) be used to make data safe before sending a query to MySQL. Parameters - unescaped_string
The string that is to be escaped. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns the escaped string, or FALSE on error. Examples Example 22.69. Simple mysql_real_escape_stringexample <?php// Connect$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') OR die(mysql_error());// Query$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'", mysql_real_escape_string($user), mysql_real_escape_string($password));?>Example 22.70. An example SQL Injection Attack <?php// We didn't check $_POST['password'], it could be anything the user wanted! For example:$_POST['username'] = 'aidan';$_POST['password'] = "' OR ''='";// Query database to check if there are any matching users$query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'";mysql_query($query);// This means the query sent to MySQL would be:echo $query;?> The query sent to MySQL: SELECT * FROM users WHERE user='aidan' AND password='' OR ''='' This would allow anyone to log in without a valid password. Notes Note A MySQL connection is required before using mysql_real_escape_string otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn't defined, the last MySQL connection is used. Note If magic_quotes_gpc is enabled, first apply stripslashes to the data. Using this function on data which has already been escaped will escape the data twice. Note mysql_real_escape_string does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE. See Also 22.9.2.5.42. mysql_result Copyright 1997-2012 the PHP Documentation Group. mysql_result Get result data
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_result( resource result, int row, mixed field= =0); Retrieves the contents of one cell from a MySQL result set. When working on large result sets, you should consider using one of the functions that fetch an entire row (specified below). As these functions return the contents of multiple cells in one function call, they're MUCH quicker than mysql_result. Also, note that specifying a numeric offset for the field argument is much quicker than specifying a fieldname or tablename.fieldname argument. Parameters - result
The result resource that is being evaluated. This result comes from a call to mysql_query. - row
The row number from the result that's being retrieved. Row numbers start at 0. - field
The name or offset of the field being retrieved. It can be the field's offset, the field's name, or the field's table dot field name (tablename.fieldname). If the column name has been aliased ('select foo as bar from...'), use the alias instead of the column name. If undefined, the first field is retrieved.
Return Values The contents of one cell from a MySQL result set on success, or FALSE on failure. Examples Example 22.71. mysql_resultexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Could not connect: ' . mysql_error());}if (!mysql_select_db('database_name')) { die('Could not select database: ' . mysql_error());}$result = mysql_query('SELECT name FROM work.employee');if (!$result) { die('Could not query:' . mysql_error());}echo mysql_result($result, 2); // outputs third employee's namemysql_close($link);?> Notes Note Calls to mysql_result should not be mixed with calls to other functions that deal with the result set. See Also 22.9.2.5.43. mysql_select_db Copyright 1997-2012 the PHP Documentation Group. mysql_select_db Select a MySQL database
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description bool mysql_select_db( string database_name, resource link_identifier= =NULL); Sets the current active database on the server that's associated with the specified link identifier. Every subsequent call to mysql_query will be made on the active database. Parameters - database_name
The name of the database that is to be selected. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns TRUE on success or FALSE on failure. Examples Example 22.72. mysql_select_dbexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$link) { die('Not connected : ' . mysql_error());}// make foo the current db$db_selected = mysql_select_db('foo', $link);if (!$db_selected) { die ('Can\'t use foo : ' . mysql_error());}?> Notes Note For backward compatibility, the following deprecated alias may be used: mysql_selectdb See Also 22.9.2.5.44. mysql_set_charset Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description bool mysql_set_charset( string charset, resource link_identifier= =NULL); Sets the default character set for the current connection. Parameters - charset
A valid character set name. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns TRUE on success or FALSE on failure. Notes Note This function requires MySQL 5.0.7 or later. See Also Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_stat( resource link_identifier= =NULL); mysql_stat returns the current server status. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values Returns a string with the status for uptime, threads, queries, open tables, flush tables and queries per second. For a complete list of other status variables, you have to use the SHOW STATUS SQL command. If link_identifier is invalid, NULL is returned. Examples Example 22.73. mysql_statexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$status = explode(' ', mysql_stat($link));print_r($status);?> The above example will output something similar to: Array( [0] => Uptime: 5380 [1] => Threads: 2 [2] => Questions: 1321299 [3] => Slow queries: 0 [4] => Opens: 26 [5] => Flush tables: 1 [6] => Open tables: 17 [7] => Queries per second avg: 245.595) Example 22.74. Alternative mysql_statexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$result = mysql_query('SHOW STATUS', $link);while ($row = mysql_fetch_assoc($result)) { echo $row['Variable_name'] . ' = ' . $row['Value'] . "\n";}?> The above example will output something similar to: back_log = 50basedir = /usr/local/bdb_cache_size = 8388600bdb_log_buffer_size = 32768bdb_home = /var/db/mysql/bdb_max_lock = 10000bdb_logdir =bdb_shared_data = OFFbdb_tmpdir = /var/tmp/... See Also 22.9.2.5.46. mysql_tablename Copyright 1997-2012 the PHP Documentation Group. mysql_tablename Get table name of field
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description string mysql_tablename( resource result, int i); Retrieves the table name from a result. This function is deprecated. It is preferable to use mysql_query to issue an SQL SHOW TABLES [FROM db_name] [LIKE 'pattern'] statement instead. Parameters - result
A result pointer resource that's returned from mysql_list_tables. - i
The integer index (row/table number)
Return Values The name of the table on success or FALSE on failure. Use the mysql_tablename function to traverse this result pointer, or any function for result tables, such as mysql_fetch_array. Changelog Examples Example 22.75. mysql_tablenameexample <?phpmysql_connect("localhost", "mysql_user", "mysql_password");$result = mysql_list_tables("mydb");$num_rows = mysql_num_rows($result);for ($i = 0; $i < $num_rows; $i++) { echo "Table: ", mysql_tablename($result, $i), "\n";}mysql_free_result($result);?> Notes Note The mysql_num_rows function may be used to determine the number of tables in the result pointer. See Also 22.9.2.5.47. mysql_thread_id Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description int mysql_thread_id( resource link_identifier= =NULL); Retrieves the current thread ID. If the connection is lost, and a reconnect with mysql_ping is executed, the thread ID will change. This means only retrieve the thread ID when needed. Parameters - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values The thread ID on success or FALSE on failure. Examples Example 22.76. mysql_thread_idexample <?php$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');$thread_id = mysql_thread_id($link);if ($thread_id){ printf("current thread id is %d\n", $thread_id);}?> The above example will output something similar to: current thread id is 73 See Also 22.9.2.5.48. mysql_unbuffered_query Copyright 1997-2012 the PHP Documentation Group. Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: Description resource mysql_unbuffered_query( string query, resource link_identifier= =NULL); mysql_unbuffered_query sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed. To use mysql_unbuffered_query while multiple database connections are open, you must specify the optional parameter link_identifier to identify which connection you want to use. Parameters - query
The SQL query to execute. Data inside the query should be properly escaped. - link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect is assumed. If no such link is found, it will try to create one as if mysql_connect was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
Return Values For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_unbuffered_query returns a resource on success, or FALSE on error. For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_unbuffered_query returns TRUE on success or FALSE on error. Notes See Also Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
| |
