Use php mysql PDO

$ Dbh = new PDO ('mysql: host = localhost; dbname = access_control ', 'root', ''); <br/> $ dbh-> setAttribute (PDO :: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); <br/> $ dbh-> exec ('set names utf8 '); <br/>/* Add */<br/> // $ SQL = "INSERT INTO 'user' SET 'login' =: login AND 'Password' =: password "; <br/> $ SQL = "INSERT INTO 'user' ('login', 'Password') VALUES (: login,: password )"; <br/> $ stmt = $ dbh-> prepare ($ SQL); <br/> $ stmt-> execute (array (': login' => 'kevin2 ', ': password' => ''); <br/> echo $ dbh-> lastinsertid (); <br/>/* modify */<br/> $ SQL = "UPDATE 'user' SET 'Password' =: password WHERE 'user _ id' =: userId "; <br/> $ stmt = $ dbh-> prepare ($ SQL); <br/> $ stmt-> execute (array (': userid' => '7 ', ': password' => '4607e782c4d86fd5364d7e00008bb10d9'); <br/> echo $ stmt-> rowCount (); <br/>/* DELETE */<br/> $ SQL = "DELETE FROM 'user' WHERE 'login' LIKE 'kevin _'"; // kevin % <br/> $ stmt = $ dbh-> prepare ($ SQL); <br/> $ stmt-> execute (); <br/> echo $ stmt-> rowCount (); <br/>/* query */<br/> $ login = 'kevin % '; <br/> $ SQL = "SELECT * FROM 'user' WHERE 'login' LIKE: login"; <br/> $ stmt = $ dbh-> prepare ($ SQL ); <br/> $ stmt-> execute (array (': login' => $ login); <br/> while ($ row = $ stmt-> fetch (PDO:: FETCH_ASSOC) {<br/> print_r ($ row); <br/>}< br/> print_r ($ stmt-> fetchAll (PDO: FETCH_ASSOC )); 

Mysql common: http://blog.csdn.net/yjj1s/archive/2011/05/23/6440345.aspx

1. Establish a connection

$ Dbh = new PDO ('mysql: host = localhost; port = 3306; dbname = test', $ user, $ pass, array (
PDO: attr_persistent => true
));

Persistent LinkPDO: ATTR_PERSISTENT => true

2. capture errors

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

$ Dbh-> setAttribute (PDO: ATTR_ERRMODE,PDO: errmode_exception);

$ Dbh-> exec ("set character set utf8 ");
$ DBH = NULL; // disconnect
} Catch (Pdoexception$ E ){
Print "Error! : ". $ E-> getMessage ()." <br/> ";
Die ();
}

3. Transaction

Try {
$ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION );

$ Dbh->Begintransaction (); // Start the transaction
$ Dbh-> exec ("insert into staff (id, first, last) values (23, 'job', 'bloggs ')");
$ Dbh-> exec ("insert into salarychange (id, amount, changedate)
Values (23,500 00, NOW ())");
$ Dbh->Commit (); // Submit the transaction

} Catch (Exception $ e ){
$ Dbh->Rollback (); // Error rollback
Echo "Failed:". $ e-> getMessage ();
}

4. handle errors

A. Silent mode (default mode)

$ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_SILENT); // No error is displayed.

$ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_WARNING); // displays a warning error and continues execution

$ Dbh-> setAttribute (PDO: ATTR_ERRMODE,PDO: errmode_exception); // Generates a fatal error, PDOException

Try <br/>{< br/> $ dbh = new PDO ($ dsn, $ user, $ password ); <br/> $ SQL = 'select * from city where CountryCode =: country '; <br/> $ dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_WARNING ); <br/> $ stmt = $ dbh-> prepare ($ SQL); <br/> $ stmt-> bindParam (': country', $ country, PDO :: PARAM_STR); <br/> $ stmt-> execute (); <br/> while ($ row = $ stmt-> fetch (PDO: FETCH_ASSOC )) {<br/> print $ row ['name']. "/t "; <br/>}< br/> // if there is a problem we can handle it here <br/> catch (PDOException $ e) <br/>{< br/> echo 'pdo Exception Caught. '; <br/> echo 'error with the database: <br/>'; <br/> echo 'SQL Query:', $ SQL; <br/> echo 'error :'. $ e-> getMessage (); <br/>} 

======================================

1. UseQuery ()

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

$ SQL = 'select * from city where countrycode = '. $ DBH-> quote ($ country); <br/> foreach ($ DBH-> query ($ SQL) as $ row) <br/> {<br/> Print $ row ['name']. "/t"; <br/> Print $ row ['countrycode']. "/t"; <br/> Print $ row ['population']. "/N"; <br/>} 

 

