Home > Article > php教程 > Front-end learning PDO basic operations of PHP

Front-end learning PDO basic operations of PHP

WBOYOriginal: 2016-12-05 13:26:251099browse

[1] Create PDO [2] Use PDO [3] Transaction processing

Previous words

　PDO (php data object) extension class library defines a lightweight and consistent interface for PHP to access the database. It provides a database access abstraction layer so that no matter what database is used, queries can be executed through consistent functions. and obtaining data, which greatly simplifies the operation of the database and can shield the differences between different databases. Using PDO can easily carry out the development of cross-database programs and transplantation between different databases, which will be the main focus of PHP in database processing in the future. Development direction, it can support mysql, postgresql, oracle, mssql and other databases

Create PDO object

When using PDO to interact with different database management systems, the member methods in the PDO object are to unify the access interfaces of various databases, so before using PDO to interact with the database, you must first create a PDO object. While creating an object through the constructor method, you need to establish a connection with the database server and select a database

　The prototype of PDO’s construction method is as follows

__construct ( string $dsn [,string $username [,string $password [,array $driver_options ]]] )

　In the construction method, the first required parameter is the data source name (dsn), which is used to define a certain database and the driver that must be used. DSN's PDO naming convention is the name of the PDO driver, followed by a colon, and then optional driver database connection variable information, such as host name, port and database name

　The second parameter username and the third parameter password in the construction method specify the username and password used to connect to the database respectively. The last parameter driver_options requires an array to specify all additional options required for the connection, passing additional tuning parameters to PDO or the underlying driver

/*连接如果失败，使用异常处理模式进行捕获 */
$dsn = 'mysql:dbname=pdotest;host=127.0.0.1'; //连接MySQL数据库的DSN 
$user = 'root'; //MySQL数据库的用户名
$password = '*****'; //MySQL数据库的密码
try { 
     $dbh = new PDO($dsn, $user, $password); 
} catch (PDOException $e) { 
      echo '数据库连接失败： ' . $e->getMessage(); 
}

When creating a PDO object, there are some options related to database connection. You can pass the necessary options to form the data to the fourth parameter driver_opts of the constructor to pass additional tuning parameters to PDO or the underlying driver. Program

 PDO::ATTR_AUTOCOMMIT): PDO是否关闭自动提交功能
 PDO::ATTR_ERRMODE): 当前PDO的错误处理的模式 
 PDO::ATTR_CASE): 表字段字符的大小写转： 
 PDO::ATTR_CONNECTION_STATUS): 与连接状态相关特有信息： 
 PDO::ATTR_ORACLE_NULLS): 空字符串转换为SQL的null 
 PDO::ATTR_PERSISTENT): 应用程序提前获取数据大 
 PDO::ATTR_SERVER_INFO): 与数据库特有的服务器信 
 PDO::ATTR_SERVER_VERSION): 数据库服务器版本号信息
 PDO::ATTR_CLIENT_VERSION): 数据库客户端版本号信息

//设置持久连接的选项数组作为最后一个参数,可以一起设置多个元素 
$opt = array(PDO::ATTR_PERSISTENT => true);　　 
try { 
       $db = new PDO('mysql:dbname=pdotest;host=127.0.0.1'，'root'，'*****',$opt); 
} catch (PDOException $e) { 
       echo "数据库连接失败： " .$e->getMessage(); 
}

Use PDO objects

Adjust the behavioral attributes of PDO

　There are many properties in the PDO object that are used to adjust the behavior of PDO or obtain the underlying driver status. If you do not pass the attribute option as the last parameter in the constructor when creating a PDO object, you can also set and obtain the values of these attributes through the setAttribute() and getAttribute() methods in the PDO object after the object is created

PDO::getAttribute()

　PDO::getAttribute() is used to retrieve the attributes of a database connection

mixed PDO::getAttribute ( int $attribute )

PDO::setAttribute()

　PDO::setAttribute() is used to set attributes

bool PDO::setAttribute ( int $attribute , mixed $value )

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$dbh->setAttribute(3,2); 
$dbh->setAttribute(PDO::ATTR_AUTOCOMMIT,0);//$dbh->setAttribute(0,0); 
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
//$dbh->setAttribute(19,2); 

echo "\nPDO是否关闭自动提交功能：". $dbh->getAttribute(PDO::ATTR_AUTOCOMMIT);
echo "\n当前PDO的错误处理的模式：". $dbh->getAttribute(PDO::ATTR_ERRMODE); 
echo "\n表字段字符的大小写转换： ". $dbh->getAttribute(PDO::ATTR_CASE); 
echo "\n与连接状态相关特有信息： ". $dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS); 
echo "\n空字符串转换为SQL的null：". $dbh->getAttribute(PDO::ATTR_ORACLE_NULLS); 
echo "\n应用程序提前获取数据大小：".$dbh->getAttribute(PDO::ATTR_PERSISTENT); 
echo "\n与数据库特有的服务器信息：".$dbh->getAttribute(PDO::ATTR_SERVER_INFO); 
echo "\n数据库服务器版本号信息：". $dbh->getAttribute(PDO::ATTR_SERVER_VERSION);
echo "\n数据库客户端版本号信息：". $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION);

