Connect phpPDO to mysql

Connect phpPDO to mysql

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 ).

// The PDO driver extension = php_pdo.dll extension = php_pdo_firebird.dll for each database // Firebird extension = php_pdo_informix.dll // Informix extension = login // SQL server extension = login // mysql extension = Development/release // oracle extension = php_pdo_oci8.dll 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:

<? Phpheader ('content-type: text/html; charset = UTF-8 '); try {$ db = new PDO ('mysql: host = 127.0.0.1; dbname = test ', 'root', ''); // query $ 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.

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-Number of columns in the returned result set PDOStatement: debugDumpParams-print an SQL preprocessing command PDOStatement: errorCode-get the SQLSTATE PDOStatement related to the last statement handle operation :: errorInfo-get the extension error message PDOStatement: execute related to the last statement handle operation-execute a preprocessing statement PDOStatement: fetch-get the next row PDOStatement from the result set :: 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-get the next row and return it as an object. PDOStatement: getAttribute-retrieves a statement attribute PDOStatement: getColumnMeta-returns the metadata PDOStatement: nextRowset of a column in the result set-push it to the next row set PDOStatement in the statement handle of a multi-row set:: rowCount-returns the number of rows affected by the previous SQL statement PDOStatement: setAttribute-sets a statement attribute PDOStatement: setFetchMode-sets the default acquisition 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.

<?phptry {$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();}?>

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.

PDOException

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);?>

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.

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)

More PDO methods:

PDO: beginTransaction-start a transaction PDO: commit-Submit a transaction PDO ::__ construct-create a PDO instance PDO :: errorCode-obtain sqlstate pdo: errorInfo-Fetch extended error information associated with the last operation on the database handle PDO: exec-execute an SQL statement, and return the affected number of rows PDO: getAttribute-retrieve the attribute PDO: getAvailableDrivers of a database connection-return an available driver array PDO: inTransaction-check whether PDO is in a transaction:: lastInsertId-return 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-roll back a transaction PDO: setAttribute-set attributes

Exception: getMessage-get the content of the Exception message. Exception: getPrevious-returns the previous Exception in the Exception chain: getCode-Get Exception Code Exception: getFile-get the program file name that encountered an Exception :: getLine-get the row number of the Code with an Exception in the file Exception: getTrace-Get Exception tracing information Exception: getTraceAsString-get the Exception tracing information of the string type Exception :: toString-convert an Exception object to a string Exception: clone-Exception clone

Attribute list:

PDO: PARAM_BOOL represents a boolean type PDO: PARAM_NULL represents the NULL type PDO: PARAM_INT in an SQL statement represents the INTEGER type PDO: PARAM_STR in an SQL statement, VARCHAR type PDO: PARAM_LOB indicates the large object type PDO: PARAM_STMT in an SQL statement. It indicates the recordset type in an SQL statement. PDO :: PARAM_INPUT_OUTPUTSpecifies 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 takes the result of each row as an object Return PDO: FETCH_ASSOC only returns the result set of the query with the key value as the underlying value. Only one PDO is returned for data with the same name :: FETCH_NAMED only returns the result set of the query with the key value as the underlying object. data with the same name is returned in the form of an array PDO: FETCH_NUM only returns the result set PDO :: FETCH_BOTH returns the result set PDO: FETCH_OBJ, which uses both the key value and number as the subobject, and returns the result set PDO: FETCH_BOUND and PDOStatement: bindParam () in the form of an object:: bindColumn () returns PDO: FETCH_COLUMN after the value of bindColumn () is assigned as the variable name, indicating that only one column of PDO in the result set is returned :: FETCH_CLASS indicates that the result set PDO: FETCH_INTO is returned in the form of a class. The data is merged into an existing class and the PDO: FETCH_FUNCPDO: FETCH_GROUPP is returned. DO: FETCH_UNIQUEPDO: FETCH_KEY_PAIR returns the result set PDO: FETCH_CLASSTYPEPDO :: FETCH_SERIALIZE indicates that the data is merged into an existing class and serialized to return PDO: FETCH_PROPS_LATEAvailable since PHP 5.2.0PDO: ATTR_AUTOCOMMIT. When it is set to true, PDO will automatically stop accepting delegation, run PDO: ATTR_PREFETCH to set the data size obtained by the application in advance. Not all databases support PDO: ATTR_TIMEOUT to set the value of PDO :: ATTR_ERRMODE sets the mode of Error processing PDO: ATTR_SERVER_VERSION read-only attribute, indicating the server database version PDO: ATTR_CLIENT_VERSION read-only attribute of the PDO connection, indicating the PDO connection Client PDO driver version PDO: ATTR_SERVER_INFO read-only attribute, indicating the meta information PDO: ATTR_CONNECTION_STATUSPDO: ATTR_CASE through PDO :: the content in CASE _ * performs operations on the column form PDO: ATTR_CURSOR_NAME to obtain or set the pointer name PDO: ATTR_CURSOR to set the pointer type. PDO now supports PDO: CURSOR_FWDONLY and PDO:: Drivers: ATTR_DRIVER_NAME: the name of the PDO driver used to be returned PDO: ATTR_ORACLE_NULLS converts the returned null string to NULLPDO: ATTR_PERSISTENT of SQL to obtain an existing connection PDO: Drivers :: ATTR_FETCH_CATALOG_NAMES in the returned result set, use the custom directory name To replace the field name. PDO: ATTR_FETCH_TABLE_NAMES in the returned result set, use a custom table name instead of the field name. PDO: temperature: ATTR_MAX_COLUMN_LENPDO: ATTR_DEFAULT_FETCH_MODEAvailable since PHP 5.2.0PDO: ATTR_EMULATE_PREPARESAvailable since PHP 5.1.3.PDO: ERRMODE_SILENT no error message is reported. The default value is PDO :: when an error occurs in ERRMODE_WARNING, a php E_WARNING message PDO: ERRMODE_EXCEPTION is generated. When an error occurs, a PDOExceptionPDO: CASE_NATURAL reply column's default display format PDO :: CASE_LOWER forced column names lowercase PDO: CASE_UPPER forced column names uppercase PDO: NULL_NATURALPDO: Upper: NULL_TO_STRINGPDO: FETCH_ORI_NEXT get the next row of data in the result set, valid PDO: FETCH_ORI_PRIOR only when the pointer function is available to obtain the previous row of data in the result set. Valid PDO: FETCH_ORI_FIRST only when the pointer function is available to obtain the first row of data in the result set, valid PDO: FETCH_ORI_LAST only when the pointer function is available to obtain the last row of data in the result set. Valid PDO: FETCH_ORI_ABS only when the pointer function is available to obtain a row of data in the result set, valid only when the pointer function is available: PDO: FETCH_ORI_REL get the data of a row after the current row in the result set. Valid only when the pointer function is available :: CURSOR_FWDONLY creates a backward pointer operation object PDO: CURSOR_SCROLL and transmits the content in PDO: FETCH_ORI _ * to control the result set PDO: ERR_NONE (string) set the error message when no error occurs

<? 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 ();/* modify */$ SQL =" UPDATE 'user' SET 'Password' =: password WHERE 'user _ id' =: userId "; $ stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (array (': userid' => '7', ': password' => '4607e782c4d86fd5364d7e3168bb10d9'); echo $ stmt-> rowCount (); /* DELETE */$ SQL = "DELETE FROM 'user' WHERE 'login' LIKE 'kevin _'"; // kevin % $ stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (); echo $ stmt-> row Count ();/* 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);?>