PHP connection mode PDO use detailed, PDO Wan apply Xuan "в_php tutorial

Source: Internet
Author: User
Tags rowcount

MySQL connection in PHP PDO use detailed, PDO WAN apply Xuan "в


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

Copy CodeThe code is as follows:
<?php
$DBH = new PDO (' Mysql:host=localhost;dbname=access_control ', ' root ', ');
$DBH->setattribute (Pdo::attr_errmode, pdo::errmode_exception);
$DBH->exec (' Set names UTF8 ');
/* Add */
$sql = "INSERT into ' user ' SET ' login ' =:login and ' password ' =:p assword";
$sql = "INSERT into ' user ' (' login ', ' password ') VALUES (: Login,:p assword)"; $stmt = $dbh->prepare ($sql); $stmt->execute (': Login ' = ' kevin2 ', ':p assword ' = ') ');
echo $DBH->lastinsertid ();
/* Modify */
$sql = "UPDATE ' user ' SET ' password ' =:p assword WHERE ' user_id ' =:userid";
$stmt = $dbh->prepare ($sql);
$stmt->execute (Array (': userId ' = ' 7 ', ':p assword ' = ' 4607e782c4d86fd5364d7e4508bb10d9 '));
echo $stmt->rowcount ();
/* Delete */
$sql = "DELETE from ' user ' WHERE ' login ' like ' kevin_ '"; kevin%
$stmt = $dbh->prepare ($sql);
$stmt->execute ();
echo $stmt->rowcount ();
/* Query */
$login = ' kevin% ';
$sql = "SELECT * from ' user ' WHERE ' login ' Like:login";
$stmt = $dbh->prepare ($sql);
$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

Copy the Code code as follows:
<?php
$DBH =newpdo (' mysql:host=localhost;port=3306; Dbname=test ', $user, $pass, Array (
Pdo::attr_persistent=>true
));
?>

Persistent link Pdo::attr_persistent=>true

2. Catching errors

Copy the Code code as follows:
<?php
try{
$DBH =newpdo (' mysql:host=localhost;dbname=test ', $user, $pass);
$DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);
$DBH->exec ("Set CHARACTER set UTF8");
$DBH =null; Disconnect Connection
}catch (pdoexception$e) {
Print "error!:". $e->getmessage (). "
";
Die ();
}
?>

3. The Business

Copy the Code code as follows:
<?php
try{
$DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);
$DBH->begintransaction ();//Open transaction
$DBH->exec ("Insertintostaff (Id,first,last) VALUES (+, ' Joe ', ' Bloggs ')");
$DBH->exec ("Insertintosalarychange (id,amount,changedate)
VALUES (23,50000,now ()) ");
$DBH->commit ();//Commit a transaction
}catch (exception$e) {
$DBH->rollback ();//Error rollback
echo "Failed:". $e->getmessage ();
}
?>

4. Error handling

A. Silent mode (default mode)

The

copy Code code is as follows:
$dbh->setattribute (pdo::attr_errmode,pdo::errmode_silent);//Do not display error
$ Dbh->setattribute (Pdo::attr_errmode, pdo::errmode_warning);//Displays a warning error and continues execution of
$dbh->setattribute (pdo::attr _errmode,pdo::errmode_exception);//Generate fatal error, pdoexception

Copy the Code code as follows:
<?php
try{
$DBH = new PDO ($DSN, $user, $password);
$sql = ' Select * from city where CountryCode =:country ';
$DBH->setattribute (Pdo::attr_errmode, pdo::errmode_warning);
$stmt = $dbh->prepare ($sql);
$stmt->bindparam (': Country ', $country, PDO::P aram_str);
$stmt->execute ();
while ($row = $stmt->fetch (PDO::FETCH_ASSOC)) {
Print $row [' Name ']. "/t";
}
}//If there is a problem we can handle it here
catch (Pdoexception $e) {
Echo ' PDO Exception caught. ';
Echo ' Error with the database:
';
Echo ' SQL Query: ', $sql;
Echo ' Error: '. $e->getmessage ();
}
?>

1. Use Query ()

Copy the Code code as follows:
<?php
$DBH->query ($sql); When the $sql variable can be used $dbh->quote ($params); Escaping the data of a string
$sql = ' Select * from city where CountryCode = '. $dbh->quote ($country);
foreach ($dbh->query ($sql) as $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]

Copy the Code code as follows:
<?php
$DBH->prepare ($sql); Produced a Pdostatement object.

Pdostatement->bindparam ()

Pdostatement->execute ();//You can put the corresponding variable of the binding here
?>

3. Things

Copy the Code code as follows:
<?php
try {
$DBH = new PDO (' 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 ();
}
?>

The above is about the use of PDO in PHP all the content, I hope this article can help you, but also hope that we can like.

http://www.bkjia.com/PHPjc/960715.html www.bkjia.com true http://www.bkjia.com/PHPjc/960715.html techarticle MySQL connection in PHP in the way PDO use detailed, PDO WAN apply Xuan "вpdo common methods: Pdo::query () is mainly used for logging results returned operations (pdostatement), especially the select operation. ...

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.