Php pdo operation database and phppdo Database

Source: Internet
Author: User
Tags error status code prefetch

Php pdo operation database and phppdo Database

The POD extension is added to PHP5, which provides the PHP built-in PDO class to access the database. Different databases use the same method name to solve the problem of inconsistent database connections.

PDO features:

Performance. From the very beginning, PDO learned from the success and failure of existing database expansion. Because the PDO code is brand new, we have the opportunity to design performance again to take advantage of the latest features of PHP 5.
Capability. PDO is designed to provide common database functions as the basis and provide convenient access to the unique functions of RDBMS.
Simple. PDO is designed to make it easy to use databases. The API does not forcibly intervene in your code and clearly shows the process of calling each function.
It can be expanded during runtime. The PDO extension is modular, allowing you to load drivers at the backend of your database at runtime without re-compiling or re-installing the entire PHP program. For example, the PDO_OCI extension replaces the PDO extension to implement the oracle database API. There are also some drivers for MySQL, PostgreSQL, ODBC, and Firebird. More drivers are still under development.

  

PDO Installation

You can use the phpinfo () function of PHP to check whether the PDO extension is installed.

1. Install PDO on Unix/Linux systems

On Unix or Linux, you need to add the following extensions:

Extension = pdo. so

  2. Install pdo on Windows

PDO and all major drivers are released as shared extensions along with PHP. to activate them, simply edit the php. ini file and add the following extensions:

Extension = php_pdo.dll

In addition, the following database extensions are available:

    ;extension=php_pdo_firebird.dll    ;extension=php_pdo_informix.dll    ;extension=php_pdo_mssql.dll    ;extension=php_pdo_mysql.dll    ;extension=php_pdo_oci.dll    ;extension=php_pdo_oci8.dll    ;extension=php_pdo_odbc.dll    ;extension=php_pdo_pgsql.dll    ;extension=php_pdo_sqlite.dll

Open php. ini and remove the semicolons (;) from all the preceding rows.
After setting these configurations, We need to restart the PHP or Web server.

 

Take mysql as an example to use pdo:

<? Php $ dbms = 'mysql'; // Database Type $ host = 'localhost'; // Database host Name $ dbName = 'test '; // database $ user = 'root'; // database connection username $ pass = ''; // password $ dsn =" $ dbms: host = $ host; dbname = $ dbName "; try {$ dbh = new PDO ($ dsn, $ user, $ pass); // initialize a PDO object echo" connection successful <br/> "; /* you can perform a search operation foreach ($ dbh-> query ('select * from foo') as $ row) {print_r ($ row ); // you can use echo ($ GLOBAL); to view these values} */$ dbh = null;} catch (PDOException $ e) {die ("Error! :". $ E-> getMessage (). "<br/>");} // This is not a persistent connection by default. If you need a persistent connection to the database, add the last parameter: array (PDO: ATTR_PERSISTENT => true) the result is as follows: $ db = new PDO ($ dsn, $ user, $ pass, array (PDO: ATTR_PERSISTENT => true);?>

 

  Let's take a look at the detailed introduction of pdo:

1. predefined constants:

PDO: PARAM_BOOL (integer) Boolean data type.
PDO: PARAM_NULL (integer) Indicates the NULL data type in SQL.
PDO: PARAM_INT (integer) Integer in SQL.
PDO: PARAM_STR (integer) CHAR, VARCHAR, or other string types in SQL.
PDO: PARAM_LOB (integer) Indicates the data type of large objects in SQL.
PDO: PARAM_STMT (integer) Indicates the type of a record set. Currently, no driver is supported.
PDO: PARAM_INPUT_OUTPUT (integer) The specified parameter is the INOUT parameter of a stored procedure. A specific PDO: PARAM _ * data type must be used with this value for bitwise OR.
PDO: FETCH_LAZY (integer) Specify the way to obtain data. Each row in the result set is returned as an object. The variable name of this object corresponds to the column name. PDO: The variable name of the object to be accessed by FETCH_LAZY. Invalid in PDOStatement: fetchAll.
PDO: FETCH_ASSOC (integer) Specify the query method. Each row in the corresponding result set is returned as an array indexed by the column name. If the result set contains multiple columns with the same name, only one value is returned for each column name of PDO: FETCH_ASSOC.
PDO: FETCH_NAMED (integer) Specify the query method. Each row in the corresponding result set is returned as an array indexed by the column name. If the result set contains multiple columns with the same name, each column name of PDO: FETCH_ASSOC returns an array containing values.
PDO: FETCH_NUM (integer) Specify the acquisition method. Each row in the corresponding result set is returned as an array indexed by the column number, starting from column 0th.
PDO: FETCH_BOTH (integer) Specify the acquisition method. Each row in the corresponding result set is returned as an array indexed by the column number and column name, starting from column 0th.
PDO: FETCH_OBJ (integer) Specify the way to obtain data. Each row in the result set is returned as an object of the column name corresponding to an attribute name.
PDO: FETCH_BOUND (integer) Returns TRUE and assigns the column values in the result set to the PHP variables bound using the PDOStatement: bindParam () or PDOStatement: bindColumn () method.
PDO: FETCH_COLUMN (integer) Specify the method to obtain the required columns from the next row in the result set.
PDO: FETCH_CLASS (integer) Returns a new instance of the requested class, which is mapped to the corresponding attribute name in the class.

