PDO (PHP Data Object) is a new thing in PHP 5. When PHP 6 is about to be released, PHP 6 only uses PDO to process the database by default, and will move all database extensions to PECL, so the default is no more. php_mysql.dll and the like, what should I do? We can only keep pace with the times, so I gave PDO a try. (This article is just entry-level, experts can skip it, haha)
[What is PDO]
PDO is a major new feature added to PHP 5, because before PHP 5, php4/php3 had a bunch of database extensions to communicate with each database. Connection and processing, php_mysql.dll, php_pgsql.dll, php_mssql.dll, php_sqlite.dll and other extensions to connect MySQL, PostgreSQL, MS SQL Server, SQLite, similarly, we must use ADOdb, PEAR::DB, PHPlib ::Database abstract classes such as DB are extremely cumbersome and inefficient to help us. After all, how can the efficiency of PHP code be so high that we can directly write it in C/C++? Therefore, the emergence of PDO is inevitable. Everyone should accept its use with a calm learning attitude. Maybe you will find that it can save you a lot of effort.
[Installing PDO]
I am running Windows XP SP2, so the whole process is carried out on Windows. As for Linux/FreeBSD and other platforms, please find the information and set up the installation by yourself.
Mine is PHP 5.1.4, which already comes with the php_pdo.dll extension, but it requires a little setup before it can be used.
Open c:windowsphp.ini, which is my PHP configuration file, and find the following line:
extension_dir
This is the directory where our extension exists. My PHP 5 extension is in: C:php5ext, then I will Change this line to:
extension_dir = "C:/php5/ext"
and then find it under php.ini:
;;;;;;;;;;;;;;;;;;;;;;;;
; Dynamic Extensions ;
;;;;;;;;;;;;;;;;;;;;;;;
There are a bunch of things similar to ;extension=php_mbstring.dll below. Here is the configuration for PHP extension loading. We will add it at the end. Our PDO extension:
extension=php_pdo.dll
extension=php_pdo_mysql.dll
extension=php_pdo_pgsql.dll
extension=php_pdo_sqlite.dll
extension=php_pdo_mssql.dll
extension=php_pdo_odbc.dll
extension=php_pdo_firebird.dll
;extension=php_pdo_oci8.dll
Various PDO drivers can be added, but the following php_pdo_oci8.dll, because I have not installed the Oralce database, does not have this, so I use a semicolon to comment it out. Then restart our web server, IIS/Apache, mine is IIS, hey, you look down on me, on Windows, it’s easy.
After restarting, write a phpinfo.php file in the document directory of our web server and add these:
<?
phpinfo();
?＞
Then open our lovely browser: IE/FireFox, mine is FireFox 2.0, I just downloaded it, it’s great, I’m not afraid of rogue software, haha.
Enter in the browser: http://localhost/phpinfo.php . If your path to this page is inconsistent, please enter it yourself.
In the output content, if you can see it successfully:
PDO
PDO support enabled
PDO drivers mysql, pgsql, sqlite, mssql, odbc, firebird.
There are various driver instructions at the back: PDO_Firebird, pdo_mssql, pdo_mysql, PDO_ODBC, pdo_pgsql, pdo_sqlite.
Then, congratulations on your successful installation, otherwise please check the above steps carefully.
[Quick Test]
I am using MySQL 4.0.26, but I personally recommend everyone to use MySQL 4.1.x or MySQL 5.0.x, because those versions have a lot of interesting things worth learning. What our PDO needs to connect to is my MySQL 4.0. If you have not installed MySQL, please install it yourself. We have established MySQL and added table foo to the test library, including four fields such as id, name, gender, and time.
We started to construct the first PDO application and created a pdo.php file in the Web document directory:
<?php
$dsn = "mysql:host=localhost;dbname=test";
$db = new PDO($dsn, 'root', '');
$count = $db->exec("INSERT INTO foo SET name = 'heiyeluren',gender='male',time=NOW()");
echo $count;
$db = null;
?＞
I don’t understand what it means, let’s explain it slowly. This line:
$dsn = "mysql:host=localhost;dbname=test";
is to construct our DSN (data source). Look at the information inside: the database type is mysql, the host address is localhost, and the database name is test. Just a few pieces of information. The data source construction methods of different databases are different.
$db = new PDO($dsn, 'root', '');
Initialize a PDO object. The first parameter of the constructor is our data source, the second is the user connecting to the database server, and the third parameter is password. We cannot guarantee that the connection is successful. We will talk about exceptions later. Here we assume that the connection is successful.
$count = $db->exec("INSERT INTO foo SET name = 'heiyeluren',gender='male',time=NOW()");
echo $count;
calls our successfully connected PDO object to execute a query. This query is an operation to insert a record. Using the PDO::exec() method will return a result that affects the record, so we output this result. Finally, you still need to end the object resource:
$db = null;
By default, this is not a long connection. If you need a long connection to the database, you need to add a last parameter: array(PDO::ATTR_PERSISTENT => true). It becomes like this:
$db = new PDO( $dsn, 'root', '', array(PDO::ATTR_PERSISTENT => true));
It's such a simple operation. It may not be much different from the previous one, but it is somewhat similar to ADOdb.
[Continue to learn]
If we want to extract data, we should use the data acquisition function. ($db used below are all objects connected above)
<?php
foreach($db->query("SELECT * FROM foo")){
print_r($row);
}
?＞
We can also use this acquisition method:
＜?php
$rs = $db->query("SELECT * FROM foo");
while($row = $rs->fetch()){
print_r($row);
}
?＞
If you want to get all the data into the array at once, you can do this:
＜?php
$rs = $db->query("SELECT * FROM foo");
$result_arr = $rs->fetchAll();
print_r($result_arr);
?＞
Array
([0] => Array(
[id] => 1
[0] => 1
[name] => heiyeluren
[1] =＞heiyeluren
[gender] =＞Male[2] =＞Male[time] =＞2006-10-28 23:14:23
[3] => 2006-10-28 23:14:23
)
}
Let’s look at the records inside. There are both digital indexes and associated indexes, which is a waste of resources. We only need the associated index:
<?php
$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);
$rs = $db->query("SELECT * FROM foo");
$rs->setFetchMode(PDO::FETCH_ASSOC);
$result_arr = $rs->fetchAll();
print_r($result_arr);
?＞
Look at the above code, the setAttribute() method is to set some attributes. The main attributes are: PDO::ATTR_CASE, PDO::ATTR_ERRMODE, etc. What we need to set here is PDO::ATTR_CASE, that is, we use the associated index to obtain data When setting, there are several options for whether the associated index is uppercase or lowercase:
PDO::CASE_LOWER -- Force the column name to be lowercase PDO::CASE_NATURAL -- The column name will be in the original way PDO::CASE_UPPER -- Force the column name to be uppercase
We use the setFetchMode method to set the type of return value for getting the result set. The same types are:
PDO::FETCH_ASSOC -- associative array form PDO::FETCH_NUM -- numeric index array form PDO::FETCH_BOTH -- both array forms. Yes, this is the default PDO::FETCH_OBJ - in the form of an object, similar to the previous mysql_fetch_object().
Of course, generally we use PDO::FETCH_ASSOC. What to use specifically depends on your own needs. Other acquisitions Type reference manual.
In addition to the above method of obtaining data, there is also this method:
<?php
$rs = $db->prepare("SELECT * FROM foo");
$rs->execute();
while($row = $rs->fetch()){
print_r($row);
}
?＞
Actually, it’s almost the same. If you want to get the results of a field in a specified record, you can use PDOStatement::fetchColumn():
<?php
$rs = $db->query("SELECT COUNT(*) FROM foo");
$col = $rs->fetchColumn();
echo $col;
?>
Generally, fetchColumn() is used to perform count statistics or some records that only require a single field are easy to operate.
To briefly summarize the above operations:
query operations are mainly PDO::query(), PDO::exec(), and PDO::prepare(). PDO::query() is mainly used for operations that return recorded results, especially SELECT operations. PDO::exec() is mainly used for operations that do not return a result set, such as INSERT, UPDATE, DELETE and other operations. It returns The result 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 relatively 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, and PDOStatement::fetchAll() is used to obtain all 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.
[Error Handling]
What should you do if you encounter an error in the program? We describe here the error information and exception handling of the PDO class.
1. Object-oriented approach.
Let’s first look at how to deal with connection errors, etc., and use the object-oriented approach to handle it:
＜?php
try {
$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$db = null;
} catch (PDOException $e) {
print "Error: " . $e-＞getMessage() . "＜br/＞";
die();
}
?＞
Here we use the object-oriented exception handling feature of PHP 5. If there is an exception, we will initialize it by calling PDOException to initialize an exception class.
Attribute structure of PDOException exception class:
<?php
class PDOException extends Exception
{
public $errorInfo = null; // For error information, you can call PDO::errorInfo() or PDOStatement::errorInfo() to access protected $message; // For exception information, you can try Exception::getMessage() to access protected $code ; // SQL status error code, which can be accessed using Exception::getCode()
}
?＞
This exception handling class is integrated with the built-in exception handling class of PHP 5. Let’s take a brief look at the structure of the built-in exception handling class of PHP 5:
＜?php
classException
{
//Property protected $message = 'Unknown exception'; //Exception message protected $code = 0; //User-defined exception code protected $file; //The file name where the exception occurred protected $line; //The code where the exception occurred Line number
// Method final function getMessage(); // Return exception information final function getCode(); // Return exception code final function getFile(); // Return the file name where the exception occurred final function getLine(); // Return The code line number where the exception occurred final function getTrace(); // backtrace() array final function getTraceAsString(); // getTrace() information formatted into a string
}
?＞
Correspondingly, getFile() and getLine() can be appropriately called in the code to locate errors and make debugging more convenient.
2. Use the process-oriented approach
to look at the code first:
＜?
$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$rs = $db->query("SELECT aa,bb,cc FROM foo");
if ($db->errorCode() != '00000'){
print_r($db->errorInfo());
exit;
}
$arr = $rs->fetchAll();
print_r($arr);
$db = null;
?＞
PDO and PDOStatement objects have errorCode() and errorInfo() methods. If there are no errors, errorCode() returns: 00000, otherwise some error codes will be returned. errorInfo() returns an array, including error codes defined by PHP and MySQL error codes and error information. The array structure is as follows:
Array
(
[0] => 42S22
[1] => 1054
[2] => Unknown column 'aaa' in 'field list'
)
After each query is executed, the result of errorCode() is the latest, so we can easily control the error message display ourselves.
[Simple summary]
From the above usage, we can see that PDO is indeed powerful. There are also some other things that I have not mentioned, such as binding parameters, preprocessing, stored procedures, transaction processing and other functions. In addition, there are different data expansion DSN structures. Oracle database itself has many special things that require in-depth study and understanding. This article only briefly describes some introductory knowledge, which can be regarded as a simple understanding of PDO.