Pdo usage learning Notes

I. Basic concepts

1. PDO: the abbreviation of PHP Data Object. The PDO extension class library defines a lightweight, consistent interface for PHP, which provides a data access abstraction layer so that no matter what database is used, you can use consistent functions to query and obtain data.

PDO is a "database access abstraction layer", which is used to unify access interfaces of various databases.

2. Operations on any database are not performed using the PDO extension itself, and must be accessed using specific PDO drivers for different database servers. For example, MYSQL (PDO_MYSQL ). You can view the list of PDO parts in the phpinfo () function.

II. PDO installation

1. Linux: add the following flag to the configure command when installing PHP:

-With-pdo-mysql =/usr/local/mysql // where/usr/local/mysql is the mysql installation directory

2. Windows:

Find the php. Ini file in C: windows.

(1) Open extension = php_pdo.dll

(2) Open extension = php_pdo_mysql.dll

3. Use PDO process

1. Connect to the database

(1) create a PDO object

(2) setattribute ())

(3) set the character set ($ link-> query ('set names utf8 '))

2. Send SQL statements

(1) prepare SQL statements

(2) execute sending

3. View Results

4. Connect to the database

1. Create a PDO object:

(1) $ link = new PDO (DSN, user name, password, driver attribute );

1) DSN: data source name, used to define a required driver and database to be used. The DSN format of mysql: 'MySQL: host = localhost; dbname = lamp30'

2) you can put the DSN in a file, for example, 'uri: file: // usr/local/dsn.txt'

3) try... Catch statement, because an exception is automatically thrown when an error occurs when the PDO instance is declared. For example:

The code is as follows:	Copy code
Try { $ Link = new PDO ('MySQL: host = localhost; dbname = lamp30', 'root', '123 '); } Catch (PDOException $ e ){ Echo $ e-> getMessage (); Exit ('database connection error .'); }

2. Driver attributes

(1) you can pass the required options to the fourth parameter of the constructor (attribute name as the element key and attribute value as the element value. If the driver attribute is not defined in the constructor, you can use the setattribute () function of the PDO class to define each attribute.

(2) these attributes are interpreted in Chinese on p501.

3. set the character set: $ link-> query ('set names utf8 ')

5. Send SQL statements

(1) $ link-> exec (): execute add, delete, and modify operations, return the number of affected rows, and return false or 0 if execution fails.

(2) $ link-> query (): executes the query and returns the PDOStatement result set object.

6. Query results

1. Non-query:

(1) directly use $ link-> exec () to return the affected rows

(2) $ link-> lastInsertId () returns the AUTO_INCREMENT number value generated by the last INSERT command.

2. See preprocessing.

VII. Preprocessing

1. Step 2: send SQL statements

The code is as follows:	Copy code
$ Stmt = $ link-> prepare ('select * from user where id =: ID '); $ Stmt-> bindparam (': ID', $ id, PDO: PARAM_INT ); $ Id = 2; $ Stmt-> execute ();

The bindParam () parameter has the following seven types:

PDO: PARAM_INT

PDO: PARAM_STR

PDO: PARAM_BOOL

PDO: PARAM_NULL

PDO: PARAM_LOB: big object data type

PDO: PARAM_STMT: PDOstatement type

PDO: PARAM_INPUT_OUTPUT: data type used by the stored procedure

2. Step 3:

For example:

The code is as follows:	Copy code
$ Stmt = $ link-> query ('select * from user '); (1) fetch () method $ PdoStat-> bindColumn (1, $ id); // The first parameter can be the index value starting from 1. $ PdoStat-> bindColumn ('name', $ name); // It can also be a column name. $ PdoStat-> bindColumn ('pass', $ pass ); While ($ row = $ stmt-> fetch (PDO: FETCH_BOUND )){ Echo $ id .''; Echo $ name .''; Echo $ pass. '<br> '; }

There are six fetch () parameters: see the manual.

You can use setFetchMode () to set the default mode.

(2) fetchall () method

The code is as follows:	Copy code
$ Result = $ stmt-> fetchall (); Foreach ($ result as $ row ){ Echo $ row ['id']. ''; Echo $ row ['name']. ''; Echo $ row ['pass']. '<br> '; }

The Fetchall () parameter is the same as fetch.

VIII. Transaction processing

1. Disable automatic submission (modify in driver attributes)

2. Open a transaction

3. Commit transactions/Rollback

4. Enable automatic submission

For example:

The code is as follows:	Copy code
$ Link = new PDO ('MySQL: host = localhost; dbname = lamp30 '); // 1 $ Link-> setattribute (PDO: ATTR_AUTOCOMMIT, false ); // 2 $ Link-> begintransaction (); $ Result = $ link-> exec ('Insert into user (name, paa) values ('wsy', '123 ')'); // 3 If ($ result ){ $ Link-> commit (); } Else { $ Link-> rollback (); } // 4 $ Link-> setattribute (PDO: ATTR_AUTOCOMMIT, true );

9. Member methods in PDO objects

1. $ link-> getattribute (attribute name): get a driver attribute.

2. $ link-> setattribute (attribute name, attribute value): sets a driver attribute.

1) because oracle returns NULL strings, other databases do not have this feature. For better compatibility, $ link-> setattribute (PDO: ATTR_ORACLE_NULLS, PDO :: NULL_EMPTY_STRING ,);

2) There are three ways to display errors: static, WARNING, and exception.

3. $ link-> errorcode (): get the error code.

1) if the setattribute function sets the display error method to static, nothing is displayed when an error occurs. You must call this function to view the error number.

4. $ link-> errorinfo (): Get error information (array ).

1) if the setattribute function sets the display error method to static, nothing is displayed when an error occurs. You must call this function to view the error information.

5. $ link-> lastinsertid (): obtain the value of the last primary key of the data inserted to the table. (if multiple data entries are inserted at the same time, the ID of the first inserted row is returned ).

6. $ link-> prepare (): sends the prepared SQL statement and returns the PDOStatement object.

7. $ link-> begintransaction (): Open the transaction.

8. $ link-> commit (): submit a transaction and execute an SQL statement.

9. $ link-> rollback (): rolls back a transaction.

10. Incorrect mode

1. Static mode:

The code is as follows:	Copy code
$ Link-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_SILENT)

(1) in the default mode, no operation is performed when an error occurs. PDO only sets the error code.

(2) you can call the errorCode (), errorInfo (), PDO, and PDOStatement classes to view errors.

2. Warning mode:

The code is as follows:	Copy code
$ Link-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_WARNING)

(1) In this mode, in addition to setting error code, PDO will also send a traditional PHP E_WARNING message.

(2) This is an error in mysql and mysqli.

3. Exception mode:

The code is as follows:	Copy code
$ Link-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION)

(1) In this mode, PDO throws a PDOException and sets its properties to reflect the error code and information.

(2) if the script is terminated due to an exception, the transaction will be automatically rolled back.

(3) This mode is recommended for PDO.

11. Persistent connections

The code is as follows:	Copy code
$ Link-> setAttribute (PDO: ATTR_PERSISTENT, true );

Persistent connection means that the connection will not be automatically closed when the script execution ends, and the connection cannot be closed with $ link-> close.