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!
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.
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.dll 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_oci8.dll extension=php_pdo_odbc.dll //DB2 extension=php_pdo_pgsql.dll //PostgreSQL extension=php_pdo_sqlite.dll //SQLite
// Connect to the database
$ db = new PDO ('mysql: host = localhost; dbname = test', $ user, $ pass );
<?php
header('content-type:text/html;charset=utf-8');
try {
$db = new PDO('mysql:host=127.0.0.1;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/>";
die();
}
print_r($rs);
?>
Don't understand what it means, let's talk about it slowly. This line:
$ Dsn = "mysql: host = 127.0.0.1; 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.
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
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.
$db->exec("DELETE FROM `xxxx_menu` where mid=43");
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.
<?php
try {
$dbh = new PDO('mysql:host=localhost;dbname=test', ‘root', ”);
$dbh->query('set names utf8;');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$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);”);
$dbh->commit();
} catch (Exception $e) {
$dbh->rollBack();
echo “Failed: ” . $e->getMessage();
}
?>
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.
1. PDO: ERRMODE_SILENT
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.
2. PDO: ERRMODE_WARNING
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.
3. PDO: ERRMODE_EXCEPTION
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 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); > >
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.
Note:
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)
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
PDO::PARAM_BOOL Represents a boolean type PDO::PARAM_NULL Represents a null type in SQL PDO::PARAM_INT Represents an integer type in SQL PDO::PARAM_STR Represents the SQL char, varchar type in a SQL PDO::PARAM_LOB Represents a large object type in SQL PDO::PARAM_STMT Represents the recordset type in SQL, which is not supported yet PDO::PARAM_INPUT_OUTPUT 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. PDO::FETCH_LAZY Return the result of each row as an object PDO::FETCH_ASSOC 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 PDO::FETCH_NAMED 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 PDO::FETCH_NUM Returns only the result set of a number based query PDO::FETCH_BOTH Returns the result set of a query with key and number as Subscripts PDO::FETCH_OBJ Returns the result set as an object PDO::FETCH_BOUND Value bound by pdostatement:: bindparam() and pdostatement:: bindcolumn() is used as variable name assignment and returned PDO::FETCH_COLUMN Indicates that only one column in the result set is returned PDO::FETCH_CLASS Represents the return of a result set as a class PDO::FETCH_INTO Represents merging data into an existing class for return PDO::FETCH_FUNC PDO::FETCH_GROUP PDO::FETCH_UNIQUE PDO::FETCH_KEY_PAIR Return the result set in the form of the first key value and the following number PDO::FETCH_CLASSTYPE PDO::FETCH_SERIALIZE Represents merging data into an existing class and serializing the return PDO::FETCH_PROPS_LATE Available since PHP 5.2.0 PDO::ATTR_AUTOCOMMIT When set to true, PDO will automatically try to stop accepting delegation and start execution PDO::ATTR_PREFETCH Setting the data size that the application obtains in advance is not supported by all databases PDO::ATTR_TIMEOUT Set the value of connection database timeout PDO::ATTR_ERRMODE Set the mode of error processing PDO::ATTR_SERVER_VERSION Read only property indicating the server-side database version of PDO connection PDO::ATTR_CLIENT_VERSION Read only property indicating the client PDO driver version of the PDO connection PDO::ATTR_SERVER_INFO Read only attribute, which represents the meta information of the server to which PDO connects PDO::ATTR_CONNECTION_STATUS PDO::ATTR_CASE Operate on the column form through the content in PDO:: case PDO::ATTR_CURSOR_NAME Gets or sets the name of the pointer PDO::ATTR_CURSOR Set the pointer type. PDO now supports PDO:: cursor? Fwonly and PDO:: cursor? Fwonly PDO::ATTR_DRIVER_NAME Returns the name of the PDO driver used PDO::ATTR_ORACLE_NULLS Convert the returned null string to SQL null PDO::ATTR_PERSISTENT Get an existing connection PDO::ATTR_STATEMENT_CLASS PDO::ATTR_FETCH_CATALOG_NAMES In the returned result set, use the custom directory name instead of the field name. PDO::ATTR_FETCH_TABLE_NAMES In the returned result set, use the custom table name instead of the field name. PDO::ATTR_STRINGIFY_FETCHES PDO::ATTR_MAX_COLUMN_LEN PDO::ATTR_DEFAULT_FETCH_MODE Available since PHP 5.2.0 PDO::ATTR_EMULATE_PREPARES Available since PHP 5.1.3. PDO::ERRMODE_SILENT Do not report any error information when an error occurs, which is the default value PDO::ERRMODE_WARNING When an error occurs, send a message of PHP's e'warning PDO::ERRMODE_EXCEPTION Throw a pdoexception when an error occurs PDO::CASE_NATURAL Default display format of reply column PDO::CASE_LOWER Force column name lowercase PDO::CASE_UPPER Force column names to be capitalized PDO::NULL_NATURAL PDO::NULL_EMPTY_STRING PDO::NULL_TO_STRING PDO::FETCH_ORI_NEXT Get the next row of data in the result set, only valid if there is pointer function PDO::FETCH_ORI_PRIOR Get the previous row of data in the result set, only valid if there is pointer function PDO::FETCH_ORI_FIRST Get the first row of data in the result set, only valid if there is pointer function PDO::FETCH_ORI_LAST Get the last row of data in the result set, only valid if there is pointer function PDO::FETCH_ORI_ABS Get a row of data in the result set, only valid when there is pointer function PDO::FETCH_ORI_REL Get the data of a row after the current row in the result set, only valid if there is a pointer function PDO::CURSOR_FWDONLY Create a backward only pointer operand PDO::CURSOR_SCROLL 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->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$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);
$stmt->execute();
echo $stmt->rowCount();
/ * query * /
$login = 'kevin%';
$sql = "SELECT * FROM `user` WHERE `login` LIKE :login";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':login'=>$login));
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
print_r($row);
}
print_r( $stmt->fetchAll(PDO::FETCH_ASSOC));
> >
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.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
