Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to install, configure and use PDO objects in php. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

A brief introduction to the understanding of PDO objects

The PHP data object (PDO) extension defines a lightweight and consistent interface for PHP to access the database. Each database driver that implements the PDO interface can expose the characteristics of a specific database as a standard extension. Note that extending with PDO does not implement any database functionality by itself; you must use a PDO driver for a specific database to access the database service.

PDO provides a data access abstraction layer, which means that no matter which database you use, you can use the same function (method) to query and retrieve data. PDO does not provide a database abstraction layer; it does not rewrite SQL, nor does it simulate missing features. If necessary, a mature abstraction layer should be used.

1.2 installation and configuration method

PDO and all major drivers are released with PHP as shared extensions, and to activate them, simply edit the php.ini file:

Extension=php_pdo.dll

Note: this step is not required in PHP 5.3 and later, and is no longer required as an DLL file for PDO.

To connect to the database, you also need to remove the ";" sign before the PDO-related database extension, and then restart the Apache server.

Extension=php_pdo.dllextension=php_pdo_firebird.dllextension=php_pdo_informix.dllextension=php_pdo_mssql.dllextension=php_pdo_mysql.dllextension=php_pdo_oci.dllextension=php_pdo_oci8.dllextension=php_pdo_odbc.dllextension=php_pdo_pgsql.dllextension=php_pdo_sqlite.dll1.3 PDO class 1.3.1 PDO::__construct

PDO::__construct-create a PDO instance that represents a database connection

Description

PDO::__construct (string $dsn, string $username =?, string $password =?, array $driver_options =?)

Parameters.

Dsn

The data source name, or DSN, contains information about the request to connect to the database. Typically, a DSN consists of a PDO driver name, followed by a colon, and the connection syntax of a specific PDO driver. Example:

Mysql:host=localhost;dbname=pxscj

Username

User name in the DSN string. For some PDO drivers, this parameter is optional.

Password

The password in the DSN string. For some PDO drivers, this parameter is optional.

Driver_options

An array of key = > values for a specifically driven connection option.

Return value

If successful, a PDO object is returned.

Example

1.3.2 PDO::exec

PDO::exec-executes a SQL statement and returns the number of rows affected

Description

PDO::exec (string $statement): int

Parameters.

Statement

The SQL statement to be preprocessed and executed.

Opinion: here statement should properly handle user input to prevent SQL injection

Return value

PDO::exec () returns the number of rows affected by the modified or deleted SQL statement. If there are no affected rows, PDO::exec () returns 0.

Example

1.4.2 PDOStatement::execute

PDOStatement::bindParam-binds a parameter to the specified variable name

Description

PDOStatement::execute (array $input_parameters =?): bool

Execute preprocessed statements. If the preprocessed statement contains parameter markers, you must choose one of the following practices:

1) call PDOStatement::bindParam () to bind the PHP variable to the parameter marker: if any, pass the input value and get the output value by associating the bound variable with the parameter tag

2) or pass an array that is only used as an input parameter value

Return value

Returns true on success or false on failure.

Parameters.

Input_parameters

Example

1.4.3 PDOStatement::fetch

PDOStatement::fetch-get the next row from the result set

Description

PDOStatement::fetch (int $fetch_style =?, int $cursor_orientation = PDO::FETCH_ORI_NEXT, int $cursor_offset = 0): mixed

Gets the next row from a result set related to a PDOStatement object. The fetch_style parameter determines how POD returns rows.

Return value

The value returned when this function (method) succeeds depends on the extraction type. In all cases, failure returns false.

Parameters.

Fetch_style

Controls how the next line is returned to the caller. This value must be one of the PDO::FETCH_* series constants and defaults to the value of PDO::ATTR_DEFAULT_FETCH_MODE (default is PDO::FETCH_BOTH).