Error handling

PDO provides a total of three different error handling modes, which can not only meet different styles of programming, but also adjust and extend the way of handling errors

PDO:ERRORMODE_SILENT

　This is the default mode, no action is taken when an error occurs, PDO will only set the error code. Developers can check statements and database objects through the errorCode() and errorInfo() methods in the PDO object. If the error occurs due to a call to a statement object, the errorCode() or errorInfo() method can be called on that statement object. If the error is caused by calling a database object, then the above two methods can be called on that database object

PDO:ERRMODE_WARNING

　In addition to setting the error code, PDO will also emit a PHP traditional E_WARNING message, which can be caught using regular PHP error handlers. This setup is useful in debugging or testing if you just want to see what went wrong without inadvertently interrupting the flow of your application

$dbh->setAttrbute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);//设置警告模式处理错误

PDO:ERRMODE_EXCEPTION

In addition to setting the error code, PDO will also throw a PDOException and set its properties to reflect the error code and error information. This setting is also useful in debugging, as it will zoom in on where in the script the error is occurring, making it possible to pinpoint problematic potential areas of the code very quickly. Another useful aspect of the exception pattern is that you can structure your own error handling more clearly than traditional PHP-style warnings, and rather than silently and explicitly checking the return value of each database call, the exception pattern Less code and nested code

$dbh->setAttrbute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);//设置异常模式处理错误

执行SQL语句

　　在使用PDO执行查询数据之前，先提供一组相关的数据。创建PDO对象并通过mysql驱动连接mysql数据库服务器，创建一个以'testdb'命名的数据库，并在该数据库中创建一个联系人信息表contactInfo

CREATE TABLE contactInfo(
    uid MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    departmentID CHAR(3) NOT NULL,
    address VARCHAR(80) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(20),
    PRIMARY KEY(uid)
);

　　数据表contactInfo建立之后，向表中插入多行记录

INSERT INTO contactInfo(name,departmentID,address,phone,email) VALUES ('张三','D01','朝阳','15011111234','zs@aaa.com'),('李四','D02','朝阳','15011112345','ls@aaa.com'),('王五','D02','海淀','15011113456','ww@aaa.com'),('赵四','D01','海淀','15011114567','zx@aaa.com');

PDO::exec()

　　PDO::exec()函数执行一条SQL语句，并返回受影响的行数

int PDO::exec ( string $statement )

　　当执行INSERT、UPDATE、DELETET等没有结果集的查询时，使用PDO对象中的exec()方法去执行。该方法成功执行后，将返回受影响的行数

php
try {
    //创建对象
    $dbh = new PDO("mysql:host=localhost;dbname=testdb", "root", "zhiaihebe0123");
}catch(PDOException $e) {
    echo "数据库连接失败：".$e->getMessage();
    exit;
}

$query = "UPDATE contactInfo SET phone='12345678900' WHERE name='张三'";
$affected = $dbh->exec($query);
if($affected){
    //数据表contactInfo中受影响的行数为:1
    echo '数据表contactInfo中受影响的行数为:' .$affected;
}else{
    print_r($dbh->errorInfo());
}
$query = "UPDATE contactInfo SET phone='123456789' WHERE (uid%2 = 0)";
$affected = $dbh->exec($query);
if($affected){
    //数据表contactInfo中受影响的行数为:2
    echo '数据表contactInfo中受影响的行数为:' .$affected;
}else{
    print_r($dbh->errorInfo());
}
?>

PDO::lastInsertId()

　　PDO::lastInsertId()函数用于返回最后插入行的ID或序列值

string PDO::lastInsertId ([ string $name = NULL ] )

php
try {
    //创建对象
    $dbh = new PDO("mysql:host=localhost;dbname=testdb", "root", "zhiaihebe0123");
}catch(PDOException $e) {
    echo "数据库连接失败：".$e->getMessage();
    exit;
}

try{
    $query = "INSERT INTO contactInfo(name,departmentID,phone,email) VALUES ('诸葛','D03','120120120','zg@aaa.com')";
    $affected = $dbh->exec($query);    
    echo $affected."
";//1
    echo $dbh->lastInsertId();//5
}catch(PDOException $e){
    echo "错误：" .$e->getMessage();
}
?>

PDO::query()

　　当执行返回结果集的SELECT查询时，或者所影响的行数无关紧要时，应当使用PDO对象中的query()方法。如果该方法成功执行指定的查询，则返回一个PDOStatement对象。如果使用了query()方法，并想了解获取的数据行总数，可以使用PDOStatement对象中的rowCount()方法获取

