PHP MySQL PDO use, phpmysqlpdo use _php tutorial

PHP MySQL PDO use, phpmysqlpdo use

1 
 Php2 $DBH=NewPDO (' Mysql:host=localhost;dbname=access_control ', ' root ', '); 3 $DBH->setattribute (Pdo::attr_errmode, PDO::errmode_exception); 4 $DBH-exec(' Set names UTF8 '); 5 /*Add*/6 //$sql = "INSERT into ' user ' SET ' login ' =:login and ' password ' =:p assword";7 $sql= "INSERT into ' user ' (' login ', ' password ') VALUES (: Login,:p assword)";$stmt=$DBH->prepare ($sql);$stmt->execute (Array(': Login ' = ' kevin2 ', ':p assword ' = ')); 8 Echo $DBH-Lastinsertid (); 9 /*Modify*/Ten $sql= "UPDATE ' user ' SET ' password ' =:p assword WHERE ' user_id ' =:userid";  One $stmt=$DBH->prepare ($sql);  A $stmt->execute (Array(': userId ' = ' 7 ', ':p assword ' = ' 4607e782c4d86fd5364d7e4508bb10d9 '));  - Echo $stmt-RowCount (); - /*Delete*/ the $sql= "DELETE from ' user ' WHERE ' login ' like ' kevin_ '";//kevin% - $stmt=$DBH->prepare ($sql);  - $stmt-execute ();  - Echo $stmt-RowCount ();  + /*Enquiry*/ - $login= ' kevin% ';  + $sql= "SELECT * from ' user ' WHERE ' login ' Like:login";  A $stmt=$DBH->prepare ($sql);  at $stmt->execute (Array(': Login ' =$login));  -  while($row=$stmt->fetch (PDO::Fetch_assoc)) {      -  Print_r($row);  - }   - Print_r($stmt->fetchall (PDO::Fetch_assoc));  -?>

1 Establishing the connection

 
 1 
   PHP2$dbh=newpdo (' mysql:host=localhost;port=3306; Dbname=test ',$user,$pass, Array (3 pdo::attr_persistent=>true4)); 5 ?>

Persistent link Pdo::attr_persistent=>true

2. Catching errors

 
 1 
   PHP 2try{  3$dbh=newpdo (' mysql:host=localhost;dbname=test ',$user ,$pass); 4 5 $dbh->setattribute (pdo::attr_errmode,pdo::errmode_exception); 6 7 exec, $dbh("Set CHARACTER set UTF8"); 8 $DBH=null// disconnect 9 }catch(pdoexception$e  {print"error!:"). $e->getmessage (). "
";  One  die ();  A } ?>

3. The Business

1 
 Php2 Try{3 $DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);4 5 $DBH->begintransaction ();//Open Transaction6 $DBH-exec("Insertintostaff (id,first,last) VALUES (' Joe ', ' Bloggs ')");7 $DBH-exec("Insertintosalarychange (id,amount,changedate)8VALUES (23,50000,now ()) ");9 $DBH->commit ();//Commit a transactionTen  One}Catch(Exception$e){ A $DBH->rollback ();//Error Rollback - Echo"Failed:".$e-getMessage (); - } the?>

4. Error handling

A. Silent mode (default mode)

$DBH->setattribute (pdo::attr_errmode,pdo::errmode_silent); Do not display errors

$DBH->setattribute (Pdo::attr_errmode, pdo::errmode_warning);//Display warning error, and continue execution

$DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);//Generate fatal error, pdoexception

1 
 Php2 Try{    3  $DBH=NewPDO ($dsn,$user,$password); 4  $sql= ' Select * from city where CountryCode =:country '; 5  $DBH->setattribute (Pdo::attr_errmode, PDO::errmode_warning); 6  $stmt=$DBH->prepare ($sql); 7  $stmt->bindparam (': Country ',$countryPdo::param_str); 8  $stmt-execute (); 9   while($row=$stmt->fetch (PDO::Fetch_assoc)) {      Ten   Print $row[' Name ']. "/T";  One  }   A}//If there is a problem we can handle it here - Catch(pdoexception$e)  {     -  Echo' PDO Exception caught. ';  the  Echo' Error with the database:
';  -  Echo' SQL Query: ',$sql;  -  Echo' Error: '.$e-GetMessage ();  - }  +?>

1. Use Query ()

 
  PHP$dbh->query ($sql); When $sql variables can be used $dbh->quote ($params// Escape string data $sql = ' Select * From city where CountryCode = '. $dbh->quote ($country);   foreach ($dbh->query ($sqlas$row)   {     print  $row[' Name ']. "/t"     ; Print $row [' CountryCode ']. "/t"     ; Print $row [' Population ']. "/n"?>

2. Use prepare, Bindparam and execute [recommended, and can be added, modified, deleted]

 
  PHP$dbh->prepare ($sql), produced a Pdostatement object pdostatement-Bindparam () Pdostatement->execute (); // you can put the corresponding variable of the binding here ?>

3. Things

 PhpTry {    $DBH=NewPDO (' 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 (); }  ?>

PDO Common methods:
Pdo::query () is primarily used for operations (pdostatement) that have logged results returned, especially for select operations.

Pdo::exec () is primarily for operations returned without a result set. such as Insert,update and other operations. 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): By inserting multiple records at once, Lastinsertid () returns only the ID of the first (V1,V2) insert, not the record ID that was inserted in the last record.
Pdostatement::fetch () is used to get a record. With while to traverse.
Pdostatement::fetchall () is to get all recordsets into one.
Pdostatement::fetchcolumn ([int column_indexnum]) is used for direct access to the column, and the parameter column_indexnum is the 0-based index value in the row of the column, but this method can only get one row at a time. Once executed, jump to the next line. Therefore, it is better to use for direct access to a column, but it is not useful to traverse multiple columns.
Pdostatement::rowcount () is used when using the query ("SELECT ...") method to get the number of records. can also be used in preprocessing. $stmt->rowcount ();
Pdostatement::columncount () is used when using the query ("SELECT ...") method to get the number of columns of a record.

Annotations:
1, pick or fetchall?
Small record set, with fetchall efficiency, reduce the number of retrieval from the database, but for large result sets, with Fetchall to the system brings a great burden. The database is too large to transmit to the web front end and inefficient.
2. Fetch () or Fetchall () has several parameters:
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, can be saved, returns associations and indexes.
$row = $rs->fetchall (PDO::FETCH_ASSOC); The FETCH_ASSOC parameter determines that only the associative array is returned.
$row = $rs->fetchall (pdo::fetch_num); Returns an indexed array
$row = $rs->fetchall (pdo::fetch_obj); If fetch () returns the object, and if it is Fetchall (), returns a two-dimensional array consisting of the object

http://www.bkjia.com/PHPjc/960239.html www.bkjia.com true http://www.bkjia.com/PHPjc/960239.html techarticle PHP MySQL PDO use, phpmysqlpdo use 1? php 2 $dbh = new PDO (' Mysql:host=localhost;dbname=access_control ', ' root ', '); 3 $ Dbh-setattribute (Pdo::attr_errmode, PDO:: Errmode_e ...

