Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Tutorial > Detailed explanation of php mysql connection method pdo usage

Detailed explanation of php mysql connection method pdo usage

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

1、建立连接

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

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

2，捕捉错误

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

3，pdo事务

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

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

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

1，使用 query()

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

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

  2. $dbh->prepare($sql); generates a PDOStatement object
  3. PDOStatement->bindParam()
  4. PDOStatement->execute();//You can put it here The corresponding bound variables
  5. ?>
Copy the code

3. PHP pdo transaction example

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


Related labels：
php mysql连接方式pdo用法详解
source：php.cn
Previous article：PHP string splitting function example learning Next article：PHP regular expression pattern matching example tutorial
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
How can I connect to a table and display its contents? (MySQL and PHP) I have two mysql tables (product and category). I have some mock data in both tables. Now ...
From 2024-04-03 09:55:31
0
1
350
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template