PHP operation of the MySQL database PDO way

This article mainly introduces the PHP operation of the MySQL database PDO way, has a certain reference value, now share to everyone, the need for friends can refer to

Basic use

1) Connect to the database

$pdo = new PDO ("Mysql:host=localhost; Dbname=db_name ", Username,password);

2) Execute SQL statement

$pdo, query () for operations    that return results apply    to exec (), SELECT$PDO, for     operations that do not return results  

3) Get result set (query operation proprietary)

Fetch ($mode) $stmt      a data $stmt-Fetchall ($mode)   get all data

Mode selects Pdo::fetch_assoc, and the result becomes an associative array .

Instance

<?php//PDO Operation Database Example (query) header ("Content-type:text/html;charset=utf-8");//Connection Database $DSN = "mysql:host=localhost; Dbname=test "; $pdo = new PDO ($DSN, ' root ', ' 123456789 ');/*********** Execute SQL statement  *************///set character set $pdo-exec (" Set names UTF8 "); $sql =" SELECT * from users "; $stmt = $pdo, query ($sql);//Get result set $data = $stmt Fetchall (pdo::fet CH_ASSOC); Var_dump ($data);

Transaction control

1) Open transaction

BeginTransaction, $pdo ()

2) Transaction rollback

Rollback, $pdo ()

3) Transaction Submission

Commit ($PDO)

4) Automatic Submission

SetAttribute, $pdo (pdo::attr_autocommit,1)

Instance

<?phpheader ("Content-type:text/html;charset=utf-8");//Connect database $DSN = "mysql:host=localhost; Dbname=test "; $pdo = new PDO ($DSN, ' root ', ' 123456789 ');//Execute SQL statement//set $PDO, EXEC (" Set names UTF8 ");//Open transaction $pdo-> ;  BeginTransaction ();//SQL statement $SQL1 = "UPDATE users SET ' money ' = ' money ' +1 WHERE ' id ' = 1"; $r 1 = $pdo-EXEC ($sql 1); $sql 2 = "UPDATE users SET ' money ' = ' money '-1 WHERE ' id ' ="; $r 2 = EXEC ($sql 2) $pdo,//Make a judgment if ($r 1 > 0 && $r 2 & Gt 0) {    $pdo, commit ();    Echo ' Operation succeeded ';} else{    $pdo-rollback ();    Echo ' operation failed ';} SetAttribute, $pdo (pdo::attr_autocommit,1);

Pretreatment

1) $sql = "???"

The parameter inside the value of the SQL statement, using a question mark instead.

2) Creating precompiled objects

Prepare, $pdo ($sql)

3) Parameter Assignment

Bindparam, $stmt (parameter location | parameter pseudo-name, variable name (requires value) $stmt-bindvalue (parameter parameter position | parameter pseudo-name, variable name (requires value) | specific value)

$STMT-Bindparam Detailed:

$sql = "UPDATE users SET ' money ' =100 WHERE id =: num";//Note that the $num must first be assigned $num = 3; $stmt, Bindparam (": num", $num); another form (The placeholder becomes a question mark) $sql = "UPDATE users SET ' money ' =100 WHERE id =?"; /Note that the $num must first be assigned $num = 3; $stmt, Bindparam (": num", $num);

Bindvalue, $stmt

After you use the Bindvalue binding variable, the result is unchanged even if you change the value of the variable before executing execute.

For details, please see here to write the link content

4) Code Execution

Execute (), $stmt

5) Get Results (query operation Proprietary)

Fetch ($mode) $stmt to      get a result $stmt-Fetchall ($mode)   get all results

Mode selects Pdo::fetch_assoc, and the result becomes an associative array .

Instance

<?php//mysqli Preprocessing Control Example (query) header ("Content-type:text/html;charset=utf-8");//Connection Database $DSN = "mysql:host=localhost; Dbname=test "; $pdo = new PDO ($DSN, ' root ', ' 123456789 ');//Set $PDO, EXEC (" Set names UTF8 ");//SQL statement $sql =" SELECT * FR OM users WHERE ID >: num ";//Create precompiled object $stmt = $pdo-Prepare ($sql);//Parameter binding $num = 3; $stmt, Bindparam (": num ", $num );//Execute SQL statement $stmt, execute ();//Get result set $data = $stmt-Fetchall (PDO::FETCH_ASSOC); Var_dump ($data);