PHP PDO transaction preprocessing Gao Luofeng details PHP

Three modes of PDO error handling

/ * * Database abstraction layer PDO * A DSN consists of the PDO driver name, followed by a colon, and the connection syntax of the specific PDO driver. * method of executing SQL statement in * PDO::ERRMODE_SILENT * PDO::ERRMODE_WARNING * PDO::ERRMODE_EXCEPTION * * PDO * 1.exec () is used to deal with insert update delete create return influence rows that are not result sets * 2.query () is used to process select desc show with result sets * * both can * $pdo- > exec ('set names utf8'); * $pdo- > query (' set names utf8') * * / / mysql_connect ('localhost','root','root'); / / mysql_select_db (' test'); $dsn = 'mysql:dbname=test;host=localhost'; $username =' root'; $passwd = 'root'; $options = array (PDO::ATTR_AUTOCOMMIT= > true, PDO::ATTR_PERSISTENT= > true) Try {$pdo = new PDO ($dsn, $username, $passwd, $options);} catch (PDOException $e) {echo "database connection failed:. $e-> getMessage (); exit;} echo 'PDO object created successfully!' ; echo $pdo- > getAttribute (PDO::ATTR_CLIENT_VERSION).''; echo $pdo- > getAttribute (PDO::ATTR_SERVER_VERSION).''; echo $pdo- > getAttribute (PDO::ATTR_AUTOCOMMIT).''; echo $pdo- > getAttribute (PDO::ATTR_CASE).''; echo $pdo- > getAttribute (PDO::ATTR_CONNECTION_STATUS).''; echo $pdo- > getAttribute (PDO::ATTR_DRIVER_NAME). Echo $pdo- > getAttribute (PDO::ATTR_PERSISTENT).''; / * $query = "delete from helloworld"; $affected_rows = $pdo- > exec ($query); / / error handling mode of PDO (default is not prompted, we may not see the problem) PDO::ERRMODE_SILENT if (! $affected_rows) {echo $pdo- > errorCode (); echo'' Echo'; print_r ($pdo- > errorInfo ()); echo'';} / warning mode $pdo- > setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); echo $pdo- > getAttribute (PDO::ATTR_ERRMODE) * / / exception mode $pdo- > setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try {/ / use the method in PDO to execute the statement / / insert $query = "insert into users (username,password,email) values ('xiaowang','123','xiaoming@163.com')"; $affected_rows = $pdo- > exec ($query) Echo $pdo- > lastInsertId (); / / query $query = "select * from users"; $result = $pdo- > query ($query); var_dump ($result);} catch (PDOException $e) {echo 'error cause:'. $e-> getMessage ();}

PDO transaction processing

/ * * PDO transaction * / $dsn = 'mysql:dbname=test;host=localhost';$username =' root';$passwd = 'root';$options = array (PDO::ATTR_AUTOCOMMIT= > true, PDO::ATTR_PERSISTENT= > true); try {/ / create object $pdo = new PDO ($dsn, $username, $passwd, $options) / / set the exception mode $pdo- > setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); / / turn off autocommit (use the first step of the transaction) $pdo- > setAttribute (PDO::ATTR_AUTOCOMMIT, false);} catch (PDOException $e) {echo "database connection failed:". $e-> getMessage (); exit;} try {/ / Open a transaction $pdo- > beginTransaction (); $price = 50 $statement = "update demo set salary=salary- {$price} where id=1"; $aura exec-> exec ($statement); if ($a > 0) {echo 'transferred out successfully!' ;} else {throw new PDOException ('transfer failed!') ;} $statement = "update demo set salary=salary+ {$price} where id=3"; $baked roompdo-> exec ($statement); if ($b > 0) {echo 'transferred successfully!' ;} else {throw new PDOException ('transfer failed!') ;} echo 'deal succeeded!' ; / / submit the above action $pdo- > commit ();} catch (PDOException $e) {echo 'error reason:'. $e-> getMessage (); echo 'transaction failed!' ; / / undo all operations $pdo- > rollBack ();} / / enable auto-commit $pdo- > setAttribute (PDO::ATTR_AUTOCOMMIT, true) after the operation is completed

PDO pair preprocessing statement

