pdo usage study notes_PHP tutorial-PHP Tutorial-php.cn

1. Basic concepts

1. PDO: 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, queries and data can be obtained through consistent functions.

PDO is a "database access abstraction layer" that unifies the 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. Such as: MYSQL (PDO_MYSQL). A list of PDO parts can be viewed in the phpinfo() function.

2. PDO installation

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

–with-pdo-mysql=/usr/local/mysql ///usr/local/mysql is the mysql installation directory

2. Windows:

Find the php.ini file under C:windows

(1) Open: extension=php_pdo.dll

(2) Open: extension=php_pdo_mysql.dll

3. Using PDO process

1. Connect to database

(1) Create PDO object

(2) Set PDO behavior attributes (setattribute())

(3) Set character set ($link->query(‘set names UTF8’))

2. Send SQL statement

(1) Prepare SQL statement

(2) Execute sending

3. View results

4. Connect to database

1. Create PDO object:

(1)$link = new PDO(DSN, username, password, driver properties);

1) DSN: Data source name, used to define a driver that must be used and the database to be used. DSN format of mysql: ‘mysql:host=localhost;dbname=lamp30’

2) You can put the DSN in a file, such as: 'uri:file:///usr/local/dsn.txt'

3) Use the try...catch statement when creating an object, because when an error occurs when declaring a PDO instance, an exception will be automatically thrown. Such as:

The code is as follows

Copy code

代码如下

复制代码

try{

$link = new PDO(‘mysql:host=localhost;dbname=lamp30’,’root’,’111111’);

}catch(PDOException $e){

echo $e->getMessage();

exit(‘连接数据库错误.’);

}

try{

$link = new PDO(‘mysql:host=localhost;dbname=lamp30’,’root’,’111111’);

}catch(PDOException $e){

echo $e->getMessage();

exit(‘Error connecting to database.’);

}

2. Driver attributes

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

(2) There are Chinese explanations of these attributes on page P501 of the book.

3. Set character set: $link->query(‘set names UTF8’)

5. Send SQL statement

(1) $link->exec(): Execute additions, deletions, and modifications, and return the number of affected rows. If execution fails, return false or 0.

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

6. Query results

1. Non-query:

代码如下

复制代码

$stmt = $link->prepare(‘select * from user where id=:id’);

$stmt->bindparam(‘:id’, $id, PDO::PARAM_INT);

$id = 2;

$stmt->execute();

(1) Directly use the number of rows affected by the return of $link->exec() (2)$link->lastInsertId() returns the AUTO_INCREMENT number value generated by the last INSERT command 2. See preprocessing 7. Preprocessing 1. Step 2: Send SQL statement

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();

bindParam() parameters have the following 7 types: you don’t need to write

PDO::PARAM_INT

PDO::PARAM_STR

PDO::PARAM_BOOL

PDO::PARAM_NULL

PDO::PARAM_LOB: Large Object Data Type

PDO::PARAM_STMT: PDOstatement type

PDO::PARAM_INPUT_OUTPUT: Data type used by stored procedures

2. Step 3:

For example:

The code is as follows

Copy code

代码如下

复制代码

$stmt = $link->query(‘select * from user’);

（1）fetch()方法

$pdoStat ->bindColumn(1, $id); //第一个参数可以是从1开始的索引值

$pdoStat ->bindColumn(‘name’, $name); //也可以是列名

$pdoStat ->bindColumn(‘pass’, $pass);

while($row = $stmt ->fetch(PDO::FETCH_BOUND)){

echo $id.’ ’;

echo $name.’ ’;

echo $pass.’
’;

}

$stmt = $link->query(‘select * from user’);

(1) fetch() method

$pdoStat ->bindColumn(1, $id); //The first parameter can be an index value starting from 1

$pdoStat ->bindColumn(‘name’, $name); //It can also be a column name

代码如下

复制代码

$result = $stmt ->fetchall();

foreach($result as $row){

echo $row[‘id’].’ ’;

echo $row[‘name’].’ ’;

echo $row[‘pass’].’
’;

}

$pdoStat ->bindColumn(‘pass’, $pass);

while($row = $stmt ->fetch(PDO::FETCH_BOUND)){

echo $id.’ ’;

echo $name.’ ’;

echo $pass.’
’;

}

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

代码如下

复制代码

$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’,’111’)’);

//3

if($result){

$link->commit();

}else{

$link->rollback();

}

//4

$link->setattribute(PDO::ATTR_AUTOCOMMIT, true);

You can use the setFetchMode() method 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’].’ ’; }

Fetchall() parameters are the same as fetch(). 8. Transaction processing 1. Turn off automatic submission (modify in driver properties) 2. Open transaction 3. Commit transaction/rollback 4. Turn on 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’,’111’)’); //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): Set a driver attribute.

1) Because Oracle returns empty strings as NULL values, but other databases do not have this feature, in order to have better compatibility $link->setattribute(PDO::ATTR_ORACLE_NULLS,PDO::NULL_EMPTY_STRING,);

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

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

1) If the setattribute function sets the error display mode to static, nothing will be displayed when an error occurs. This function must be called to view the error number.

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

1) If the setattribute function sets the error display mode to static, nothing will be displayed when an error occurs. This function must be called to view the error message.

5. $link->lastinsertid(): Get the primary key value of the last data inserted into the table (if multiple pieces of data are inserted at the same time, return the ID of the first inserted row).

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

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

8. $link->commit(): Submit a transaction and execute a SQL.

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

10. Error mode

1. Static mode:

The code is as follows

代码如下	复制代码
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT)

Copy code

$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT)

代码如下	复制代码
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING)

(1) Default mode, no operation is performed when an error occurs, PDO will only set the error code.

(2) To view errors, you can call errorCode() and errorInfo(). Both PDO and PDOStatement classes have these two methods.

2. Warning mode:

3. Abnormal mode:

The code is as follows

代码如下	复制代码
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)

Copy code

$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING)

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

代码如下	复制代码
$link->setAttribute(PDO::ATTR_PERSISTENT, true);

(2) This is the way mysql and mysqli display errors.

The code is as follows

Copy code

$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) (1) In addition to setting the error code in this mode, PDO will also throw a PDOException and set its properties to reflect the error code and error information. (2) If an exception causes the script to terminate, the transaction will be automatically rolled back. (3) PDO recommends using this mode. 11. Persistent connection

The code is as follows

Copy code

$link->setAttribute(PDO::ATTR_PERSISTENT, true); A persistent connection will not automatically disconnect when the script execution ends, and the connection cannot be closed using $link->close().