当前位置:Gxlcms > PHP教程 > phpmysql连接方式pdo用法详解

phpmysql连接方式pdo用法详解

时间:2021-07-01 10:21:17 帮助过:36人阅读

  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. ?>

人气教程排行