2. UsePrepare, bindparamAndExecute [recommended, and can be added, modified, or deleted]

$ Dbh->Prepare($ SQL );PdostatementObject

PDOStatement->Bindparam()

PDOStatement->Execute(); // You can put the bound variable here

3. Transactions

<? Php <br/> try {<br/> $ dbh = new PDO ('mysql: host = localhost; dbname = test', 'root ',''); <br/> $ dbh-> query ('set names utf8; '); <br/> $ dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); <br/> $ dbh-> beginTransaction (); <br/> $ dbh-> exec ("Insert INTO 'test '. 'table' ('name', 'age') VALUES ('mick', 22 );"); <br/> $ dbh-> exec ("Insert INTO 'test '. 'table' ('name', 'age') VALUES ('lily', 29); "); <br/> $ dbh-> exec ("Insert INTO 'test '. 'table' ('name', 'age') VALUES ('Susan ', 21); "); <br/> $ dbh-> commit (); <br/>} catch (Exception $ e) {<br/> $ dbh-> rollBack (); <br/> echo "Failed :". $ e-> getMessage (); <br/>}< br/>?> 

Common PDO methods:
PDO ::Query ()It is mainly used for operations (PDOStatement) that return record results, 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 ([int column_indexnum]) is used to directly access a column. The parameter column_indexnum 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)

Fetch_style parameters:
■ $ ROW = $ RS-> fetchall (PDO: FETCH_BOTH); Fetch_both is the default value, which can save, return Association and index.
■ $ ROW = $ RS-> fetchall (PDO: FETCH_ASSOC); The fetch_assoc parameter determines that only the associated array is returned.
■ $ ROW = $ RS-> fetchall (PDO: FETCH_NUM); Returns the Index Array
■ $ ROW = $ RS-> fetchall (PDO: FETCH_OBJ); If fetch () is returned, if fetchall () is returned, a two-dimensional array composed of objects is returned,

PDO MySQL DSN (Data Source Name) http://www.php.net/manual/zh/ref.pdo-mysql.connection.php

 

Reference: http://hi.baidu.com/traindiy/blog/item/050f3087a544e621c75cc378.html

Http://www.php.net/manual/zh/book.pdo.php

• PDO-The PDO class
  • PDO: beginTransaction-Initiates a transaction
  • PDO: commit-Commits a transaction
  • PDO: :__ construct-Creates a PDO instance representing a connection to a database
  • PDO: errorCode-Fetch the SQLSTATE associated with the last operation on the database handle
  • PDO: errorInfo-Fetch extended error information associated with the last operation on the database handle
  • PDO: exec-Execute an SQL statement and return the number of affected rows
  • PDO: getAttribute-Retrieve a database connection attribute
  • PDO: getAvailableDrivers-Return an array of available PDO drivers
  • PDO: inTransaction-Checks if inside a transaction
  • PDO: lastInsertId-Returns the ID of the last inserted row or sequence value
  • 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-Rolls back a transaction
  • PDO: setAttribute-Set an attribute
• PDOStatement-The PDOStatement class
  • PDOStatement-> bindColumn-Bind a column to a PHP variable
  • PDOStatement-> bindParam-Binds a parameter to the specified variable name
  • PDOStatement-> bindValue-Binds a value to a parameter
  • PDOStatement-> closeCursor-Closes the cursor, enabling the statement to be executed again.
  • PDOStatement-> columnCount-Returns the number of columns in the result set
  • PDOStatement-> debugDumpParams-Dump an SQL prepared command
  • PDOStatement-> errorCode-Fetch the SQLSTATE associated with the last operation on the statement handle
  • PDOStatement-> errorInfo-Fetch extended error information associated with the last operation on the statement handle
  • PDOStatement-> execute-Executes a prepared statement
  • PDOStatement-> fetch-Fetches the next row from a result set
  • Pdostatement-> fetchall-returns an array containing all of the result set rows
  • Pdostatement-> fetchcolumn-returns a single column from the next row of a result set
  • Pdostatement-> fetchobject-fetches the next row and returns it as an object.
  • Pdostatement-> getattribute-retrieve a statement attribute
  • Pdostatement-> getcolumnmeta-returns metadata for a column in a result set
  • Pdostatement-> nextrowset-advances to the next rowset in a multi-rowset statement handle
  • Pdostatement-> rowcount-returns the number of rows affected by the last SQL statement
  • Pdostatement-> setattribute-set a statement attribute
  • Pdostatement-> setfetchmode-set the default fetch mode for this statement