Note: If this attribute does not exist in the requested class, the _ set () magic method is called.

PDO: FETCH_INTO (integer) Specify the access method, update an existing instance of the Request class, and map the columns to the corresponding attribute names in the class.
PDO: FETCH_FUNC (integer) Data can be processed in full custom mode during running. (Valid only in PDOStatement: fetchAll ).
PDO: FETCH_GROUP (integer) Return results by group of values. Usually used together with PDO: FETCH_COLUMN or PDO: FETCH_KEY_PAIR.
PDO: FETCH_UNIQUE (integer) Take only the unique value.
PDO: FETCH_KEY_PAIR (integer) Obtain a result set with two columns to an array. The first column is the key name and the second column is the value. Available from PHP 5.2.3.
PDO: FETCH_CLASSTYPE (integer) Determine the class name based on the value of the first column.
PDO: FETCH_SERIALIZE (integer) Similar to PDO: FETCH_INTO, but represents an object in a serialized string. Available from PHP 5.1.0. From PHP 5.3.0, if this flag is set, the class constructor will never be called.
PDO: FETCH_PROPS_LATE (integer) Call the constructor before setting properties. Available from PHP 5.2.0.
PDO: ATTR_AUTOCOMMIT (integer) If this value is FALSE, PDO tries to disable automatic commit so that the database connection starts a transaction.
PDO: ATTR_PREFETCH (integer) Set the prefetch size to balance the speed and memory usage of your application. Not all database/driver combinations support setting the prefetch size. The larger prefetch size leads to higher performance while occupying more memory.
PDO: ATTR_TIMEOUT (integer) Set the timeout seconds for database connection.
PDO: ATTR_ERRMODE (integer) For more information about this attribute, see error and error handling.
PDO: ATTR_SERVER_VERSION (integer) This attribute is read-only and returns the version information of the Database Service that the PDO connects.
PDO: ATTR_CLIENT_VERSION (integer) This attribute is read-only and returns the version information of the client library used by the PDO driver.
PDO: ATTR_SERVER_INFO (integer) This is a read-only attribute. Returns metadata about the database service that the PDO connects.
PDO: ATTR_CONNECTION_STATUS (integer)  
PDO: ATTR_CASE (integer) Use a constant like PDO: CASE _ * to force the column name to the specified CASE.
PDO: ATTR_CURSOR_NAME (integer) Obtain or set the name of the cursor. It is useful when you use a scroll cursor and locate an update.
PDO: ATTR_CURSOR (integer) Select the cursor type. PDO currently supports PDO: CURSOR_FWDONLY and PDO: CURSOR_SCROLL. It is usually PDO: CURSOR_FWDONLY, unless a scroll cursor is required.
PDO: ATTR_DRIVER_NAME (string) Returns the driver name.

Example of using PDO: ATTR_DRIVER_NAME:

