How to use pdo in php

pdo in php is an extended class library that can define a lightweight, consistent interface for php. Its usage is as follows: 1. Create a php sample file; 2. Through "try{$link = new PDO(.);}catch(PDOException $e){...}" to create a pdo object; 3. Execute the query through "$link->query('select *from user');" and return the results Just set objects.

This operating system environment: Linux5.18.14 system, Dell G3 computer.

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. Use PDO process

1. Connect to the database

2. Create a PDO object:

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

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. For example:

The code is as follows

try{
$link = new PDO(‘mysql:host=localhost;dbname=lamp30’,’root’,’111111’);
}catch(PDOException $e){
echo $e->getMessage();
exit(‘连接数据库错误.’);
}

3. Drive attribute

(1) You can form an array with several necessary options (the attribute name is used as the element key, and the attribute value is used as the element value) is passed 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 the execution fails, it returns false or 0.

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

6. Query results

1. Non-query:

(1) Directly use the affected rows returned by $link->exec() Number

(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

$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 the stored procedure

2. Step 3:

For example:

The code is as follows

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

(1)fetch() method

$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.’
’;
}

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

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

(2)fetchall() method

The code is as follows

$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

$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. In the PDO object Member methods

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, and 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()：发送准备的SQL语句，返回PDOStatement对象。

7、$link->begintransaction()：打开事务。

8、$link->commit()：提交一个事务，执行一个SQL。

9、$link->rollback()：回滚一个事务。

十、错误模式

1、静态模式：

代码如下

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

(1) 默认模式，在错误发生时不进行任何操作，PDO将只设置错误代码。

(2) 查看错误可以调用errorCode()和errorInfo()，PDO和PDOStatement类都有这两个方法。

2、警告模式：

代码如下

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

(1)此模式在设置错误代码以外，PDO还将发出一条PHP传统的E_WARNING消息。

(2)这是mysql和mysqli显示错的方式。

3、异常模式：

代码如下

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

(1) 此模式在设置错误代码以外，PDO还将抛出一个PDOException，并设置其属性，以反映错误代码和错误信息。

(2) 如果异常导致脚本终止，则事务将自动回滚。

(3) PDO推荐使用此模式。

十一、持久连接

代码如下

$link->setAttribute(PDO::ATTR_PERSISTENT, true);

持久连接即当脚本执行结束时不会自动断开连接，而且用$link->close()不能关闭连接。

The above is the detailed content of How to use pdo in php. For more information, please follow other related articles on the PHP Chinese website!