PHP understands the PDO database abstraction layer, phppdo database abstraction_PHP tutorial

PHP understands the PDO database abstraction layer, phppdo database abstraction

The full name of PDO is PHP Data Object (PHP Data Object), which is an extension of PHP connection database. Currently Be commonly used. The main problem solved by PDO is to provide a unified data access interface and operation layer for different databases. It provides a better solution for the development and migration of the system across database platforms. Acquisition of PDO objects
In PDO, to establish a connection with the database, you need to instantiate the constructor of PDO. The PDO constructor syntax is as follows:
PDO::__construct ( string $dsn [, string $username [, string $password [, array $driver_options ]]] )
Connect to the database through the PDO constructor:

<span> 1</span> <?<span>php
</span><span> 2</span> <span>$dbms</span> = 'mysql'<span>;
</span><span> 3</span> <span>$dbName</span> = 'dormitory'<span>;
</span><span> 4</span> <span>$host</span> = 'localhost'<span>;
</span><span> 5</span> <span>$user</span> = 'root'<span>;
</span><span> 6</span> <span>$pwd</span> = ''<span>;
</span><span> 7</span> <span>$dsn</span> = "<span>$dbms</span>:host=<span>$host</span>;dbname=<span>$dbName</span>"<span>;
</span><span> 8</span> <span>try</span><span> {
</span><span> 9</span>     <span>$pdo</span> = <span>new</span> PDO(<span>$dsn</span>, <span>$user</span>, <span>$pwd</span><span>);
</span><span>10</span>     <span>echo</span> '连接成功！'<span>;
</span><span>11</span> } <span>catch</span> (<span>Exception</span> <span>$e</span><span>) {
</span><span>12</span>     <span>echo</span> <span>$e</span>->getMessage() . "<br>"<span>;
</span><span>13</span> <span>}
</span><span>14</span> ?>

3 ways to execute sql statements
The methods to execute sql statements are: exec(), query(), prepare()+execute().
1. The exec() method is used to execute input, delete, and update statements, and the return value is the number of affected rows;
2. The query() method is used When executing a select statement, the return value is a two-digit array;
3. The preprocessing statement prepare()+execute() can be used to execute input, delete, update, and select statements,
You can think of prepared statements as a compiled template of the SQL you want to run. It can be customized using variable parameters, so that it only needs to be parsed once when querying. Can be executed multiple times;
If the application only uses prepared statements, it can ensure that SQL injection will not occur.
PDO::prepare() returns a PDOStatement object, and the content of the object is the parameters in the prepare() method;
PDO::execute(), Check whether sql is executable, and the return value is a boolean type.
It can also be seen from the return value of the prepared statement that he does not actually execute the sql statement, but checks the correctness of the sql, prepares the execution status, and waits until the result set is needed and then execute it.

<span> 1</span> <?<span>php
</span><span> 2</span>             <span>include_once</span> './pdo_db_conn.php'<span>;
</span><span> 3</span>             <span>try</span><span> {
</span><span> 4</span>                 <span>$query</span> = "select * from building"<span>;
</span><span> 5</span> <span>//</span><span>                $result = $pdo->query($query);//结果为一个二维数组</span>
<span> 6</span>                 <span>$result</span> = <span>$pdo</span>->prepare(<span>$query</span><span>);
</span><span> 7</span>                 <span>var_dump</span>(<span>$result</span><span>);
</span><span> 8</span>                 <span>$flag</span> = <span>$result</span>-><span>execute();
</span><span> 9</span>                 <span>var_dump</span>(<span>$flag</span><span>);
</span><span>10</span>             } <span>catch</span> (<span>Exception</span> <span>$e</span><span>) {
</span><span>11</span>                 <span>echo</span> <span>$e</span>-><span>getMessage();
</span><span>12</span> <span>            }
</span><span>13</span>             ?>

Return value:

object(PDOStatement)[2]
public 'queryString' => string 'select * from building' (length=22)

boolean true

3 ways to get the result set
The methods to get the result set are fetch(), fetchAll(), fetchColumn();
The fetch() method gets the next row in the result set, which is an array;
fetchAll() method gets all the rows in the result set, which is an array;
The fetchColumn() method gets the value of the column specified in the next row in the result set.
The calling object type of the three methods is the PDOStatement object type, which is usually executed after the prepared statement.

3 methods to capture sql statements Error method in
When an error occurs when executing a sql statement, the method of displaying the error can be determined according to the set error prompt method.
Error prompt methods are:
1. PDO::ERRMODE_SILENT
Default mode, when an error occurs, the program continues to execute without error hint.
2. PDO::ERRMODE_WARNING
Execution will continue when an error occurs, and a warning message will be prompted for the wrong part.
3. PDO::ERRMODE_EXCEPTION
Execution will not continue when an error occurs, and an exception message will be prompted for the wrong part.

2 ways to get program error information
When an error occurs when executing a sql statement (access to the database part, the result set Traversal errors will not be prompted), can output error information in the background through the errorCode() and errorInfo() methods. When the sql statement is executed through precompilation, these two methods are not applicable. When the errorCode() method is called in the program and the return value is 00000 (5 zeros), it means there is no error in the program, but when the other 5 characters are returned, it means there is an error in the program, At this time, you can view specific error information by calling the errorInfo() method.

This has a little to do with the PHP version. For example, PHP5.2 needs to open both php_pdo.dll and php_pdo_mysql.dll, while php5.4 only needs to open php_pdo_mysql.dll

$s_sql = select username,password from t_table where username=? and password=?;
$sth = $dbh->prepare($s_sql);
$result = $sth->execute (array($username,$password));
$result = $sth->fetchAll();//If it does not exist, an empty array is returned. If it exists, it is the username + password