MySQL And PHP - E-book PDF

1m ago
4 Views
0 Downloads
801.77 KB
252 Pages
Transcription

MySQL and PHP

MySQL and PHP ReferenceAbstractThis manual describes the PHP extensions and interfaces that can be used with MySQL.Document generated on: 2010-09-22 (revision: 22813)Copyright 1997, 2010, Oracle and/or its affiliates. All rights reserved.This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast,modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, ordecompilation of this software, unless required by law for interoperability, is prohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to usin writing.If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customersare "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms setforth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherentlydangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporationand its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. MySQL is a trademark of Oracle Corporation and/or its affiliates, and shallnot be used without Oracle's express written authorization. Other names may be trademarks of their respective owners.This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and itsaffiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, orservices.This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and useof this material is subject to the terms and conditions of your Oracle Software License and Service Agreement, which has been executed and with whichyou agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oraclewithout prior written consent of Oracle or as specifically provided below. This document is not part of your license agreement nor can it be incorporatedinto any contractual agreement with Oracle or its subsidiaries or affiliates.This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms:You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute thedocumentation in a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROMor similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as anydissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above.For more information on the terms of this license, for details on how the MySQL documentation is built and produced, or if you are interested in doing atranslation, please visit MySQL Contact & Questions.For additional licensing information, including licenses for libraries used by MySQL products, see Preface, Notes, Licenses.If you want help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you can discuss your issues with otherMySQL users.For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in variety of formats, including HTML and PDF formats, see the MySQL Documentation Library.

PrefacePHP 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 orcompiled as a module for use with the Apache Web server.PHP actually provides two different MySQL API extensions: 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. If you wish to use this extension with MySQL 4.1, you will likely want to configure the MySQL server to use the -old-passwords option (see Client does not support authentication protocol). This extension is documented on the PHP Web site at http://php.net/mysql. Chapter 2, MySQL Improved Extension (Mysqli) - Stands for “MySQL, Improved”; this extension is available only in PHP 5. It isintended for use with MySQL 4.1.1 and later. This extension fully supports the authentication protocol used in MySQL 5.0, as wellas the Prepared Statements and Multiple Statements APIs. In addition, this extension provides an advanced, object-oriented programming interface. You can read the documentation for the mysqli extension at http://php.net/mysqli. Helpful article can befound at http://devzone.zend.com/node/view/id/686 and http://devzone.zend.com/node/view/id/687.If you're experiencing problems with enabling both the mysql and the mysqli extension when building PHP on Linux yourself, seeChapter 7, Enabling Both mysql and mysqli in PHP.The PHP distribution and documentation are available from the PHP Web site.Portions of this section are Copyright (c) 1997-2008 the PHP Documentation Group This material may be distributed only subject tothe 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 This material may be distributed only subjectto the terms and conditio\ ns set forth in the Open Publication License, v1.0.8 or later (the latest version is presently available at http://www.opencontent.org/openpub/).iv

Chapter 1. MySQL Extension (mysql)Copyright 1997-2010 the PHP Documentation Group.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/.For an overview of MySQL database connectivity terms and products see Section 2.2, “Overview”.1.1. Installing/ConfiguringCopyright 1997-2010 the PHP Documentation Group.1.1.1. RequirementsCopyright 1997-2010 the PHP Documentation Group.In order to have these functions available, you must compile PHP with MySQL support.1.1.2. InstallationCopyright 1997-2010 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 anyconflicts.1.1.2.1. Installation on Linux SystemsCopyright 1997-2010 the PHP Documentation Group.1.1.2.1.1. PHP 4Copyright 1997-2010 the PHP Documentation Group.The option --with-mysql is enabled by default. This default behavior may be disabled with the --without-mysql configure option. If MySQL is enabled without specifying the path to the MySQL install DIR, PHP will use the bundled MySQL client libraries.Users who run other applications that use MySQL (for example, auth-mysql) should not use the bundled library, but rather specifythe path to MySQL's install directory, like so: --with-mysql /path/to/mysql. This will force PHP to use the client libraries installed by MySQL, thus avoiding any conflicts.1.1.2.1.2. PHP 5.0.x, 5.1.x, 5.2.xCopyright 1997-2010 the PHP Documentation Group.MySQL is not enabled by default, nor is the MySQL library bundled with PHP. Read this FAQ for details on why. Use the -with-mysql[ DIR] configure option to include MySQL support. You can download headers and libraries from http://www.mysql.com/.1.1.2.1.3. PHP 5.3.0 Copyright 1997-2010 the PHP Documentation Group.In PHP 5.3.0 and above the MySQL-related database extensions use the MySQL Native Driver by default. This means that the MySQL1

