A case of PDO query operation using PDO to connect to mysql

This article mainly introduces relevant information about the detailed explanation of examples of php mysql PDO query operations. I hope this article can help everyone. Friends in need can refer to

php mysql PDO query operations. Detailed example

<?php 
$dbh = new PDO(&#39;mysql:host=localhost;dbname=access_control&#39;, &#39;root&#39;, &#39;&#39;);  
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
$dbh->exec(&#39;set names utf8&#39;);  
/*添加*/ 
//$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(&#39;:login&#39;=>&#39;kevin2&#39;,&#39;:password&#39;=>&#39;&#39;));  
echo $dbh->lastinsertid();  
/*修改*/ 
$sql = "UPDATE `user` SET `password`=:password WHERE `user_id`=:userId";  
$stmt = $dbh->prepare($sql);  
$stmt->execute(array(&#39;:userId&#39;=>&#39;7&#39;, &#39;:password&#39;=>&#39;4607e782c4d86fd5364d7e4508bb10d9&#39;));  
echo $stmt->rowCount();  
/*删除*/ 
$sql = "DELETE FROM `user` WHERE `login` LIKE &#39;kevin_&#39;"; //kevin%  
$stmt = $dbh->prepare($sql);  
$stmt->execute();  
echo $stmt->rowCount();  
/*查询*/ 
$login = &#39;kevin%&#39;;  
$sql = "SELECT * FROM `user` WHERE `login` LIKE :login";  
$stmt = $dbh->prepare($sql);  
$stmt->execute(array(&#39;:login&#39;=>$login));  
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){    
 print_r($row);  
}  
print_r( $stmt->fetchAll(PDO::FETCH_ASSOC));  
?>

1 Establish a connection

<?php 
$dbh=newPDO(&#39;mysql:host=localhost;port=3306; dbname=test&#39;,$user,$pass,array( 
PDO::ATTR_PERSISTENT=>true 
)); 
?>

Persistent Sexual link PDO::ATTR_PERSISTENT=>true

2. Catching errors

<?php 
try{ 
$dbh=newPDO(&#39;mysql:host=localhost;dbname=test&#39;,$user,$pass); 
 
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); 
 
$dbh->exec("SET CHARACTER SET utf8"); 
$dbh=null; //断开连接 
}catch(PDOException$e){ 
print"Error!:".$e->getMessage()."<br/>"; 
die(); 
} 
?>

3. Transaction

<?php 
try{ 
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); 
 
$dbh->beginTransaction();//开启事务 
$dbh->exec("insertintostaff(id,first,last)values(23,&#39;Joe&#39;,&#39;Bloggs&#39;)"); 
$dbh->exec("insertintosalarychange(id,amount,changedate) 
values(23,50000,NOW())"); 
$dbh->commit();//提交事务 
 
}catch(Exception$e){ 
$dbh->rollBack();//错误回滚 
echo"Failed:".$e->getMessage(); 
} 
?>

4. Error handling

a. Silent mode (Default mode)

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT); //不显示错误
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);//显示警告错误，并继续执行
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);//产生致命错误，PDOException

<?php 
try{   
 $dbh = new PDO($dsn, $user, $password);   
 $sql = &#39;Select * from city where CountryCode =:country&#39;;   
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);   
 $stmt = $dbh->prepare($sql);   
 $stmt->bindParam(&#39;:country&#39;, $country, PDO::PARAM_STR);   
 $stmt->execute();   
 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {    
 print $row[&#39;Name&#39;] . "/t";   
 }  
}  // if there is a problem we can handle it here  
catch (PDOException $e) {   
 echo &#39;PDO Exception Caught. &#39;;   
 echo &#39;Error with the database: <br />&#39;;   
 echo &#39;SQL Query: &#39;, $sql;   
 echo &#39;Error: &#39; . $e->getMessage();  
}  
?>

1. Use query()

<?php 
$dbh->query($sql); 当$sql 中变量可以用$dbh->quote($params); //转义字符串的数据 
 
$sql = &#39;Select * from city where CountryCode =&#39;.$dbh->quote($country);  
foreach ($dbh->query($sql) as $row)  {   
 print $row[&#39;Name&#39;] . "/t";   
 print $row[&#39;CountryCode&#39;] . "/t";   
 print $row[&#39;Population&#39;] . "/n";  
}  
?>

2. Use prepare, bindParam and execute [recommended, you can also add, modify, delete]

<?php 
$dbh->prepare($sql); 产生了个PDOStatement对象 
 
PDOStatement->bindParam() 
 
PDOStatement->execute();//可以在这里放绑定的相应变量 
?>

3. Things

<?php  
 try {  
 $dbh = new PDO(&#39;mysql:host=localhost;dbname=test&#39;, &#39;root&#39;, &#39;&#39;);  
 $dbh->query(&#39;set names utf8;&#39;);  
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
 $dbh->beginTransaction();  
 $dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES (&#39;mick&#39;, 22);");  
 $dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES (&#39;lily&#39;, 29);");  
 $dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES (&#39;susan&#39;, 21);");  
 $dbh->commit();  
 } catch (Exception $e) {  
 $dbh->rollBack();  
 echo "Failed: " . $e->getMessage();  
 }  
?>

Common PDO methods:

PDO::query() is mainly used for operations with recorded results returned (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 insert into tb(col1,col2) values(v1,v2),(v11,v22).. For multiple records, lastinsertid() returns only the ID when the first record (v1, v2) was inserted, not the record ID of the last record inserted.
PDOStatement::fetch() is used to obtain a record. Use while to traverse.
PDOStatement::fetchAll() is to get 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 and only needs to be executed once. , jump 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. Can also be used in preprocessing. $stmt->rowcount();
PDOStatement::columncount() is suitable for obtaining the number of columns of records when using the query("select...") method.

Notes:

1. Choose fetch or fetchall?

When using a small record set, using fetchall is more efficient and reduces the number of retrieval times from the database. However, for a large result set, using fetchall will put a lot of burden on 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); Returns the index array
■$row=$rs->fetchAll(PDO::FETCH_OBJ); If fetch() returns the object , if it is fetchall(), return a two-dimensional array composed of objects

The above is the detailed content of A case of PDO query operation using PDO to connect to mysql. For more information, please follow other related articles on the PHP Chinese website!