Php PDO connects to mysql and phppdomysql

Source: Internet
Author: User

Php PDO connects to mysql and phppdomysql

Recently, a new environment has been installed in linux, php5.6 + mysql5.5 + nginx. Then, an error occurs when you use the original mysql connection to the database.

The reason is that the database connection method is too old. We recommend that you use mysqli and PDO to connect to the database.

Well, we can't lag behind. Using mysqli is actually much simpler, but PDO seems simpler. Efficiency will also be improved. According to the official documentation, it seems that some risks of SQL injection are also blocked. So my blog today is about how to use PDO to connect to mysql in php!

[What is PDO]

PDO is a major new feature of PHP 5, because php4/php3 before PHP 5 was a bunch of database extensions to connect to and process various databases, php_mysql.dll, php_pgsql.dll, php_mssql.dll, php_sqlite.dll, and other extensions to connect to MySQL, PostgreSQL, ms SQL Server, and SQLite. Similarly, we must use ADOdb, PEAR: DB, PHPlib :: database abstract classes such as DB are very cumbersome and inefficient to help us. After all, how can we directly use C/C ++ to write php code with high efficiency? Therefore, the emergence of PDO is inevitable. you should accept it with a calm learning attitude. Maybe you will find it can reduce your efforts.

The following describes the php-based version of PDO:

PDO is released in PHP 5.1. That is to say, PDO is not supported in versions earlier than 5.1, and All Versions later than 5.1 are supported. It can also be used in the PECL extension of PHP5.0.

How to Use PDO:

Here we will take the PHP Gold partner mysql as an example:

PDO_MYSQL: PDO_MYSQL is the driver that can connect to the mysql database through the PDO interface (Note: It is only used in mysql 3.x or later versions ).

Install: Open php. in the INI file, you can find the following code. Here, we can see that the mysql driver has been opened by default (there is no semicolon before for comment). If you need to connect to other databases, add drivers for other databases on your own (remove the semicolon before the corresponding item and add the driver without it ).

//PDO driver of each database
extension=php_pdo_firebird.dll //Firebird
extension=php_pdo_informix.dll //Informix
extension=php_pdo_mssql.dll    //sql server
extension=php_pdo_mysql.dll    //mysql
extension=php_pdo_oci.dll      //Oracle
extension=php_pdo_odbc.dll     //DB2
extension=php_pdo_pgsql.dll    //PostgreSQL
extension=php_pdo_sqlite.dll   //SQLite

Connection: create a connection by creating an instance of the PDO base class.

// Connect to the database 

$ db = new PDO ('mysql: host = localhost; dbname = test', $ user, $ pass );

