PDO, PDOStatement, PDOException, pdopdostatement

PDO, PDOStatement, PDOException, pdopdostatement

More detailed information about PDO recently

Source: http://blog.csdn.net/hsst027/article/details/23682003

PDO contains three predefined classes: PDO, PDOStatement, and PDOException.

PDO requires the PDOStatement class object to support preprocessing statements, but this class object is not instantiated through the NEW Keyword, but through the prepare () method in the PDO object, prepare a pre-processed SQL statement on the database server and then return it directly. If the PDOStatement class object returned by the query () method in the PDO object is previously executed, only one result set object is returned. If the PDOStatement class object generated by executing the prepare () method in the PDO object is a query object, parameterized SQL commands can be defined and executed.

PDO:

PDO-> setAttribute ();

Global attribute settings, including column name format and Error Message Type

PDO-> query ($ SQL );

It is often used to execute SQL query statements with returned results, but it must be manually escaped before execution to return the PDOStatement object.

PDO-> exec ($ SQL );

Execute SQL statements that are not returned. You must also manually escape them, including insert, modify, and delete operations. Returns the number of affected rows.

PDO-> lastInsertId ();

Returns the value of the last primary key to be inserted. If a statement is inserted multiple times, the first primary key value is returned: id + rows-1.

PDO-> beginTransaction ();

When a transaction is started, multiple associated executable SQL statements are added in the middle to perform block processing. This is also called the rollback start point)

PDO-> commit ();

The transaction ends, that is, the rollback ends.

PDO-> rollback ();

Rollback of the transaction to the original starting point

PDO-> quto ($ SQL );

Manually escape $ SQL statements to go to the next Processing

PDO-> prepare ($ SQL );

Generate a new compilation template for repeated execution. A PDOStatement object is returned.

PDO->__ construct ();

Create a PDO database connection object instance

PDO-> errorCode ();

Capture PDO error codes

PDO-> errorInfo ();

Capture PDO error messages and return an array of information

PDO-> getAttribute ();

Obtains the attribute information of a database connection object.

PDO-> getAvailableDriver ();

Get the driver name of a valid PDO connection

 

 

PDOStatementThe object is returned by PDO-> query () and PDO-> prepare:

PDOStatement-> fetch ();

Returns a record in a specified format. The content is usually traversed with the while clause.

PDOStatement-> fetchAll ();

Returns all content in a specified format.

PDOStatement-> fetchColumn (intnum );

Returns a value of the specified field index. Each time a row of the specified field is returned, that is, a content value. With while Traversal

PDOStatement-> fetchObject ();

Returns a record in an object-oriented style each time.

PDOStatement-> bindColumn ();

Bind the column in the query record set to the variable, and then use the variable to get the row content of the corresponding column. use $ smt-> fetch (: BOUBD) for traversal.

PDOStatement-> bindParam ();

Bind a variable to a placeholder as a reference to complete compilation! Because it is a reference, it can be repeated multiple times.

PDOStatement-> bindValue ();

Same as above, but can only be bound once

PDOStatement-> closeCursor ();

Close the current template compilation link to prepare the template link created next time. (That is, the template can have only one link)

PDOStatement-> excute ();

Execute the compilation template in two formats. 1. Compile and then execute; 2. Compile and execute

PDOStatement-> columnCount ();

Returns the total number of PDOStatement result set columns.

PDOStatement-> rowCount ();

Returns the total number of rows in the PDOStatement result set.

PDOStatement-> nextRowset ();

It usually works with fetch () and fetchAll () to determine whether the next record exists, so that the next record can be traversed.

PDOStatement-> errorCode ();

Error Code returned

PDOStatement-> errorInfo ();

Error message returned

PDOStatement-> getAttribute ();

 

PDOStatement-> getColumnMeta ();

 

PDOStatement-> setAttribute ();

 

PDOStatement-> setFetchMode ();

 

Parameters and attributes:

1. Global attribute. Set the column name to a certain format and specify an error message.

* ** PDO: ATTR_CASE: (force the column name to be in a format)

PDO: CASE_LOWER: the column name must be in lower case.

PDO: CASE_NATURAL: column names follow the original method

PDO: CASE_UPPER: Force column names to be uppercase.

* ** PDO: ATTR_ERRMODE :( error message .)

PDO: ERRMODE_SILENT: only error codes are displayed. (default)

PDO: ERRMODE_WARNING: displays a warning error.

PDO: ERRMODE_EXCEPTION: throw an exception.

$ Dbh-> setAttribute (PDO: ERR_MODE, PDO: ERRMODE_SILENT); default

$ Dbh-> setAttribute (PDO: ERR_MODE, PDO: ERRMODE_EXCEPTION );

$ Dbh-> setAttribute (PDO: ERR_MODE, PDO: ERRMODE_WARNING );

