PDO Common methods:
Pdo::query () is used primarily for operations (Pdostatement) that have record results returned, especially select operations.
Pdo::exec () is primarily for operations that are returned without a result collection. such as Insert,update and other operations. Returns the number of rows affected.
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) ... To insert more than one record at a time, Lastinsertid () returns only the ID of the first (V1,V2) insert, not the record ID that was inserted by the last record.
Pdostatement::fetch () is used to get a record. With while to traverse.
Pdostatement::fetchall () is to get all the recordset into one.
Pdostatement::fetchcolumn ([int column_indexnum]) is used to access the column directly, and the parameter column_indexnum is the 0-indexed 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 access a column directly, but it is not necessary to traverse multiple columns.
Pdostatement::rowcount () is used to get the number of records when you use Query ("SELECT ...") method. can also be used in preprocessing. $stmt->rowcount ();
Pdostatement::columncount () is used to get the number of columns for a record when you use query (the "SELECT ...") method.
Annotations:
1, choose to fetch or Fetchall?
Small Recordset, with fetchall efficiency, reduce the number of retrieval from the database, but for large result sets, using Fetchall to the system brings a great burden. It is inefficient for a database to transmit too much to the front end of the web.
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 province, and returns the association and index.
$row = $rs->fetchall (PDO::FETCH_ASSOC); The FETCH_ASSOC parameter determines that only associative arrays are returned.
$row = $rs->fetchall (pdo::fetch_num); Returns an array of indices
$row = $rs->fetchall (pdo::fetch_obj); If fetch () returns the object, if it is Fetchall (), returns a two-dimensional array of objects
Copy Code code 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 (': UserId ' => ' 7 ', ':p assword ' => '));
echo $stmt->rowcount ();
/* Delete * *
$sql = "DELETE from ' the ' user ' WHERE ' login ' like ' kevin_ '"; kevin%
$stmt = $dbh->prepare ($sql);
$stmt->execute ();
echo $stmt->rowcount ();
* * Inquiry * *
$login = ' kevin% ';
$sql = "SELECT * from ' user ' WHERE ' login ' like:login ';
$stmt = $dbh->prepare ($sql);
$stmt->execute (': Login ' => $login));
while ($row = $stmt->fetch (PDO::FETCH_ASSOC)) {
Print_r ($row);
}
Print_r ($stmt->fetchall (PDO::FETCH_ASSOC));
?>
1 Establishing the connection
Copy Code code as follows:
<?php
$DBH =newpdo (' mysql:host=localhost;port=3306 dbname=test ', $user, $pass, Array (
Pdo::attr_persistent=>true
));
?>
Persistence Link Pdo::attr_persistent=>true
2. Catching errors
Copy 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
}catch (pdoexception$e) {
Print "error!:". $e->getmessage (). " <br/> ";
Die ();
}
?>
3. The Business
Copy 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 TRANSACTION
}catch (exception$e) {
$DBH->rollback ();//Error rollback
echo "Failed:". $e->getmessage ();
}
?>
4. Error handling
A. Silent mode (default mode)
Copy Code code as follows:
$DBH->setattribute (pdo::attr_errmode,pdo::errmode_silent); Do not show errors
$DBH->setattribute (Pdo::attr_errmode, pdo::errmode_warning);//Display warning error, and continue execution
$DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);//Fatal error, Pdoexception
Copy Code code as follows:
<?php
try{
$DBH = new PDO ($DSN, $user, $password);
$sql = ' Select * from the 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
catch (Pdoexception $e) {
Echo ' PDO Exception caught. ';
Echo ' Error with the database: <br/> ';
Echo ' SQL Query: ', $sql;
Echo ' Error: '. $e->getmessage ();
}
?>
1. Use Query ()
Copy 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 the 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 Code code as follows:
<?php
$DBH->prepare ($sql); Created a Pdostatement object.
Pdostatement->bindparam ()
Pdostatement->execute ()//You can place the corresponding variable for the binding here
?>
3. Things
Copy 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 PDO in PHP all the content of the relevant usage, I hope this article can be helpful to everyone, but also hope that we can enjoy.
