PHP class module engine PDO operation MySQL database simple elaboration

What is PDO?

Popular saying is someone else wrote a "Database Operations Tool Class", it is very powerful, can deal with almost all major databases on the market,

The specific application has such a relationship:

That is, to manipulate some kind of data, you have to "open" the corresponding PDO engine.

In the php.ini configuration file, there is nothing more than a "module", we just have to delete the semicolon to open! As follows:

Change are:

connect to the MySQL database using PDO

1. $DSN = "mysql:host= server address/name; port= port number; dbname= database name";
2. $opt = Array (pdo::mysql_attr_init_command=> ' Set names Connection code ');
3. $pdo = new PDO ($DSN, "username", "password", $opt);

It is visible that a PDO object is returned,

Use of PDO objects (common methods)

$result = $pdo->query ("SQL statement that returns the result set");

Results:

Success: is a PDO result set object (see following);

Failed: false;

$result = $pdo->exec ("Adding and deleting SQL statements");

Result: True (indicates success), False (indicates failure);

$pdo = null; Destroy the object;

Other operations

• $pdo->lastinsertid ();

Gets the last added ID value

• $pdo->begintransaction ();:

Open a transaction

• $pdo->commit ()

Commit a transaction

• $pdo->rollback ();

rolling back a transaction;

• $pdo->intransaction ();

Determines whether the current row is in a transaction and returns True/false

• $pdo->setattribute (attribute name, attribute value);

Sets the property value of the PDO object;

Example: $pdo->setattribute (Pdo::attr_errmode, pdo::errmode_exception)

error handling for PDO

• Silent mode

By default, PDO handles errors with "silent mode":

Just after the error has occurred, do not prompt, but return false. We need to determine in the program whether the return is Fale, then, if it is false, then go "active" to get the error message. --As with MySQL!

Compare MySQL:

$sql = "updateeeee tab SET name = ' abc ';";

$result = mysql_query ($sql); Here, the SQL statement is executed, and there is definitely an error

if ($result = = = False) {

echo "Error occurred:". Mysql_error ();

}

Else{...}

For PDO, this is roughly the case:

$sql = "updateeeee tab SET name = ' abc ';";

$result = $pdo->exec ($sql); Here, the SQL statement is executed, and there is definitely an error

if ($result = = = False) {

echo "Error occurred:". $pdo->errorinfo (); Here is just a hint;

The fact is: $pdo->errorinfo () returns an "array" with an entry labeled 3, which is the error message

}

Else{...}

• Exception mode

It can be simply understood as: a syntactic structure that adapts to the processing errors of object-oriented syntax. As shown below:

try{

Here, you can execute a "possible error" statement (more than one can);

Once an error occurs, the subsequent execution of the current scope is terminated,

And immediately jump to the catch section--handling errors!

}

catch (Exception $e) {

Once an error occurs, it is entered here, and an "Error object" is generated;

The Error object is an instance of the System class exception: it contains the error message.

}

To use the exception mode, PDO has to be set specifically (because it defaults to silent mode):

result set object for PDO (pdostatement) where does PDO's result set object come from?

The result set object of the PDO is obtained when the PDO object executes the "SQL statement of the returned DataSet" and succeeds.

$stmt = $pdo->query ("Select ..... “); If the execution succeeds, then $stmt is the result set object of the PDO

common methods for PDO result set objects

• $stmt = $pdo->query ("Select ..... ");//This is to get the result set
• $stmt->rowcount (); Get the number of rows in the result set
• $stmt->columncount (); Get the number of columns in the result set
• $stmt->fetch ([return type]); Remove "one row" of data from the result set;

The result of the removal is determined by the "return type", which is commonly used:

pdo::fetch_ ASSOC: Represents an associative array
pdo::fetch_num: Represents an indexed array
pdo::fetch_both: Indicates both before, this is the default value
pdo::fetch_obj: Representing object

• $stmt->fetchall ([return type]); Gets all the data in the result set once, returns a two-dimensional array
• $stmt->fetchcolumn ([$i]); Gets the value of the $i field of the next row of data in the result set, which results in a "scalar data"
• $stmt->fetchobject ();
• $stmt->errorcode ();: PDO result set error code
• $stmt->errorinfo (); Error message for PDO result set (is an array)
• $stmt->closecursor (); Close result set (equivalent to Mysql_close ())

pre-processing syntax in PDO What do you mean preprocessing syntax

is, in order to "repeat" multiple structures of similar SQL statements, and the form of the SQL statement "pre-Processing" (compilation);

The "form" of the SQL statement contains "data items not given".

Then, by the time of formal execution, given the corresponding formal "data item", it can be executed more quickly and conveniently.

For example (there are two predefined syntaxes):

Syntax 1:

$sql = "SELECT * from tab where id =?"; Here This "? "is a data item that is not given; This is often called a" placeholder. "

It can also be multiple greetings.

Syntax 2:

$sql = "SELECT * from tab where id =: v1 and name =: V2"; Here the ": v1" and ": V2" are data items that are not given, usually called "named parameters";

How to use?

In 3 steps:

1, "preprocessing" the SQL statement containing the preprocessing syntax:

$stmt = $pdo->prepare ($sql); //

2, assign values to the unassigned data of the result object ($stmt) as described above:

$STMT->bindvalue (data item 1, value 1);

$STMT->bindvalue (data item 2, value 2);

。。。。。。

3, implementation;

$stmt->execute ();

After that, the SQL statement is officially completed!

PHP class module engine PDO operation MySQL database simple elaboration