Getting Started with PHP (15) using PDO to manipulate MySQL database

What is PDO?

PDO (PHP Data Object) provides a lightweight interface to access the PHP database. PDO provides an abstraction layer of data access, which means that the database can be manipulated in the same way no matter what database is used.

You must modify the php.ini file before using PDO

Ensure that the preceding two lines of the semicolon are removed

Extension=php_pdo.dllextension=php_pdo_mysql.dll

1. Connecting to a database

<?PHP$DSN = "mysql:dbname=mydb;host=127.0.0.1";/* defines the name of the data source DSN (datasource name) */$user = "root"; $password = "123456" ; try{$dbh = new PDO ($DSN, $user, $password);} catch (Pdoexception $e) {/* If the connection is incorrect, a Pdoexception exception is thrown */echo "Connection failed!". $e->getmessage ();}

How do I close a connection?

$DBH = null;/* Note that all objects related to the PDO object should also be closed, which is set to null*/

How do I set up a persistent connection to a database?

$DBH = new PDO (' odbc:sample ', ' db2inst1 ', ' ibmdb2 ', array (pdo::attr_persistent = true));/* Not recommended for persistent connections */

2. Execute SQL statements

Use the query () method of the PDO object

$DBH = new PDO ($DSN, $user, $password), foreach ($dbh->query ("SELECT * from Test") as $row) {/* Execute query statement */echo $row ["id"]. " <br> ";}

If you need additional SQL statements, simply pass the SQL statement to the query () method to execute. For example

$DBH->query ("INSERT into test values (10000)");

3. Committing transactions and rolling back transactions

$DBH->begintransaction (); /* Start a transaction */$DBH->exec ("INSERT into Test values"), $DBH->commit ();/* Commit a transaction */

$DBH->begintransaction (); $dbh->exec ("INSERT into Test values"); $dbh->rollback ();/* Undo a transaction 48 not inserted */

4. Precompiled statements

Using precompiled statements to dynamically bind parameters to SQL statements at execution time can have two benefits

1. SQL statements only need to be parsed once, can be executed multiple times by binding different parameters, saving system resources.

2. Effective in preventing SQL injection.

$stmt = $dbh->prepare ("INSERT INTO test values (: ID)");/* Precompiled statement */$stmt->bindparam (": id", $id);/* Binding parameter */$id = 111;$ Stmt->execute ();/* Execute */

$stmt = $dbh->prepare ("INSERT into test values (?)"); * Precompiled statements can also be used as placeholders */$stmt->bindparam (1, $id);/* Binding parameters */$id = 111; $stmt->execute ();/* Execute */

Execute a precompiled query statement

$stmt = $dbh->prepare ("SELECT * from Test where id=?"); $stmt->execute (Array (111));/* parameter must be array type */while ($row = $stmt->fetch ()) {/* fetch result */print $row ["id"];}

Note that the following placeholder is specified as invalid.

<?php$stmt = $dbh->prepare ("select * from REGISTRY where name is like '%?% '");/note that the placeholder must occupy the position of the entire value/$stmt->execute ( Array ($_get[' name '));/* The following designation is the correct */$stmt = $DBH->prepare ("select * from REGISTRY where name like?"); $stmt->execute (Array ("%$_get[name]%"));

For more usage of PDO, find your own PHP mannual.

This article is from the "thick Product Thin Hair" blog, please make sure to keep this source http://joedlut.blog.51cto.com/6570198/1857118

Getting Started with PHP (15) using PDO to manipulate MySQL database