PDO::FETCH_ASSOC: returns an array PDO::FETCH_BOTH with the result set column name as index (default): returns an array with the result set column name and column number starting with 0 PDO::FETCH_BOUND: returns true and assigns the column value in the result set to the PHP variable bound by the PDOStatement::bindColumn () method. PDO::FETCH_CLASS: returns a new instance of the request class, mapping the column names in the result set to the corresponding attribute names in the class. If fetch_style contains PDO::FETCH_CLASSTYPE (for example: PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE), the class name is determined by the value of the first column: update an existing instance of the requested class, map the column in the result set to the named attribute in the class PDO::FETCH_LAZY: use PDO::FETCH_BOTH and PDO::FETCH_OBJ together Create object variable name for access PDO::FETCH_NUM: returns an array with an index of the result set column number starting with 0 PDO::FETCH_OBJ: returns an anonymous object whose attribute name corresponds to the result set column name

Cursor_orientation

For a scrollable cursor represented by a PDOStatement object, this value determines which row will be returned to the caller. This value must be one of the PDO::FETCH_ORI_* series constants and defaults to PDO::FETCH_ORI_NEXT.

Offset

For a scrollable cursor represented by a PDOStatement object with the cursor_orientation parameter set to PDO::FETCH_ORI_ABS, this value specifies the absolute line number of the row you want to get in the result set.

Example

1.4.4 PDOStatement::execute

PDOStatement::fetchAll-returns an array containing all rows in the result set

Description

PDOStatement::fetchAll (int $fetch_style =?, mixed $fetch_argument =?, array $ctor_args = array ()): array

Return value

PDOStatement::fetchAll () returns an array of all remaining rows in the result set. Each row of this array is either an array of column values or an object with properties corresponding to each column name.

Note: using this method to get a large result set will increase the burden on the system and may consume a lot of network resources. Instead of retrieving all the data and manipulating it with PHP, consider using database services to process the result set. For example, use the WHERE and ORDER BY clauses in SQL to qualify the result before fetching the data and processing it through PHP.

Parameters.

Fetch_style

Controls the contents of the returned array as PDOStatement::fetch (). The default value is PDO::ATTR_DEFAULT_FETCH_MODE (the default is PDO::FETCH_BOTH) to return an array containing all the values in a single column in the result set, you need to specify PDO::FETCH_COLUMN. Get the desired column by specifying the column-index parameter.

Fetch_argument

Depending on the value of the fetch_style parameter, this parameter has different meanings:

PDO::FETCH_COLUMN: returns the column that specifies the index starting with 0.

PDO::FETCH_CLASS: returns an instance of the specified class, mapping the columns of each row to the corresponding attribute names in the class.

PDO::FETCH_FUNC: passes the column of each row as an argument to the specified function and returns the result after the function is called.

Ctor_args

Customize the parameters of the constructor of the class when the fetch_style parameter is PDO::FETCH_CLASS.

Example

1.4.5 PDOStatement::rowCount

PDOStatement::rowCount-returns the number of rows affected by the previous SQL statement

Description

PDOStatement::rowCount (): int

PDOStatement::rowCount () returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the relevant PDOStatement was a SELECT statement, some data may return the number of rows returned by this statement. However, this approach is not guaranteed to be valid for all data, and should not be dependent on this approach for portable applications.

Return value

Returns the number of rows.

Example

1.4.6 Internal function description PDOStatement::bindColumn-bind a column to a PHP variable PDOStatement::bindParam-bind a parameter to the specified variable name PDOStatement::bindValue-bind a value to a parameter PDOStatement::closeCursor-close the cursor so that the statement can be executed again. PDOStatement::columnCount-return the number of columns in the result set PDOStatement::debugDumpParams-print a SQL preprocessing command PDOStatement::errorCode-get the SQLSTATEPDOStatement::errorInfo related to the last statement handle operation-get the extended error information related to the last statement handle operation PDOStatement::execute-execute a preprocessing statement PDOStatement::fetch-get the next row of PDOStatement::fetchAll from the result set-return a Array PDOStatement::fetchColumn containing all rows in the result set-returns a separate column from the next row in the result set. PDOStatement::fetchObject-gets the next row and returns it as an object. PDOStatement::getAttribute-retrieve a statement attribute PDOStatement::getColumnMeta-return a column of metadata PDOStatement::nextRowset in the result set-advance to the next rowset PDOStatement::rowCount in one multirowset statement handle-return the number of rows affected by the previous SQL statement PDOStatement::setAttribute-set a statement attribute PDOStatement::setFetchMode-set the default fetch mode for the statement. 1.5 usage and summary