<?phpif ($db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql') {  echo "Running on mysql; doing something mysql specific here\n";}?>
PDO: ATTR_ORACLE_NULLS (integer) Convert an empty string to NULL in SQL when obtaining data.
PDO: ATTR_PERSISTENT (integer) Requests a persistent connection instead of creating a new connection. For more information about this attribute, see connection and connection management.
PDO: ATTR_STATEMENT_CLASS (integer)  
PDO: ATTR_FETCH_CATALOG_NAMES (integer) Add the included directory name to the front of each column name in the result set. The directory name and column name are separated by a decimal point (.). This attribute is supported at the driver level, so some drivers may not support this attribute.
PDO: ATTR_FETCH_TABLE_NAMES (integer) Add the included table name to the front of each column name in the result set. The table name and column name are separated by a decimal point (.). This attribute is supported at the driver level, so some drivers may not support this attribute.
PDO: ATTR_STRINGIFY_FETCHES (integer)  
PDO: ATTR_MAX_COLUMN_LEN (integer)  
PDO: ATTR_DEFAULT_FETCH_MODE (integer) Available from PHP 5.2.0.
PDO: ATTR_EMULATE_PREPARES (integer) Available from PHP 5.1.3.
PDO: ERRMODE_SILENT (integer) If an error occurs, no error or exception is displayed. Developers are expected to explicitly check for errors. This is the default mode. For more information about this attribute, see error and error handling.
PDO: ERRMODE_WARNING (integer) If an error occurs, a PHP E_WARNING message is displayed. For more information about this attribute, see error and error handling.
PDO: ERRMODE_EXCEPTION (integer) If an error occurs, a PDOException is thrown. For more information about this attribute, see error and error handling.
PDO: CASE_NATURAL (integer) The column names returned by the database driver are retained.
PDO: CASE_LOWER (integer) The column name must be in lower case.
PDO: CASE_UPPER (integer) Forcibly uppercase column names.
PDO: NULL_NATURAL (integer)  
PDO: NULL_EMPTY_STRING (integer)  
PDO: NULL_TO_STRING (integer)  
PDO: FETCH_ORI_NEXT (integer) Obtain the next row in the result set. Valid only for scroll cursors.
PDO: FETCH_ORI_PRIOR (integer) Obtain the previous row in the result set. Valid only for scroll cursors.
PDO: FETCH_ORI_FIRST (integer) Obtain the first line in the result set. Valid only for scroll cursors.
PDO: FETCH_ORI_LAST (integer) Obtain the last row in the result set. Valid only for scroll cursors.
PDO: FETCH_ORI_ABS (integer) Obtain the required rows from the result set based on the row number. Valid only for scroll cursors.
PDO: FETCH_ORI_REL (integer) Obtain the required rows from the result set based on the relative position of the current cursor position. Valid only for scroll cursors.
PDO: CURSOR_FWDONLY (integer) Create a cursor-only PDOStatement object. This is the default cursor option, because this cursor is the fastest and the most common data access mode in PHP.
PDO: CURSOR_SCROLL (integer) Create a PDOStatement object that can scroll cursors. Use the PDO: FETCH_ORI _ * constant to control the rows obtained in the result set.
PDO: ERR_NONE (string) Corresponding to SQLSTATE '200', indicating that the SQL statement is successfully issued without errors or warnings. This constant is convenient when PDO: errorCode () or PDOStatement: errorCode () is used to determine whether an error occurs. The error status code returned by the preceding method is frequently used.
PDO: PARAM_EVT_ALLOC (integer) Allocate events
PDO: PARAM_EVT_FREE (integer) Unallocate events
PDO: PARAM_EVT_EXEC_PRE (integer) An event is triggered before a preprocessing statement is executed.
PDO: PARAM_EVT_EXEC_POST (integer) An event is triggered after a preprocessing statement is executed.
PDO: PARAM_EVT_FETCH_PRE (integer) Events triggered before a result is extracted from a result set.
PDO: PARAM_EVT_FETCH_POST (integer) An event is triggered after a result is extracted from a result set.
PDO: PARAM_EVT_NORMALIZE (integer) The event is triggered when the BIND parameter registration allows the driver to normalize the variable name.

2. PDO class:

    • PDO: beginTransaction-start a transaction
    • PDO: commit-Submit a transaction
    • PDO ::__ construct-create a PDO instance indicating a database connection
    • PDO: errorCode-obtain the SQLSTATE related to the last operation of the database handle
    • PDO: errorInfo-returns the error message of the last database operation.
    • PDO: exec-execute an SQL statement and return the affected number of rows
    • PDO: getAttribute-retrieve the attributes of a Database Connection
    • PDO: getAvailableDrivers-returns an array of available drivers.
    • PDO: inTransaction-check whether it is in a transaction
    • PDO: lastInsertId-return the ID or Sequence Value of the last inserted row
    • PDO: prepare-the SQL statement to be executed by the slave node and returns a PDOStatement object.
    • PDO: query-executes the SQL statement and returns the PDOStatement object, which can be understood as a result set.
    • PDO: quote-add quotation marks to strings in SQL statements.
    • PDO: rollBack-roll back a transaction
    • PDO: setAttribute-set attributes
    • PDOStatement class:
      • PDOStatement: bindColumn-bind a column to a PHP variable
      • PDOStatement: bindParam-bind a parameter to the specified variable name
      • PDOStatement: bindValue-bind a value to a parameter
      • PDOStatement: closeCursor-close the cursor so that the statement can be executed again.
      • PDOStatement: columnCount-Number of columns in the returned result set
      • PDOStatement: debugDumpParams-print an SQL preprocessing command
      • PDOStatement: errorCode-obtain the SQLSTATE related to the last statement handle operation
      • PDOStatement: errorInfo-get the extension error information related to the last statement handle operation
      • PDOStatement: execute-execute a preprocessing statement
      • PDOStatement: fetch-get the next row from the result set
      • PDOStatement: fetchAll-returns an array containing all rows in the result set.
      • PDOStatement: fetchColumn-returns a separate column from the next row in the result set.
      • PDOStatement: fetchObject-get the next row and return it as an object.
      • PDOStatement: getAttribute-retrieve a statement attribute
      • PDOStatement: getColumnMeta-returns the metadata of a column in the result set.
      • PDOStatement: nextRowset-push to the next row set in a multi-row set statement handle
      • PDOStatement: rowCount-returns the number of rows affected by the previous SQL statement.
      • PDOStatement: setAttribute-set a statement attribute
      • PDOStatement: setFetchMode-set the default acquisition mode for the statement.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.