MySQL Extension (mysql)Client Library (libmysql) is no longer required in order to support connection to a MySQL database. The extensions mysql,mysqli, and PHP PDO MYSQL are all enabled by default in PHP 5.3.0 , and all use the MySQL Native Driver by default. In eachcase no further installation steps are required in order to use these extensions, although you may want to set some preferences inphp.ini depending on your requirements.1.1.2.2. Installation on Windows SystemsCopyright 1997-2010 the PHP Documentation Group.1.1.2.2.1. PHP 4Copyright 1997-2010 the PHP Documentation Group.The PHP MySQL extension is compiled into PHP.1.1.2.2.2. PHP 5.0.x, 5.1.x, 5.2.xCopyright 1997-2010 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 accessto the MySQL client library. A file named libmysql.dll is included in the Windows PHP distribution and in order for PHP to talkto MySQL this file needs to be available to the Windows systems PATH. See the FAQ titled "How do I add my PHP directory to thePATH on Windows" for information on how to do this. Although copying libmysql.dll to the Windows system directory alsoworks (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 wherethe PHP extensions are located. See also the Manual Windows Installation Instructions. An example extension dir value for PHP 5 isc:\php\extNoteIf 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.1.1.2.2.3. PHP 5.3.0 Copyright 1997-2010 the PHP Documentation Group.Please refer to these notes on installing MySQL support on PHP 5.3.0 and above.1.1.2.3. MySQL Installation NotesCopyright 1997-2010 the PHP Documentation Group.WarningCrashes 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.NoteIf you need charsets other than latin (default), you have to install external (not bundled) libmysql with compiled charsetsupport.1.1.3. Runtime ConfigurationCopyright 1997-2010 the PHP Documentation Group.The behaviour of these functions is affected by settings in php.ini.Table 1.1. MySQL Configuration Options2

MySQL Extension (mysql)NameDefaultChangeablemysql.allow persistent"1"PHP INI SYSTEMmysql.max persistent"-1"PHP INI SYSTEMmysql.max links"-1"PHP INI SYSTEMmysql.trace mode"0"PHP INI ALLmysql.default portNULLPHP INI ALLmysql.default socketNULLPHP INI ALLmysql.default hostNULLPHP INI ALLmysql.default userNULLPHP INI ALLmysql.default passwordNULLPHP INI ALLmysql.connect timeout"60"PHP INI ALLChangelogAvailable since PHP 4.3.0.Available since PHP 4.0.1.PHP INI SYSTEM in PHP 4.3.2. Available since PHP4.3.0.For further details and definitions of the PHP INI * modes, see the configuration.changes.modes.Here's a short explanation of the configuration directives.mysql.allow persistentbooleanmysql.max persistent integermysql.max links integerWhether to allow persistent connections to MySQL.mysql.trace mode booleanTrace mode. When mysql.trace mode is enabled, warnings for table/index scans, non freeresult sets, and SQL-Errors will be displayed. (Introduced in PHP 4.3.0)mysql.default port stringThe default TCP port number to use when connecting to the database server if no other port isspecified. 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-timeMYSQL PORT constant, in that order. Win32 will only use the MYSQL PORT constant.mysql.default socketstringThe default socket name to use when connecting to a local database server if no other socketname is specified.mysql.default host stringThe 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 stringThe 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 passwordstringThe 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 integerConnect timeout in seconds. On Linux this timeout is also used for waiting for the first answerfrom the server.The maximum number of persistent MySQL connections per process.The maximum number of MySQL connections per process, including persistent connections.1.1.4. Resource TypesCopyright 1997-2010 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.1.2. Predefined ConstantsCopyright 1997-2010 the PHP Documentation Group.3

MySQL Extension (mysql)The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP ordynamically 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 1.2. MySQL client constantsConstantDescriptionMYSQL CLIENT COMPRESSUse compression protocolMYSQL CLIENT IGNORE SPACEAllow space after function namesMYSQL CLIENT INTERACTIVEAllow interactive timeout seconds (instead of wait timeout)of inactivity before closing the connection.MYSQL CLIENT SSLUse SSL encryption. This flag is only available with version 4.x ofthe MySQL client library or newer. Version 3.23.x is bundled bothwith 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 1.3. MySQL fetch constantsConstantDescriptionMYSQL ASSOCColumns are returned into the array having the fieldname as the array index.MYSQL BOTHColumns are returned into the array having both a numerical indexand the fieldname as the array index.MYSQL NUMColumns are returned into the array having a numerical index tothe fields. This index starts with 0, the first field in the result.1.3. ExamplesCopyright 1997-2010 the PHP Documentation Group.1.3.1. BasicThis simple example shows how to connect, execute a query, print resulting rows and disconnect from a MySQL database.Example 1.1. MySQL extension overview exampleCopyright 1997-2010 the PHP Documentation Group. ?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 resultset4

MySQL Extension (mysql)mysql free result( result);// Closing connectionmysql close( link);? 1.4. MySQL FunctionsCopyright 1997-2010 the PHP Documentation Group.NoteMost 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 notsuccessful, functions return FALSE .1.4.1. mysql affected rowsCopyright 1997-2010 the PHP Documentation Group. mysql affected rowsGet number of affected rows in previous MySQL operationDescriptionint mysql affected rows(resource link identifier);Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link identifier.Parameterslink identifierThe MySQL connection. If the link identifier is not specified, the last link opened bymysql connect is assumed. If no such link is found, it will try to create one as ifmysql connect was called with no arguments. If no connection is found or established, anE WARNING level error is generated.Return ValuesReturns 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 functionwill 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 possibilitythat 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 thenumber of deleted records plus the number of inserted records.ExamplesExample 1.2. mysql affected rows example ?php5

MySQL Extension (mysql) 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 si

MySQL is no longer enabled by default, so the php_mysql.dllDLL must be enabled inside of php.ini. Also, PHP needs access to the MySQL client library. A file named libmysql.dllis 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 PATHon Windows" for ...