Detailed explanation of the use of PDO mysql connection method in php_PHP tutorial

Detailed explanation of the use of PDO, the mysql connection method in PHP

This article mainly introduces in detail the various usage methods of PDO, the mysql connection method in PHP. It is a personal summary. If there are any omissions Please also let me know so that friends in need can refer to it.

Common methods of PDO:

PDO::query() is mainly used for operations that return recorded results (PDOStatement), especially select operations.

PDO::exec() is mainly for operations that do not return 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 insertion operation, but please note: if you use insert into tb(col1,col2) values(v1,v2),(v11,v22).. to insert multiple records at one time , lastinsertid() returns only the ID of the first record (v1, v2) inserted, not the record ID of the last record inserted.
PDOStatement::fetch() is used to obtain a record. Use while to traverse.
PDOStatement::fetchAll() fetches all records into one.
PDOStatement::fetchcolumn([int column_indexnum]) is used to directly access the column. The parameter column_indexnum is the index value of the column in the row starting from 0. However, this method can only obtain one column of the same row at a time. As long as it is executed once, it will jump. Go to the next line. Therefore, it is easier to use when directly accessing a certain column, but it is not useful when traversing multiple columns.
PDOStatement::rowcount() is suitable for obtaining the number of records when using the query("select...") method. It can also be used in preprocessing. $stmt->rowcount();
PDOStatement::columncount() is suitable for obtaining the number of columns in a record when using the query("select...") method.

Note:
1. Choose fetch or fetchall?
When the record set is small, using fetchall is efficient and reduces the number of retrieval times from the database. However, for large result sets, using fetchall brings a lot of burden to the system. The amount of data the database needs to transmit to the WEB front-end is too large 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 parameter:
■$row=$rs->fetchAll(PDO::FETCH_BOTH); FETCH_BOTH is the default, can be omitted, 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); Return index array
■$row=$rs->fetchAll(PDO::FETCH_OBJ); If fetch() returns an object, if fetchall() returns a two-dimensional array composed of objects

Copy the code The code is as follows:

$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`=:password";
$sql = "INSERT INTO `user` (`login` ,`password`)VALUES (:login, :password)"; $stmt = $dbh->prepare($sql); $stmt->execute(array (':login'=>'kevin2',':password'=>''));
echo $dbh->lastinsertid();
/*Modify*/
$sql = "UPDATE `user` SET `password`=:password WHERE `user_id`=:userId";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(':userId'=>'7', ':password'=>'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 Establish connection

The code is as follows:

$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

The code is as follows:

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()."
";
die();
}
?>

3. Business

The code is as follows:

try{
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();//Open transaction
$dbh->exec("insertintostaff(id,first,last)values(23,'Joe','Bloggs')");
$dbh->exec("insertintosalarychange(id,amount,changedate)
values(23,50000,NOW())");
$dbh->commit();//Submit transaction
}catch(Exception$e){
$dbh->rollBack();//Error rollback
echo"Failed:".$e->getMessage();
}
?>

4. Error handling

a. Silent mode (default mode)

The code is as follows:

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT); //Do not display errors
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);//Display warning errors and continue execution
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);//A fatal error occurs, PDOException

The code is as follows:

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::PARAM_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()

The code is as follows:

$dbh->query($sql); When variables in $sql can be used $dbh->quote($params); //Escape string data
$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, you can also add, modify, delete]

Copy the code The code is as follows:

$dbh->prepare($sql); generates a PDOStatement object

PDOStatement->bindParam()

PDOStatement->execute();//You can put the corresponding bound variables here
?>

3. Things

Copy the code The code is as follows:

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 all about the usage of pdo in PHP. I hope this article can be helpful to everyone, and I hope everyone likes it.

http://www.bkjia.com/PHPjc/960578.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/960578.htmlTechArticleDetailed explanation of the use of PDO, the mysql connection method in PHP. This article mainly introduces in detail the various uses of PDO, the mysql connection method in PHP. The method is a personal summary. If there are any omissions, please let me know. If necessary...