PDOStatement::rowCount()

　　PDOStatement::rowCount()函数返回受上一个 SQL 语句影响的行数

int PDOStatement::rowCount ( void )

php
try {
    //创建对象
    $dbh = new PDO("mysql:host=localhost;dbname=testdb", "root", "zhiaihebe0123");
}catch(PDOException $e) {
    echo "数据库连接失败：".$e->getMessage();
    exit;
}
$query = "SELECT name,phone,email FROM contactInfo WHERE departmentId='D01'";
try{
    $pdostatement = $dbh->query($query);    
    echo "一共从表中获取到".$pdostatement->rowCount()."条记录:
";
    foreach($pdostatement as $row){
        echo $row['name'] ."\t";
        echo $row['phone'] ."\t";
        echo $row['email'] ."
";
    }
}catch (PDOException $e){
    echo $e->getMessage();
}
?>

事务处理

　　事务是确保数据库一致的机制，是一个或一系列的查询，作为一个单元的一组有序的数据库操作。如果组中的所有SQL语句都操作成功，则认为事务成功，事务则被提交，其修改将作用于所有其他数据库进程。即使在事务的组中只有一个环节操作失败，事务也不成功，则整个事务将被回滚，该事务中所有操作都被取消。事务功能是企业级数据库的一个重要部分，因为很多业务过程都包括多个步骤。如果任何一个步骤失败，则所有步骤都不应发生。事务处理有4个特征：原子性(Atomicity)、一致性(Consistency)、独立性(Isolation)和持久性(Durability)，即ACID。对于在一个事务中执行的任何工作，即使它是分阶段进行的，也一定可以保证该工作会安全地应用于数据库，并且在工作被提交时，不会受到其他连接的影响

　　MySQL目前只有InnoDB和BDB两个数据库表类型才支持事务，两个表类型具有相同的特性，InnoDB表类型具有比BDB还丰富的特性，速度更快，因此建议使用InnoDB表类型。创建InnoDB类型的表实际上与创建任何其他类型表的过程没有区别，如果数据库没有设置为默认的表类型，只要在创建时显式指定要将表创建为InnoDB类型

　　要实现事务处理，首先要使用InnoDB引擎

ALTER TABLE contactInfo engine=innodb;

　　在默认的情况下，MySQL是以自动提交(autocommit)模式运行的，这就意味着所执行的每一个语句都将立即写入数据库中。但如果使用事务安全的表格类型，是不希望有自动提交的行为的，所以要在当前的会话中关闭自动提交

SET AUTOCOMMIT = 0;//在当前的会话中关闭自动提交

　　如果提交被打开了，必须开启一个事务；如果自动提交是关闭的，则不需要使用这条命令，因为输入一个SQL命令时，一个事务将自动启动

START TRANSACTION;//开启一个事务

　　在完成了一组事务的语句输入后，需要提交一个事务，该事务才能在其他会话中被其他用户所见

COMMIT;//提交一个事务给数据库

　　如果改变注意，可以回滚到以前的状态

ROOLBACK;//事务被回滚，所有操作都被取消

　　事务处理完成后，再次开启自动提交

SET AUTOCOMMIT = 1;

　　下面在PHP中进行事务处理操作，对张三和李四进行部门交换来轮岗培养

php
try {
    //创建对象
    $dbh = new PDO("mysql:host=localhost;dbname=testdb", "root", "zhiaihebe0123");
    //设置错误使用异常的模式
    $dbh -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //关闭自动提交
    $dbh-> setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
}catch(PDOException $e) {
    echo "数据库连接失败：".$e->getMessage();
    exit;
}
try {
    //开启一个事务
    $dbh -> beginTransaction();
    $affected_rows = $dbh->exec("UPDATE contactInfo set departmentID = 'D02' where uid=1");
    if($affected_rows > 0) {
        echo "张三转岗成功!
";
    } else {
        throw new PDOException("张三转岗失败！
");
    }
    $affected_rows = $dbh-> exec("UPDATE contactInfo set departmentID = 'D01' where uid=2");
    if($affected_rows) {
        echo "李四转岗成功!
";
    }else {
        throw new PDOException("李四转岗失败！
");
    }
    echo "轮岗成功!
";
    //提交以上的操作
    $dbh->commit();    
}catch(PDOException $e) {
    echo "错误：".$e->getMessage();
    echo "转岗失败!
";
    //撤销所有操作
    $dbh -> rollback();
}
//运行完成以后， 最后开启自动提交
$dbh-> setAttribute(PDO::ATTR_AUTOCOMMIT, 1);
?>

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Detailed explanation of how XAMPP modifies Apache's default website directory htdocsNext article：Detailed explanation of how XAMPP modifies Apache's default website directory htdocs

See more

Front-end learning PDO basic operations of PHP

Table of Contents

Previous words

Create PDO object

Use PDO objects

事务处理

Related articles