PHP MySQL PDO use

[PHP]View Plaincopyprint?

2. <?php
4. $DBH = New PDO (' Mysql:host=localhost;dbname=access_control ', ' root ', ' );
6. $DBH->setattribute (Pdo::attr_errmode, pdo::errmode_exception);
8. $DBH, EXEC (' Set names UTF8 ');
10. /* Add */
12. $sql = "INSERT into ' user ' SET ' login ' =:login and ' password ' =:p assword";
14. $sql = "INSERT into ' user ' (' login ', ' password ') VALUES (: Login,:p assword)";  $stmt = $dbh->prepare ($sql);    $stmt->execute (': Login ' = 'kevin2 ',':p assword ' = ') ');
16. echo $dbh->lastinsertid ();
18. /* Modify */
20. $sql = "UPDATE ' user ' SET ' password ' =:p assword WHERE ' user_id ' =:userid";
22. $stmt = $dbh->prepare ($sql);
24. $stmt->execute (Array (': userId ' = '7 ', ':p assword ' =')    4607e782c4d86fd5364d7e4508bb10d9 '));
26. echo $stmt->rowcount ();
28. /* Delete */
30. $sql = "DELETE from ' user ' WHERE ' login ' like ' kevin_ '"; //kevin%
32. $stmt = $dbh->prepare ($sql);
34. $stmt->execute ();
36. echo $stmt->rowcount ();
38. /* Query */
40. $login = ' kevin% ';
42. $sql = "select * from ' user ' WHERE ' login ' Like:login";
44. $stmt = $dbh->prepare ($sql);
46. $stmt->execute (Array (': Login ' =$login));
48. while ($row = $stmt->fetch (PDO::FETCH_ASSOC)) {
50. Print_r ($row);
52. }
54. Print_r ( $stmt->fetchall (PDO::FETCH_ASSOC));
56. ?>

1 Establishing the connection

[PHP]View Plaincopyprint?

1. <?php
2. $DBH =newpdo (' mysql:host=localhost;port=3306; Dbname=test ',$user,$pass,Array (
3. Pdo::attr_persistent=>true
4. ));
5. ?>

Persistent link Pdo::attr_persistent=>true

2. Catching errors

[PHP]View Plaincopyprint?

2. <?php
4. try{
6. $DBH =newpdo (' mysql:host=localhost;dbname=test ',$user,$pass);
10. $DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);
14. exec, $DBH ("set CHARACTER set UTF8");
16. $DBH =null; //Disconnect
18. }catch (pdoexception$e) {
20. Print"error!:". $e->getmessage ().  "<br/>";
22. Die ();
24. }
26. ?>

3. The Business

[PHP]View Plaincopyprint?

2. <?php
4. try{
6. $DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);
10. $DBH->begintransaction (); //Open transaction
12. exec (Insertintostaff (id,first,last) VALUES (at $, ' Joe ', ' Bloggs '), $DBH.
14. exec, $DBH ("Insertintosalarychange (id,amount,changedate)
16. VALUES (23,50000,now ()) ");
18. $DBH->commit (); //Commit a transaction
22. }catch (Exception$e) {
24. $DBH->rollback (); //Error rollback
26. Echo"Failed:".   $e->getmessage ();
28. }
30. ?>

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

[PHP]View Plaincopyprint?

2. <?php
4. try{
6. $DBH = new PDO ($dsn, $user, $password);
8. $sql = ' Select * from city where CountryCode =:country ';
10. $dbh->setattribute (Pdo::attr_errmode, pdo::errmode_warning);
12. $stmt = $dbh->prepare ($sql);
14. $stmt->bindparam (': Country ', $country, PDO::P aram_str);
16. $stmt->execute ();
18. While ($row = $stmt->fetch (PDO::FETCH_ASSOC)) {
20. Print $row [' Name '].      "/t";
22. }
24. } //If there is a problem we can handle it here
26. catch (pdoexception $e) {
28. echo ' PDO Exception caught.      ‘;
30. echo ' Error with the database: <br/> ';
32. echo ' SQL Query: ', $sql;
34. echo ' Error: '.    $e->getmessage ();
36. }
38. ?>

1. Use Query ()

[PHP]View Plaincopyprint?

2. <?php
4. $DBH->query ($sql), when the variables in the $sql can be $dbh->quote ($params); //Escape string data
8. $sql = ' Select * from city where CountryCode = '.     $DBH->quote ($country);
10. foreach ($dbh->query ($sql) as $row) {
12. Print $row [' Name '].      "/t";
14. Print $row [' CountryCode '].      "/t";
16. Print $row [' Population '].   "/n";
18. }
20. ?>

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

[PHP]View Plaincopyprint?

1. <?php
2. $DBH->prepare ($sql); A Pdostatement object was created
4. Pdostatement->bindparam ()
6. Pdostatement->execute (); //You can put the corresponding variable in the binding here
7. ?>

3. Things

[PHP]View Plaincopyprint?

2. <?php
4. try {
6. $DBH = new PDO (' mysql:host=localhost;dbname=test ', ' root ', ' );
8. $dbh->query (' Set names utf8; ');
10. $dbh->setattribute (Pdo::attr_errmode, pdo::errmode_exception);
12. $dbh->begintransaction ();
14. $DBH,exec ("Insert into ' test ') ' table ' (' Name ', ' age ') VALUES (' Mick ', 22);");
16. $DBH,exec ("Insert into ' test ') ' table ' (' Name ', ' age ') VALUES (' Lily ', 29);");
18. $DBH,exec ("Insert into ' test ') ' table ' (' Name ', ' age ') VALUES (' Susan ', 21);");
20. $dbh->commit ();
22. } catch (Exception $e) {
24. $dbh->rollback ();
26. echo "Failed:".    $e->getmessage ();
28. }
30. ?>

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

PHP MySQL PDO use