The above description of the PDO class and the PDOStatement class is extracted from the php development manual, and the detailed description only excerpts a few commonly used internal functions. To be honest, one of the best ways for programmers to improve their skills is to look at the documentation, which can solve many of the problems we encounter in development, and through the documentation we can know a lot of details and points for attention in the use of api to help us avoid a lot of mistakes. All in all, if you encounter problems, read the documents, read more Baidu, read more blogs, and sum up more, this is the way to learn for a long time.

Practice is the only way to improve your skills. Only by practice can you find problems and improve yourself. The following code is my collation of some common uses of PDO:

Try {$db=new PDO ("mysql:host=localhost;dbname=pxscj", "user", "123456");} catch (PDOException $e) {echo "database connection failed:". $e-> getMessage ();} $db- > exec ("set names utf-8"); / / insert $query= "insert into kcb values ('606 minutes training PHP programming', 6, 486)" If ($affCount=$db- > exec ($query)) {echo "inserted successfully, the number of affected entries is:". $affCount. "

";} / / query $query=" select * from kcb "; foreach ($db- > query ($query) as $row) {echo" course number: ". $row [0].

; echo course name:. $row [1].

; echo "course commencement date:". $row [2].

"; echo:". $row [3]. "

";} / / transaction try {$db- > exec (" set names utf-8 "); $db- > beginTransaction (); $affrows=$db- > exec (" insert into kcb values "); if (! $affrows) throw new PDOException (" insert failure 1 ") $affrows=$db- > exec ("insert into kcb values (! $affrows) throw new PDOException (" insert failed 2 "); echo" insert success! "; $db- > commit ();} catch (PDOException $e) {echo $e-> getMessage () $db- > rollBack (); / / rollback (success or failure)} / / prepare prevents sql from injecting $in_sql= "insert into userinfo (username,password,sex,age,email) values"; $in_result=$db- > prepare ($in_sql); $userid= "php3"; $pwd1= "111111"; $sex=0; $age=36; $email= "php3@qq.com"; $in_result- > bindParam (1, $userid); $in_result- > bindParam (2, $pwd1) $in_result- > bindParam (3, $sex); $in_result- > bindParam (4, $age); $in_result- > bindParam (5, $email); $in_result- > execute (); if ($in_result- > rowCount () = 0) echo "insert record failed!"; else echo "insert record successful!"; / / update password $oldpwd=$_POST ['oldpwd'] / / the original password $newpwd=$_POST ['newpwd']; / / the new password $sroomsql = "select * from userinfo where username='$username'"; / / SQL statement $saccountresultfolddbb-> query ($s_sql) List ($username,$password,$sex,$age,$email) = $sencrypted result-> fetch (PDO::FETCH_NUM); if ($passwordpassword invalid password) / / determine whether the original password is correct echo "original password is wrong!"; else {$checkpwd=preg_match ('/ ^\ w {6jue 20} $/', $newpwd) If (! $checkpwd) echo "New password format does not meet requirements!"; else {$update_sql= "update userinfo set password='$newpwd' where username='$username'"; $affected=$db- > exec ($update_sql); if ($affected) echo "password modified successfully!"; else echo "password modification failed!";}} / delete logout session_start (); $username=@$_SESSION ['userid'] $delete_sql= "delete from userinfo where username='$username'"; / / log out of your own SQL statement $affected=$db- > exec ($delete_sql); if ($affected) echo "logout user success!"; else echo "logout user failed!"; 1.6PDO chat with JPA

Software development frameworks always have a lot of interoperability, because no matter any development language, the underlying implementation of java or php is inseparable from data structures, algorithms, and software design patterns, which are interoperable, and good software frameworks are inseparable from the use of design patterns.

Since learning php contact and learning about the PDO object extension, I have thought of the springboot JPA persistence layer framework I used, so I looked up some information, and here is my personal understanding:

First, explain the concepts of PDO and JPA.

PDO

The PHP data object (PHP Data Objects) extension defines a lightweight and consistent interface for PHP to access the database.

PDO provides a data access abstraction layer, which means that no matter which database you use, you can use the same function (method) to query and retrieve data. PDO does not provide a database abstraction layer; it does not rewrite SQL, nor does it simulate missing features. If necessary, a mature abstraction layer should be used.