$ Dbh-> setAttribute (PDO: ATTR_ORACLE_NULLS, PDO: CASE_LOWER );

$ Dbh-> setAttribute (PDO: ATTR_ORACLE_NULLS, PDO: CASE_NATURAL );

$ Dbh-> setAttribute (PDO: ATTR_ORACLE_NULLS, PDO: CASE_UPPER );

 

2. Bind compilation parameters, that is, impose necessary restrictions on parameters bound to placeholders, such as type and length.

PDO_PARAM_BOOL boolean type.

PDO_PARAM_INT integer.

PDO_PARAM_STR char, varchar, and other string types.

This type is used when the PDO_PARAM_input_output parameter is passed to the stored procedure. It can be modified after the procedure is executed.

PDO_PARAM_NULL NULL type.

PDO_PARAM_LOG large object type.

PDO_PARAM_STMT PDO object type, which cannot be operated currently.

$ Something-> bindParam (': calories', $ calories, PDO: PARAM_INT );

$ Something-> bindParam (': color', $ color, PDO: PARAM_STR, 12 );

$ Something-> bindValue (': color', $ color, PDO: PARAM_STR );

 

3. Set the corresponding format for the returned value,-> fetch (),-> fetchAll ()

PDO: FETCH_BOTH -- both arrays form, ** this is the default **

$result = $sth->fetch(PDO::FETCH_BOTH);

PDO: FETCH_ASSOC -- join array form

$result = $sth->fetch(PDO::FETCH_ASSOC);

PDO: FETCH_NUM -- numeric index array format

$result = $stmt->fetch(PDO::FETCH_NUM);

PDO: FETCH_OBJ -- same as fetchObject () in object format ()

$result = $sth->fetch(PDO::FETCH_OBJ);

 

$result = $sth->fetch(PDO::FETCH_LAZY);

 

In fact, the most difficult part of PDO is its understanding of various parameters, and there is very little Chinese information in this aspect, so it is being updated...

The returned structure is a certain array and object structure. The value is usually taken in traversal and while mode.

 

The following describes the database connection and related operations: insert, search, modify, and delete.

The instantiation process of the following two connected databases is equivalent!

$ Dsn ='Mysql: host = localhost; dbname = world ;'; // Data source name, which loads the corresponding driver for a specific database

$ User ='Root';

$ Password ='Root';

Try{

$ Dbh =NewPDO ($ dsn, $ user, $ password [, driverOptions]); // instantiate a database object

$ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // you can specify global attributes.

}Catch(PDOException $ e ){

Echo 'error :'. $ E-> getMessage (); // error exception, throw a prompt

}

 