Simple query method:
try {  
    $db = new PDO('mysql:host=;dbname=test', 'root', '');  
	$rows = $db->query('SELECT * from members')->fetchAll(PDO::FETCH_ASSOC);
	$rs = array();
    foreach($rows as $row) {  
        $rs[] = $row; 
    $db = null;  
} catch (PDOException $e) {  
    print "Error!: " . $e->getMessage() . "<br/>";  

Don't understand what it means, let's talk about it slowly. This line:
$ Dsn = "mysql: host =; dbname = test ";
It is to construct our DSN (Data Source). Let's see the following information: the database type is mysql, the host address is localhost, and the database name is test. The data source construction methods for 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 who connects to the database server, and the third parameter is the password. We cannot guarantee that the connection is successful. We will talk about exceptions later. Here we will consider it successful.

$ Count = $ db-> exec ("insert into foo SET name = 'heiyeluren', gender = 'male', 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, a result that affects the record is returned, so we output this result. Finally, you need to end the object Resource:
$ Db = null;

By default, this is not a persistent connection. If you need a persistent connection to the database, you must add the following parameter: array (PDO: ATTR_PERSISTENT => true:
$ Db = new PDO ($ dsn, 'root', '', array (PDO: ATTR_PERSISTENT => true ));

One operation is so simple. It may not be much different from the previous one. It is somewhat similar to ADOdb.

You can use the setFetchMode method to set the type of the return value for the result set. The same types are as follows:

PDO: FETCH_ASSOC -- join array form
PDO: FETCH_NUM -- numeric index array format
PDO: FETCH_BOTH -- both arrays are available, which is the default

PDO: FETCH_OBJ -- according to the object form, similar to the previous mysql_fetch_object ()

$ Db-> query ($ SQL); when $ SQL contains variables, you can use $ dbh-> quote ($ params); // escape string data

Php pdo statement

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 - returns the number of columns in the result set
Pdostatement:: debugdumparams - print a SQL preprocessing command
Pdostatement:: errorcode - get sqlstate related to the last statement handle operation
Pdostatement:: errorinfo - get extended error information related to the last statement handle operation
Pdostatement:: execute - executes a preprocessing statement
Pdostatement:: fetch - get next line from result set
Pdostatement:: fetchall - returns an array containing all rows in the result set
Pdostatement:: fetchcolumn - returns a separate column from the next row in the result set.
Pdostatement:: fetchobject - gets the next line and returns it as an object.
Pdostatement:: getattribute - retrieve a statement attribute
Pdostatement:: getcolumnmeta - returns the metadata of a column in the result set
Pdostatement:: nextrowset - advances to the next rowset in a handle to a multiline SET statement
Pdostatement:: rowcount - returns the number of rows affected by the previous SQL statement
Pdostatement:: setAttribute - set a statement attribute
Pdostatement:: setfetchmode - sets the default fetch mode for the statement.

Insert, update, and delete data,

$db->exec("DELETE FROM `xxxx_menu` where mid=43");

About transactions in PDO

PDO-> beginTransaction (), PDO-> commit (), PDO-> rollBack () are used together when the rollBack function is supported. The PDO-> beginTransaction () method indicates the start point, the PDO-> commit () method indicates the end point of the rollBack, and executes the SQL statement. The PDO-> rollBack () method performs the rollBack.

try {
$dbh = new PDO('mysql:host=localhost;dbname=test', ‘root', ”);
$dbh->query('set names utf8;');

$dbh->exec(”INSERT INTO `test`.`table` (`name` ,`age`)VALUES ('mick', 22);”);
$dbh->exec(”INSERT INTO `test`.`table` (`name` ,`age`)VALUES ('lily', 29);”);
$dbh->exec(”INSERT INTO `test`.`table` (`name` ,`age`)VALUES ('susan', 21);”);

} catch (Exception $e) {
echo “Failed: ” . $e->getMessage();

Now you have established a connection through PDO. before deploying a query, you must understand how PDO manages transactions. If you have never encountered transaction processing before (for a brief introduction), they provide four main features: Atomicity, Consistency, independence, and durability (Atomicity, Consistency, isolation and Durability, ACID) in general, when all the work in a transaction is committed, even if it is executed in stages, it must be securely applied to the database, it is not affected by other connections. The transaction can also be easily canceled automatically when an error occurs in the request.

A typical use of transactions is to "save" batch changes and execute them immediately. In this way, the update efficiency will be improved thoroughly. In other words, transactions can make your scripts faster and more robust (you still need to use them correctly to achieve this advantage ).

Unfortunately, not every database supports transactions, so PDO needs to run in the "auto commit" mode when establishing a connection. The automatic commit Mode means that each query you execute has its own implicit transaction processing, no matter whether the database supports transactions or because the database does not support transactions. If you need a transaction, you must use the PDO-> beginTransaction () method to create one. If the underlying driver does not support transaction processing, a PDOException will be thrown (it is irrelevant to your Exception Processing settings because it is always a serious error state ). In a transaction, you can end it with PDO-> commit () or PDO-> rollBack (), depending on whether the code in the transaction runs successfully.

When the script ends or a connection is closed, if you still have an unfinished transaction, PDO will automatically roll it back. This is a safe solution for unexpected termination of the script-if you do not explicitly commit the transaction, it will assume that some errors have occurred for your data security, therefore, rollback is performed.


PDO provides three different error handling policies.
This is the default mode. PDO sets simple error codes on statement and database objects. You can use the PDO-> errorCode () and PDO-> errorInfo () methods to check errors; if the error is caused by calling the statement object, you can use the PDOStatement-> errorCode () or PDOStatement-> errorInfo () method on the object to get the error information. If the error is caused by calling the database object, you should call the two methods on the database object.
As an additional error code, PDO sends out a traditional E_WARNING message. This setting is useful in debugging and debugging. If you just want to see what went wrong and don't want to interrupt the program process.
As an attachment for setting the error code, PDO throws a PDOException and sets its attributes to reflect the error code and error information. This setting is also useful in addition to errors, because it will effectively point to an error point in the "enlarge (blow up)" script, very quickly pointing to a possible error area in your code. (Remember: if an exception causes a script interruption, the transaction will be automatically rolled back .)
Exception mode is also very useful, because you can use a structure that is clearer than the traditional PHP-style error handling structure, it uses less code and nesting than quiet mode, and it can also check the return values of each database access more clearly.
For more information about Exceptions in PHP, see the Exceptions section.
PDO uses an error code string based on the SQL-92 SQLSTATE; a specific PDO driver should map its own code to the appropriate SQLSTATE code. The PDO-> errorCode () method returns only a single SQLSTATE code. If you need more targeted information about an error, PDO also provides a PDO-> errorInfo () method, which can return a code containing the SQLSTATE, the error code of the specific database driver and the error description string of the specific database driver.

<? PHP
//Modify the default error display level
> >

Common PDO methods:

PDO ::Query ()It is mainly used for operations with record results returned, especially select operations.
PDO ::Exec ()It is mainly for operations that do not return result sets. Such as insert and update. Returns the number of affected rows.
PDO ::LastInsertId ()Returns the last ID of the last insert operation, but note that if you use insert into tb (col1, col2) values (v1, v2), (v11, v22 ).. lastinsertid () returns only the ID of the first (v1, v2) inserted, rather than the ID of the last inserted record.
PDOStatement: fetch () is used to obtain a record. Traverse with while.
PDOStatement: fetchAll () is to obtain all record sets to one.
PDOStatement: fetchcolumn ([intcolumn_indexnum]) is used to directly access a column. The column_indexnum parameter is the value of the column starting from 0 in the row. However,This method can only retrieve one column of the same row at a time. If it is executed once, it will jump to the next row.Therefore, it is easier to directly access a column, but it cannot be used to traverse multiple columns.
PDOStatement ::Rowcount ()This method is used to obtain the number of records when the query ("select...") method is used. It can also be used in preprocessing. $ Stmt-> rowcount ();
PDOStatement ::Columncount ()This method is used to obtain the number of columns of a record when the query ("select...") method is used.

1. Select fetch or fetchall?

When using a small record set, fetchall is highly efficient, reducing the number of retrieval times from the database. However, for a large result set, using fetchall brings a great burden to the system. The database needs to transmit too much data to the WEB Front-end, but the efficiency is low.
2. There are several parameters for fetch () or fetchall:
Mixed pdostatement: fetch ([int fetch_style [, int cursor_orientation [, int cursor_offset])
Array pdostatement: fetchAll (int fetch_style)

More PDO methods:

PDO:: begintransaction - start a transaction
PDO:: commit - commit a transaction
PDO:: uustruct - create a PDO instance that represents a database connection
PDO:: errorcode - get sqlstate related to the last operation of the database handle
PDO::errorInfo — Fetch extended error information associated with the last operation on the database handle
PDO:: exec - executes an SQL statement and returns the number of rows affected
PDO:: getattribute - retrieve the attribute of a database connection
PDO:: getavailabledrivers - returns an array of available drivers
PDO:: intransaction - check whether it is within a transaction
PDO:: lastinsertid - returns the ID or sequence value of the last inserted row
PDO::prepare — Prepares a statement for execution and returns a statement object
PDO::query — Executes an SQL statement, returning a result set as a PDOStatement object
PDO::quote — Quotes a string for use in a query.
PDO:: rollback - rollback a transaction
PDO:: setAttribute - set attribute
Exception:: GetMessage - get the content of the exception message.
Exception:: getprevious - returns the previous exception in the exception chain
Exception:: getcode - get exception code
Exception:: GetFile - get the name of the program file where the exception occurred
Exception:: getline - get the line number of the code with exception in the file
Exception:: gettrace - get exception trace information
Exception:: gettraceassstring - get exception tracking information of string type
Exception:: toString - Convert exception object to string
Exception:: clone - exception clone

Attribute list:
Represents a boolean type
Represents a null type in SQL
Represents an integer type in SQL
Represents the SQL char, varchar type in a SQL
Represents a large object type in SQL
Represents the recordset type in SQL, which is not supported yet
Specifies that the parameter is an INOUT parameter for a stored procedure. You must bitwise-OR this value with an explicit PDO::PARAM_* data type.
Return the result of each row as an object
Only the result set of the query with the key value as the subscript is returned, and only one data with the same name is returned
Only the result set of the query with the key value as the subscript is returned, and the data with the same name is returned in the form of array
Returns only the result set of a number based query
Returns the result set of a query with key and number as Subscripts
Returns the result set as an object
Value bound by pdostatement:: bindparam() and pdostatement:: bindcolumn() is used as variable name assignment and returned
Indicates that only one column in the result set is returned
Represents the return of a result set as a class
Represents merging data into an existing class for return
Return the result set in the form of the first key value and the following number
Represents merging data into an existing class and serializing the return
Available since PHP 5.2.0
When set to true, PDO will automatically try to stop accepting delegation and start execution
Setting the data size that the application obtains in advance is not supported by all databases
Set the value of connection database timeout
Set the mode of error processing
Read only property indicating the server-side database version of PDO connection
Read only property indicating the client PDO driver version of the PDO connection
Read only attribute, which represents the meta information of the server to which PDO connects
Operate on the column form through the content in PDO:: case
Gets or sets the name of the pointer
Set the pointer type. PDO now supports PDO:: cursor? Fwonly and PDO:: cursor? Fwonly
Returns the name of the PDO driver used
Convert the returned null string to SQL null
Get an existing connection
In the returned result set, use the custom directory name instead of the field name.
In the returned result set, use the custom table name instead of the field name.
Available since PHP 5.2.0
Available since PHP 5.1.3.
Do not report any error information when an error occurs, which is the default value
When an error occurs, send a message of PHP's e'warning
Throw a pdoexception when an error occurs
Default display format of reply column
Force column name lowercase
Force column names to be capitalized
Get the next row of data in the result set, only valid if there is pointer function
Get the previous row of data in the result set, only valid if there is pointer function
Get the first row of data in the result set, only valid if there is pointer function
Get the last row of data in the result set, only valid if there is pointer function
Get a row of data in the result set, only valid when there is pointer function
Get the data of a row after the current row in the result set, only valid if there is a pointer function
Create a backward only pointer operand
Create a pointer operation object, pass the content in PDO:: fetch_ori* to control the result set
PDO::ERR_NONE (string)
Set the error message when there is no error

<? PHP
$dbh = new PDO('mysql:host=localhost;dbname=access_control', 'root', '');
$dbh->exec('set names utf8');
/ * add * /
//$sql = "INSERT INTO `user` SET `login`=:login AND `password`=:password";
$sql = "INSERT INTO `user` (`login` ,`password`)VALUES (:login, :password)";  $stmt = $dbh->prepare($sql);  $stmt->execute(array(':login'=>'kevin2',':password'=>''));
echo $dbh->lastinsertid();
/ * modification * /
$sql = "UPDATE `user` SET `password`=:password WHERE `user_id`=:userId";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':userId'=>'7', ':password'=>'4607e782c4d86fd5364d7e4508bb10d9'));
echo $stmt->rowCount();
/ * delete * /
$sql = "DELETE FROM `user` WHERE `login` LIKE 'kevin_'"; //kevin%
$stmt = $dbh->prepare($sql);
echo $stmt->rowCount();
/ * query * /
$login = 'kevin%';
$sql = "SELECT * FROM `user` WHERE `login` LIKE :login";
$stmt = $dbh->prepare($sql);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
print_r( $stmt->fetchAll(PDO::FETCH_ASSOC));
> > 

Php uses pdo to connect to the mysql database. Is it null?

Why use pdo?
The myql database function provided by php is very powerful. Below is an instance.
<? Php
// Database Public link file
$ Hostname = 'localhost ';
$ Username = '';
$ Password = '';
Mysql_connect ($ hostname, $ username, $ password) or die ("database connection failed! ");
@ Mysql_select_db ('test') or die ("the database does not exist or is unavailable! ");
Mysql_query ("set names utf8 ");
How can I change the pdo class connection of a php connection to a common connection?

No. All functions are used differently.
If you are using a framework, you may be able to directly set the database connection.
If you are writing a database connection, you have to read the manual and use the method you want to connect to and operate the database.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.