/ * * PDO pair preprocessing statement * 1. Efficiency problem compilation times * 2. Security problems prevent SQL from injecting 1 or 1 GET 1'* $query = "delete from demo where id=' {$_ GET ['id']}'"; * * / $dsn = 'mysql:dbname=test;host=localhost';$username =' root';$passwd = 'root';$options = array (PDO::ATTR_AUTOCOMMIT= > true, PDO::ATTR_PERSISTENT= > true) Try {/ / create object $pdo = new PDO ($dsn, $username, $passwd, $options); / / set the exception mode $pdo- > setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);} catch (PDOException $e) {echo "database connection failed:". $e-> getMessage (); exit;} try {$statement = "insert into users (username,password,email) values (?,?)"; / /? Indicates that the placeholder / / goes directly to the database management system and executes / / $pdo- > query ($statement); / / to the database management system, compiles and waits for not executing $pdostatement=$pdo- > prepare ($statement); / / binds the parameter (?) Associate a question mark with a variable for preprocessing statements that use the question mark placeholder, it should be the parameter position that starts the index with 1. $pdostatement- > bindParam (1, $username); $pdostatement- > bindParam (2, $password); $pdostatement- > bindParam (3, $email); / / assign values to variables $username = 'lisiming1'; $password =' 1234 variables; $email = 'lisiming@163.com'; / / PDOStatement::execute-execute a preprocessing statement $pdostatement- > execute (); $username =' lisiming2' $password = '1234'; $email = 'lisiming@163.com'; $pdostatement- > execute ();} catch (PDOException $e) {echo' error reason:'. $e-> getMessage ();} = / * PDO pair preprocessing statement * 1. Efficiency problem compilation times * 2. Security problems prevent SQL from injecting 1 or 1 GET 1'* $query = "delete from demo where id=' {$_ GET ['id']}'"; * * / $dsn = 'mysql:dbname=test;host=localhost';$username =' root';$passwd = 'root';$options = array (PDO::ATTR_AUTOCOMMIT= > true, PDO::ATTR_PERSISTENT= > true) Try {/ / create object $pdo = new PDO ($dsn, $username, $passwd, $options); / / set the exception mode $pdo- > setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);} catch (PDOException $e) {echo "database connection failed:". $e-> getMessage (); exit;} try {$statement = "insert into users (username,password,email) values (: username,:password,:email)" / /: parameter name in the form of name / / directly to the database management system and execute / / $pdo- > query ($statement); / / to the database management system, compile and wait for no execution of $pdostatement=$pdo- > prepare ($statement); / / bind parameters (?) Associate a question mark with a variable for preprocessing statements that use the question mark placeholder, it should be the parameter position that starts the index with 1. $pdostatement- > bindParam (': username', $username,PDO::PARAM_STR); $pdostatement- > bindParam (': password', $password,PDO::PARAM_STR); $pdostatement- > bindParam (': email', $email,PDO::PARAM_STR); / / assign $username = 'lisiming3'; $password =' 1234 variables; $email = 'lisiming@163.com' / / PDOStatement::execute-execute a preprocessing statement $pdostatement- > execute (); $username = 'lisiming4'; $password =' 1234'; $email = 'lisiming@163.com'; $pdostatement- > execute ();} catch (PDOException $e) {echo' error cause:'. $e-> getMessage () } = $statement= "insert into users (username,password,email) values (?,?)"; / / to the database management system, compiling and waiting for not executing $pdostatement=$pdo- > prepare ($statement); $pdostatement- > execute (array ('xiaoli','123','xiaoli@163.com')); = $statement= "insert into users (username,password,email) values (: username,:password,:email)" / / to the database management system, compiling and waiting for no execution of $pdostatement=$pdo- > prepare ($statement); $pdostatement- > execute ('username'= >' xiaoli1','password'= > '1123aaq' journal emailment = > 'xiaoli1@163.com')); = / * PDO for preprocessing statements * 1. Efficiency problem compilation times * 2. Security problems prevent SQL from injecting 1 or 1 id' 1'* $query = "delete from demo where id=' {$_ GET ['id']}'"; * fetch () * fetchAll () * setFetchMode () * / $dsn = 'mysql:dbname=test;host=localhost';$username =' root';$passwd = 'root';$options = array (PDO::ATTR_AUTOCOMMIT= > true, PDO::ATTR_PERSISTENT= > true) Try {/ / create object $pdo = new PDO ($dsn, $username, $passwd, $options); / / set the exception mode $pdo- > setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);} catch (PDOException $e) {echo "database connection failed:". $e-> getMessage (); exit;} try {$statement = "select * from users where id >? And id

< ?"; //给数据库管理系统，编译后等待 没有执行 $pdostatement=$pdo->

Prepare ($statement); $pdostatement- > execute (array (3p6)); / / set the result mode $pdostatement- > setFetchMode (PDO::FETCH_NUM); echo''; while (list ($id,$username,$password,$email) = $pdostatement- > fetch ()) {echo'; echo'. $id.''; echo'. $username.'' Echo'. $password.''; echo'. $email.''; echo';} echo';} catch (PDOException $e) {echo 'error reason:'. $e-> getMessage ();} = / / bind column $pdostatement- > bindColumn (1, $id); $pdostatement- > bindColumn (2, $username) $pdostatement- > bindColumn (3, $password); $pdostatement- > bindColumn (4, $email); = / / bind columns $pdostatement- > bindColumn ('id', $id); $pdostatement- > bindColumn (' username', $username); $pdostatement- > bindColumn ('password', $password); $pdostatement- > bindColumn (' email', $email)