PHPPDO study notes lib.culog.cn November 13, 2007 09:36 Author: Liu Shui Meng Chun [Large, Medium, Small]
■What is PDO?
The POD (PHP Data Object) extension was added in PHP5. In PHP6, PDO will be used by default to connect to the database. All non-PDO extensions will be removed from the extension in PHP6. This extension provides PHP built-in class PDO to access the database. Different databases use the same method name to solve the problem of inconsistent database connections.
I configured it for development under windows.
■PDO's goal is to provide a lightweight, clear, and convenient API that unifies the common features of various RDBMS libraries, but does not exclude more advanced features. Provides an optional greater degree of abstraction/compatibility via PHP scripts.
■Features of PDO:
performance. PDO learned from the beginning about the successes and failures of scaling existing databases. Because PDO's code is brand new, we have the opportunity to redesign performance from the ground up to take advantage of PHP 5's latest features. ability. PDO is designed to provide common database functionality as a foundation while providing easy access to the unique features of an RDBMS. Simple. PDO is designed to make working with databases easy for you. The API doesn't force its way into your code and makes it clear what each function call does. Extensible at runtime. The PDO extension is modular, enabling you to load drivers for your database backend at runtime without having to recompile or reinstall the entire PHP program. For example, the PDO_OCI extension implements the Oracle database API instead of the PDO extension. There are also drivers for MySQL, PostgreSQL, ODBC, and Firebird, with more in development.
■Install PDO
What I have here is a PDO extension for development under WINDOWS. If you want to install and configure it under Linux, please look elsewhere.
Version requirements:
It is already included in the program package of php5.1 and later versions;
For php5.0.x, you need to download it from pecl.php.net and put it in your extension library, which is the ext folder of the folder where PHP is located;
The manual says that versions prior to 5.0 cannot run PDO extensions.
Configuration:
Modify your php.ini configuration file so that it supports pdo. (If you don’t understand php.ini, first find out that you need to modify the php.ini displayed when calling your phpinfo() function)
Bundle
Remove the semicolon in front of extension=php_pdo.dll. The semicolon is the php configuration file comment symbol. This extension is necessary.
There are more
;extension=php_pdo.dll
;extension=php_pdo_firebird.dll
;extension=php_pdo_informix.dll
;extension=php_pdo_mssql.dll
;extension=php_pdo_mysql.dll
;extension=php_pdo_oci.dll
;extension=php_pdo_oci8.dll
;extension=php_pdo_odbc.dll
;extension=php_pdo_pgsql.dll
;extension=php_pdo_sqlite.dll
The database corresponding to each extension is:
Driver nameSupported databasesPDO_DBLIBFreeTDS / Microsoft SQL Server / SybasePDO_FIREBIRDFirebird/Interbase 6PDO_INFORMIXIBM Informix Dynamic ServerPDO_MYSQLMySQL 3.x/4.xPDO_OCIOracle Call InterfacePDO_ODBCODBC v3 (IBM DB2, unixODBC and win32 ODBC)PDO_PGSQLPostgreSQLPDO_SQLITESQLite 3 and SQLite 2
Which database you want to use, just put the corresponding Just remove the comment symbol ";" before expansion.
■Using PDO
I assume here that you have installed mysql. If not, please find a way to install it first. Mine is mysql5.0.22, and others who use MySQL 4.0.26 can also use it.
★Database connection:
We use the following example to analyze the PDO connection database,
<?php
$dbms='mysql'; //Database type Oracle uses ODI. For developers, if you use different databases, you only need to change this, and you don’t have to remember so many functions.
$host='localhost';//Database host name
$dbName='test'; //Database used
$user='root'; //Database connection user name
$pass=''; //Corresponding password
$dsn="$dbms:host=$host;dbname=$dbName";
//
try{
$dbh=newPDO($dsn,$user,$pass);//Initializing a PDO object means creating the database connection object $dbh
echo "Connection successful<br/>";
/*You can also perform a search operation
foreach($dbh->query('SELECT * from FOO')as$row){
print_r($row);//You can use echo($GLOBAL); to see these values
}
*/
$dbh=null;
}catch(PDOException$e){
die("Error!: ".$e->getMessage()."<br/>");
}
//By default, this is not a long connection. If you need a long connection to the database, you need to add a parameter at the end: array(PDO::ATTR_PERSISTENT => true). It becomes like this:
$db=newPDO($dsn,$user,$pass,array(PDO::ATTR_PERSISTENT=>true));
?>
★Database query:
We have already performed a query above, and we can also use the following query:
<?php
$db->setAttribute(PDO::ATTR_CASE,PDO::CASE_UPPER); //Set attributes
$rs=$db->query("SELECT * FROM foo");
$rs->setFetchMode(PDO::FETCH_ASSOC);
$result_arr=$rs->fetchAll();
print_r($result_arr);
?>
Because the setAttribute() method is used above, the two parameters are put in to force the field name to uppercase. The following are the parameters of PDO::setAttribute():
PDO::ATTR_CASE: Force the column name to be in a format, as detailed below (second parameter):
PDO::CASE_LOWER: Force the column name to be lowercase.
PDO: :CASE_NATURAL: Column names follow the original way
PDO::CASE_UPPER: Force column names to uppercase.
PDO::ATTR_ERRMODE: Error message.
PDO::ERRMODE_SILENT: Does not display error information, only error code.
PDO::ERRMODE_WARNING: Displays warning error.
PDO::ERRMODE_EXCEPTION: Throws exception.
PDO::ATTR_ORACLE_NULLS (valid not only for ORACLE, but also for other databases): ) specifies the corresponding value in php for the NULL value returned by the database.
PDO::NULL_NATURAL: unchanged.
PDO::NULL_EMPTY_STRING: Empty string is converted toNULL.
PDO::NULL_TO_STRING: NULL is converted to an empty string.
PDO::ATTR_STRINGIFY_FETCHES: Convert numeric values to strings when fetching. Requires bool.
PDO::ATTR_STATEMENT_CLASS: Set user-supplied statement class derived from PDOStatement. Cannot be used with persistent PDO instances. Requiresarray(string classname, array(mixed constructor_args)) .
PDO::ATTR_AUTOCOMMIT(available in OCI, Firebird and MySQL): Whether to autocommit every single statement.
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY(available in MySQL): Use buffered queries.
$rs->setFetchMode(PDO::FETCH_ASSOC); in the example is PDOStatement::setFetchMode(), a declaration of the return type.
There are as follows:
PDO::FETCH_ASSOC-- associative array form
PDO::FETCH_NUM -- Numeric index array form
PDO::FETCH_BOTH -- Both are available in array form, which is the default
PDO::FETCH_OBJ -- in the form of an object, similar to the previous mysql_fetch_object(). For
more return type declarations (PDOStatement::method name), see the manual.
★Insert, update, delete data,
$db->exec("DELETE FROM `xxxx_menu` where mid=43");
To briefly summarize the above operations:
Query operations are mainly PDO::query(), PDO::exec(), PDO::prepare().
PDO::query() is mainly used for operations that return recorded results, especially SELECT operations.
PDO::exec() is mainly for operations that do not return a result set, such as INSERT, UPDATE, DELETE and other operations. The result it returns is the number of columns affected by the current operation.
PDO::prepare() is mainly a preprocessing operation. You need to use $rs->execute() to execute the SQL statement in the preprocessing. This method can bind parameters and is quite powerful. It cannot be explained simply in this article. Everyone You can refer to manuals and other documentation.
The main operations for obtaining the result set are: PDOStatement::fetchColumn(), PDOStatement::fetch(), PDOStatement::fetchALL().
PDOStatement::fetchColumn() is a field of the first record specified in the fetch result. The default is the first field.
PDOStatement::fetch() is used to obtain a record.
PDOStatement::fetchAll() is to get all the record sets into one. To obtain the results, you can set the type of the required result set through PDOStatement::setFetchMode.
There are also two surrounding operations, one is PDO::lastInsertId() and PDOStatement::rowCount(). PDO::lastInsertId() returns the last insertion operation, and the primary key column type is the last auto-increment ID.
PDOStatement::rowCount() is mainly used for the result set affected by the DELETE, INSERT, and UPDATE operations of PDO::query() and PDO::prepare(), and is invalid for the PDO::exec() method and SELECT operations.
★Transactions and automatic submission
At this point, you have connected to mysql through PDO. Before issuing queries, you should understand how PDO manages transactions. If you have not been exposed to transactions before, you must first know the four characteristics of transactions: Atomicity, Consistency, Isolation and Durability, that is, ACID. In layman's terms, for any work performed within a transaction, even if it is performed in stages, there is a guarantee that the work will be safely applied to the database and will not be affected by requests from other connections while the work is being submitted. influence. Transactional work can be automatically undone on request (assuming you haven't committed it yet), which makes error handling in scripts much easier.
Transactions are usually implemented by accumulating a batch of changes and making them effective at the same time. The advantage of this is that it can greatly improve the efficiency of these updates. In other words, transactions can make scripts faster and potentially more robust (although transactions need to be used correctly to gain such benefits).
Unfortunately, not every database supports transactions (Mysql5 supports transactions, mysql4 I don't know), so when the connection is first opened, PDO needs to run in so-called "auto-commit" mode. Autocommit mode means that if the database supports transactions, then every query you run has its own implicit transaction, and if the database does not support transactions, every query does not have such a transaction. If you require a transaction, you must use the PDO::beginTransaction() method to start a transaction. If the underlying driver does not support transactions, a PDOException will be thrown (regardless of the error handling settings: this is always a fatal error condition). Within a transaction, you can use PDO::commit() or PDO::rollBack() to end the transaction, depending on whether the code running in the transaction was successful.
When the script ends, or when a connection is about to be closed, if there is an outstanding transaction, PDO will automatically roll back the transaction. This is a safety measure to help avoid inconsistencies if the script ends abnormally - if the transaction is not committed explicitly, then it is assumed that there will be an inconsistency somewhere, so a rollback will be performed to preserve the data security.
//Example from http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0505furlong/index.html
try{
$dbh=new PDO('odbc:SAMPLE','db2inst1','ibmdb2',
array(PDO_ATTR_PERSISTENT=>true));
echo"Connectedn";
$dbh->setAttribute(PDO_ATTR_ERRMODE,PDO_ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
$dbh->exec("insert into salarychange (id, amount, changedate)
values (23, 50000, NOW())");
$dbh->commit();
}catch(Exception $e){
$dbh->rollBack();
echo"Failed: ".$e->getMessage();
}
In the above example, suppose we create a set of entries for a new employee with an ID number, which is 23. In addition to entering the person's basic data, we also need to record the employee's salary. It's simple to do both updates separately, but by including both updates in the beginTransaction() and commit() calls, you ensure that no one else can see the changes until they are complete. If an error occurs, the catch block can roll back all changes that have occurred since the start of the transaction and print out an error message.
Updates do not have to be made within a transaction. You can also issue complex queries to extract data and build further updates and queries using that information. When a transaction is active, it is guaranteed that others cannot make changes while the work is in progress. Actually, this isn't 100% correct, but it's a good introduction if you haven't heard of transactions before.
★Prepared statements and stored procedures Many more mature databases support the concept of prepared statements. What are prepared statements? You can think of prepared statements as a compiled template of the SQL you want to run, which can be customized using variable parameters. Prepared statements provide two major benefits:
the query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When a query is ready, the database analyzes, compiles, and optimizes the plan for executing the query. This process takes longer for complex queries and can significantly slow down your application if you need to repeat the same query multiple times with different parameters. By using prepared statements, you can avoid repeated analysis/compile/optimization cycles. Simply put, prepared statements use fewer resources and therefore run faster.
Parameters supplied to prepared statements do not need to be enclosed in quotes; the driver handles these. If your application uses prepared statements exclusively, you can be sure that no SQL intrusions can occur. (However, there is still a risk if you still base other parts of the query on untrusted input).
Prepared statements are so useful that PDO actually breaks the rule set in Goal 4: If the driver does not support prepared statements, PDO will emulate prepared statements.
Example: PDO application example:
<?php
$dbms='mysql';//Database type Oracle uses ODI. For developers, using different databases, as long as you change this, you don't have to remember so many functions.
$host='localhost';//Database host name
$dbName='test';//Database used
$user='root';//Database connection user name
$pass='';//Corresponding password
$dsn="$dbms:host=$host;dbname= $dbName";
classdbextendsPDO{
publicfunction__construct(){
try{
parent::__construct("$GLOBALS[dsn]",$GLOBALS['user'],$GLOBALS['pass']);
}catch(PDOException$e){
die("Error: ".$e->__toString()."<br/>");
}
}
publicfinalfunctionquery($sql){
try{
returnparent::query($this->setString($sql));
}catch(PDOException$e){
die("Error: ".$e->__toString()."<br/>");
}
}
privatefinalfunctionsetString($sql){
echo "I want to process $sql";
return$sql;
}
}
$db=newdb();
$db->setAttribute(PDO::ATTR_CASE,PDO::CASE_UPPER);
foreach($db->query('SELECT * from xxxx_menu')as$row){
print_r($row);
}
$db->exec('DELETE FROM `xxxx_menu` where mid=43');
?>