PDO Architecture concept Map

JPA

JPA is the abbreviation of Java Persistence API, the Chinese name Java persistence layer API, is JDK 5.0annotations or XML describes the object-relational table mapping, and the runtime entity objects are persisted to the database.

JPA is just a specification, that is, JPA defines only some interfaces, and interfaces need to be implemented to work. So the underlying layer needs some kind of implementation, and Hibernate is the ORM framework that implements the JPA interface.

JPA concept diagram

From the above concepts and architecture diagrams, we can see that there are some differences between PDO and JPA. First of all, the PDO of PHP is only an abstract data access interface layer, its access to the database depends on the corresponding database driver, and it needs to write its own SQL statements to operate the data.

The JPA of java is a persistence layer specification, that is to say, JPA only defines some interfaces, which are about the mapping of classes and database tables, that is to say, the specification of JPA is above the data access interface layer, and the real implementation of this specification and the underlying data access interface is within these data persistence layer frameworks such as Hibernate.

Taking the persistence layer framework of Hibernate as an example, it realizes the mapping relationship between objects and database tables, and only needs to operate the corresponding interface to access the DAO layer to query the database and transform it into the internal attribute data of java objects. On the other hand, the underlying access to the database of Hibernate depends on the JDBC interface.

Thus it can be seen that PDO and JPA specifications are completely at two different levels. JPA is the high-level abstraction at the bottom of the data access method, while PDO is only equivalent to the JDBC interface layer of java. PDO has a relatively low level of abstraction, which is consistent with PHP's lightweight web development language.

It can easily and clearly realize some complex database access operations, but it is not conducive to the development of complex and high-abstract large-scale projects.

The above is some personal understanding and comparison of PHP PDO objects and java JPA specifications, the level is limited, there may be inaccuracies.

1.7 Lab questions and Summary 1.7.1 mysql connection

Php uses mysql native passwords to connect, such as

$conn=mysqli_connect ('localhost','user','123456') or die (' connection failed')

An error will occur: The server requested authentication method unknown to the client.

Reason:

Since the local mysql version is above 8.0, and mysql 8 upgrades the password authentication method caching_sha2_password, the native connection will fail. The solution is to use sql to modify the user login authentication method:

Use mysql;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY' your password; 1.7.2 sql injection summary

When php uses the query method of mysqli or pdo to query, there may be hidden dangers of SQL injection if the input data of the user form is not processed, such as

$username=$_POST ['username']; $password=$_POST [' password']; / / if magic_quotes_gpc is set to off / / mysql$conn = mysqli_connect ("localhost", "user", "123456") or die ("connection failed"); / / mysql_connect () link to MySQL server mysqli_select_db ($conn,'PXSCJ') or die ("failed to select database") / / mysql_select_db () Select the database mysqli_query ($conn, "SET NAMES utf-8"); / / set the character set to utf-8$ str= "select * from userinfo where username='$username' and password='$password'"; $result=mysqli_query ($conn,$str); / / PDO// connect to the database, and create a new PDO object $pdo=new PDO ("mysql:host=localhost;dbname=pxscj", "user", "123456"); $str= "select * from userinfo where username='$username' and password='$password'"; $result=$pdo- > query ($str)

When the user enters a username value of user and a password value of 123'or 1, the query sql is escaped as:

Select * from userinfo where username='user' and password='123'or '1mm 1'

Then the user will skip the query of the password to validate all userinfo data, and there is a risk that other SQL statements will be executed ultra vires.

There are several ways to deal with risk:

Addslashes () function escape

$password=addslashes ($password)

The addslashes () function returns a string that adds a backslash before a predefined character. The predefined characters are:

Single quotation marks (')

Double quotes (")

Backslash (\)

NULL

Use the prepare () method of the PDO object

$sql= "select * from userinfo where username='$username' and password='$password'"; / / Note is not a question mark in Chinese? $result=$pdo- > prepare ($sql); / / follow? The order binding parameter values of $result- > bindParam (1 recording username); $result- > bindParam (2); $result- > execute (); on "how to install, configure and use PDO objects in php" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.