欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

php mysql连接方式pdo用法详解

程序员文章站 2022-05-10 12:24:19
...
  1. $dbh = new PDO('mysql:host=localhost;dbname=access_control', 'root', '');
  2. $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  3. $dbh->exec('set names utf8');
  4. /*添加*/
  5. //$sql = "INSERT INTO `user` SET `login`=:login AND `password`=:password";
  6. $sql = "INSERT INTO `user` (`login` ,`password`)VALUES (:login, :password)"; $stmt = $dbh->prepare($sql); $stmt->execute(array(':login'=>'kevin2',':password'=>''));
  7. echo $dbh->lastinsertid();
  8. /*修改*/
  9. $sql = "UPDATE `user` SET `password`=:password WHERE `user_id`=:userId";
  10. $stmt = $dbh->prepare($sql);
  11. $stmt->execute(array(':userId'=>'7', ':password'=>'4607e782c4d86fd5364d7e4508bb10d9'));
  12. echo $stmt->rowCount();
  13. /*删除*/
  14. $sql = "DELETE FROM `user` WHERE `login` LIKE 'kevin_'"; //kevin%
  15. $stmt = $dbh->prepare($sql);
  16. $stmt->execute();
  17. echo $stmt->rowCount();
  18. /*查询*/
  19. $login = 'kevin%';
  20. $sql = "SELECT * FROM `user` WHERE `login` LIKE :login";
  21. $stmt = $dbh->prepare($sql);
  22. $stmt->execute(array(':login'=>$login));
  23. while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  24. print_r($row);
  25. }
  26. print_r( $stmt->fetchAll(PDO::FETCH_ASSOC));
  27. ?>
复制代码

1、建立连接

  1. $dbh=newPDO('mysql:host=localhost;port=3306; dbname=test',$user,$pass,array(
  2. PDO::ATTR_PERSISTENT=>true
  3. ));
  4. ?>
复制代码

持久性链接PDO::ATTR_PERSISTENT=>true

2,捕捉错误

  1. try{
  2. $dbh=newPDO('mysql:host=localhost;dbname=test',$user,$pass);
  3. $dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
  4. $dbh->exec("SET CHARACTER SET utf8");
  5. $dbh=null; //断开连接
  6. }catch(PDOException$e){
  7. print"Error!:".$e->getMessage()."
    ";
  8. die();
  9. }
  10. ?>
复制代码

3,pdo事务

  1. try{
  2. $dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
  3. $dbh->beginTransaction();//开启事务
  4. $dbh->exec("insertintostaff(id,first,last)values(23,'Joe','Bloggs')");
  5. $dbh->exec("insertintosalarychange(id,amount,changedate)
  6. values(23,50000,NOW())");
  7. $dbh->commit();//提交事务
  8. }catch(Exception$e){
  9. $dbh->rollBack();//错误回滚
  10. echo"Failed:".$e->getMessage();
  11. }
  12. ?>
复制代码

4. 错误处理 a. 静默模式(默认模式)

  1. try{
  2. $dbh = new PDO($dsn, $user, $password);
  3. $sql = 'Select * from city where CountryCode =:country';
  4. $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
  5. $stmt = $dbh->prepare($sql);
  6. $stmt->bindParam(':country', $country, PDO::PARAM_STR);
  7. $stmt->execute();
  8. while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  9. print $row['Name'] . "/t";
  10. }
  11. } // if there is a problem we can handle it here
  12. catch (PDOException $e) {
  13. echo 'PDO Exception Caught. ';
  14. echo 'Error with the database:
    ';
  15. echo 'SQL Query: ', $sql;
  16. echo 'Error: ' . $e->getMessage();
  17. }
  18. ?>
复制代码

1,使用 query()

  1. $dbh->query($sql); 当$sql 中变量可以用$dbh->quote($params); //转义字符串的数据
  2. $sql = 'Select * from city where CountryCode ='.$dbh->quote($country);
  3. foreach ($dbh->query($sql) as $row) {
  4. print $row['Name'] . "/t";
  5. print $row['CountryCode'] . "/t";
  6. print $row['Population'] . "/n";
  7. }
  8. ?>
复制代码

2,使用 prepare, bindParam和 execute [建议用,同时可以用添加、修改、删除]

  1. $dbh->prepare($sql); 产生了个PDOStatement对象
  2. PDOStatement->bindParam()
  3. PDOStatement->execute();//可以在这里放绑定的相应变量
  4. ?>
复制代码

3、php pdo事务例子

  1. try {
  2. $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', '');
  3. $dbh->query('set names utf8;');
  4. $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  5. $dbh->beginTransaction();
  6. $dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('mick', 22);");
  7. $dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('lily', 29);");
  8. $dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('susan', 21);");
  9. $dbh->commit();
  10. } catch (Exception $e) {
  11. $dbh->rollBack();
  12. echo "Failed: " . $e->getMessage();
  13. }
  14. ?>
复制代码