How to use PDO in PHP_PHP tutorial-PHP Tutorial-php.cn

This article does not involve the installation of PDO, but simply summarizes the use of PDO.

Start constructing the first PDO application and create 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 = 'lix',gender='mail',time=NOW()");
echo $count;
$db = null;
?>
$dsn = "mysql:host=localhost;dbname=test";
Used to construct a DSN (data source), the information inside includes: the database type is mysql, the host address is localhost, the database name is test, that's all. 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 parameter is the user connecting to the database server, and the third parameter is the password.

$count = $db->exec("INSERT INTO foo SET name = 'lix',gender='mail',time=NOW()");
echo $count;
Call the 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. 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 parameter at the end: array(PDO::ATTR_PERSISTENT => true) which becomes like this:
$db = new PDO($dsn, 'root', '', array(PDO::ATTR_PERSISTENT => true));

If you want to get data
[php]
$rs=$db->query("SELECT * FROM foo");
foreach($rs->fetch() as $row){
Print_r($row);
}
?>
You can also use

[php]
$rs = $db->query("SELECT * FROM foo");
while($row = $rs->fetch()){
Print_r($row);
}
?>
Get all data at once

[php]
$rs = $db->query("SELECT * FROM foo");
$result_arr = $rs->fetchAll();
print_r($result_arr);
?>
The result shows:

[php]
Array
(
[0] => Array
(
[id] => 1 [0] => 1
[name] => [1] => [gender] => [2] => [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);
?>
The setAttribute() method is to set PDO attributes. The main attributes are: PDO::ATTR_CASE, PDO::ATTR_ERRMODE, etc. What we need to set here is PDO::ATTR_CASE, that is, when we use the associated index to obtain the data set, the associated index is Uppercase or lowercase, there are several options:
PDO::CASE_LOWER -- Force column names to be lowercase
PDO::CASE_NATURAL -- Column names are in their original form
PDO::CASE_UPPER -- Force column names to be uppercase

We use the setFetchMode method to set the type of return value for getting the result set. The same type is:
PDO::FETCH_ASSOC -- Associative array form
PDO::FETCH_NUM -- Numeric index array form
PDO::FETCH_BOTH -- Both are available in array form, 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. For other acquisition types, refer to the manual.

In addition to the above method of obtaining data, you can also:

[php]
$rs = $db->prepare("SELECT * FROM foo");
$rs->execute();
while($row = $rs->fetch()){
Print_r($row);
}
?>
If you want to get the result 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;
?>
Summary:

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 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 quite powerful. It cannot be explained simply in this article. You can refer to the manual 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 two other peripheral 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】
1. Object-oriented approach
Let’s first look at how to deal with connection errors, etc., using an object-oriented approach:

[php]
try {
$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$db = null;
} catch (PDOException $e) {
Print "Error: " . $e->getMessage() . "
";
die();
}
?>
Here we use the object-oriented exception handling feature of PHP 5. If there is an exception, PDOException is initialized to initialize an exception class.
Attribute structure of PDOException exception class:

[php]
class PDOException extends Exception
{
Public $errorInfo = null; // Error information can be accessed by calling PDO::errorInfo() or PDOStatement::errorInfo()
Protected $message; // Exception information, you can use Exception::getMessage() to access it
Protected $code; // SQL status error code, you can use Exception::getCode() to access
}
?>
Take a brief look at the built-in exception handling class structure of PHP 5:
[php]
class Exception
{
// Attributes
Protected $message = 'Unknown exception'; // Exception message
protected $code = 0; //User-defined exception code
protected $file; // The file name where the exception occurred
protected $line;
// Method
final function getMessage(); // Return exception message
final function getCode(); // Return exception code
final function getFile(); // Return the file name where the exception occurred
final function getLine(); // Returns the code line number where the exception occurred
final function getTrace(); // backtrace() array
final function getTraceAsString(); // getTrace() information that has been formatted into a string
}
?>
Correspondingly, getFile() and getLine() can be appropriately called in the code to locate errors and make debugging more convenient.

2. Process-oriented approach

[php]
$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:

[php]

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.

Excerpted from Heda Li Xin’s Crazy Coding Life