Try{

$ Pdo =NewPDO ($ dsn, $ user, $ password,Array(PDO: ATTR_PERSISTENT =>TRUE, PDO: ATTR_CASE => PDO: CASE_UPPER, PDO: ATTR_ERRMODE => PDO: ERRMODE_EXCEPTION); // you can use $ row ['City'] To access the column value, instead of using $ row ['City'] Access in upper case

}Catch(PDOException $ e ){

Echo 'error :'. $ E-> getMessage (); // error exception, throw a prompt

}

 

PDO contains three predefined classes: PDO, PDOStatement, and PDOException.

The cache of persistent connections allows you to avoid the resource consumption of deploying a new connection every time the script needs to talk to the database, making your Web application faster.

Array (PDO: ATTR_PERSISTENT => true) is to set the connection type to persistent connection.

 

There are four types of discussions:

1.PDO-> query () is mainly used for operations that have records returned, especially query operations. Returns the PDOSatement object.

PDO-> exec () is mainly for operations that are not returned by the result set. Such as setting, modifying, deleting, and inserting. Returns the number of affected rows.

 

2.PDO-> lastInsertId () returns the last ID of the last insert operation, but note: 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. To unify all the Methods: $ id = PDO-> lastInsertId () + rows-1;

PDOStatement: fetch () each time a record is obtained (that is, a row ). CooperationWhileTo traverse.

PDOStatement: fetchAll () is to get all record sets to a variable.

PDOStatement: fetchcolumn ([intindex]) is like a single-column fetch () access. This method is used to directly access a column. The index parameter is the index value starting from 0 in the row. However, this method can only retrieve one column in the same row at a time. If it is executed once, it will jump to the next row. It is like accessing only one content at a time.

PDOStatement: rowcount () 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 () is used to obtain the number of columns of records when the query ("select...") method is used.

The record set is more efficient than fetchAll, reducing the number of retrieval times from the database. However, for large result sets, using fetchAll brings a great burden to the system.

2. Several Parameters of fetch () or fetchall () are described as follows:

Mixedpdostatement: fetch ([int fetch_style [, int cursor_orientation [, intcursor_offset])

ArrayPdostatement: fetchAll (intfetch_style)

Fetch_style parameters:

$ Row = $ rs-> fetchAll (PDO: FETCH_BOTH); FETCH_BOTH is the default value, which can be saved and returns associations and indexes.

$ 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, the object is returned. If it is fetchall (), a two-dimensional array composed of objects is returned.

Foreach($ RowAs$ R ){

Echo"{$ R-> cityid}: city {$ r-> city}. <br/> ";}

$ Row = $ rs-> fetchAll (PDO: FETCH_LAZY );

$ Row = $ rs-> fetch (PDO: FETCH_LAZY); FETCH_LAZY:

PDORowObject ([queryString] => select * from city // This is useful in specific scenarios.

[Cityid] => 2

[City] => Fuzhou

[Countryid] => 1)

$ Row = $ stmt-> fetch (PDO: FETCH_BOUND); used for returning fetchTrueAnd assign the obtained column value

??? The variable specified in bindParam.

 

3. Pre-processing: (search, insert, modify, delete, and other batch data)

Meaning: batchcompute repetitive operations to implement modular processing (can be automatically escaped)

Mysql supports two placeholder Methods: Question Mark parameter "?" And the name parameter ": field name". The name parameter is clearer.

$ Q = "insertinto city (city, countryid) values (?,?) ";

// Or $ q = "insert into city (city, countryid) values (: city,: countryid )";

After execute: $ pdo-> lastinsertid (); pre-processing is also available.

There are two ways: Pay attention to the two ways with "?" The question mark parameter has different execute placeholder references. And the offset base is different.

(1) explicitly pass the parameter value to execute: booleanPDOSTatement: execute ([ArrayInput_parameters])

Naming parameters:

$ Q = "insert into city (city, countryid) values (: city,: countryid )";

$ Stmt = $ pdo-> prepare ($ q );

$ Stmt-> execute (Array(': City'=>'Hubei',': Countryid'=>'2'));

// This rule can be executed multiple times. Note that countryid is numeric, but it must be enclosed in single quotes.

Question mark parameter:

$ Q = "insertinto city (city, countryid) values (?,?) ";

$ Stmt = $ pdo-> prepare ($ q );

$ Stmt-> execute (Array('0'=>'Hubei','1'=>'2'));

// Note that '? Instead, use the index offset starting from 0 as the placeholder above,

(2) bind parameters. BooleanPDOSTatement: bindParam (mixed parameter, mixed & variable [, intdatatype [, int length [, mixed driver_option])

Mixed parameter is a placeholder? When the question mark parameter is used, parameter is the index offset starting from 1 of The placeholder, instead '? '.

Pre-processing binding column:

BooleanPDOStatement: bindcolum (mixed column_index, mixed & param [, inttype [, int maxlen [, mixed driver_options]);

The column_index parameter is the column offset in the row to be bound, starting from 1. You can also use column names. Type is used to set the value of the type restriction variable. For example, PDO: PARAM_STR is the restricted text, and the specific parameter bindParam (). Use maxlen to limit its length.

$ Calories = 150;

$ Color = 'red ';

$…… = $ Dbh-> prepare ('select name, color, caloriesFROM fruit

WHERE calories <: calories AND color =: color ');

$ Something-> bindParam (': calories', $ calories, PDO: PARAM_INT); // The value must be of the int type.

$ Something-> bindParam (': color', $ color, PDO: PARAM_STR, 12); // a string with a maximum length of 12

$ Something-> execute ();

 

$ Calories = 150;

$ Color = 'red ';

$…… = $ Dbh-> prepare ('select name, color, caloriesFROM fruit

WHERE calories <: calories AND color =: color ');

$ Something-> execute (array (': calories' => $ calories, ': color' => $ color); // directly in the form of an array

 

$ Calories = 150;

$ Color = 'red ';

$…… = $ Dbh-> prepare ('select name, color, caloriesFROM fruit

WHERE calories <? AND color =? ');

$ Something-> execute (array ($ calories, $ color ));

 

$ Calories = 150;

$ Color = 'red ';

$…… = $ Dbh-> prepare ('select name, color, caloriesFROM fruit

WHERE calories <? AND color =? ');

$ Something-> bindParam (1, $ calories, PDO: PARAM_INT );

$ Something-> bindParam (2, $ color, PDO: PARAM_STR, 12 );

$ Something-> execute ();

 

4. Use PDO to process transactions: (associated data processing, including prepare ())

Meaning: Perform transaction processing on the content that requires the associated data to be operated at the same time, either fail at the same time or succeed at the same time (most suitable for processing associated data)

The function of the PDO->__ construct () method is to create a PDO-Linked database instance.

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 =NewPDO ('Mysql: host = localhost; dbname = Test','Root','');

$ Dbh-> exec ("Set names 'utf-8 '");

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

 

5. Manually escape

PDO-> quto ();

PDO-> query ();

PDO-> exec ();

Manually escape transaction processing first