php mysql连接方式pdo用法详解

$dbh = new PDO('mysql:host=localhost;dbname=access_control', 'root', '');$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$dbh->exec('set names utf8');/*添加*///$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();/*修改*/$sql = "UPDATE `user` SET `password`=:password WHERE `user_id`=:userId";$stmt = $dbh->prepare($sql);$stmt->execute(array(':userId'=>'7', ':password'=>'4607e782c4d86fd5364d7e4508bb10d9'));echo $stmt->rowCount();/*删除*/$sql = "DELETE FROM `user` WHERE `login` LIKE 'kevin_'"; //kevin%$stmt = $dbh->prepare($sql);$stmt->execute();echo $stmt->rowCount();/*查询*/$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、建立连接$dbh=newPDO('mysql:host=localhost;port=3306; dbname=test',$user,$pass,array(PDO::ATTR_PERSISTENT=>true));?>复制代码持久性链接pdo::attr_persistent=>true2，捕捉错误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; //断开连接}catch(PDOException$e){print"Error!:".$e->getMessage()."";die();}?>复制代码3，pdo事务try{$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);$dbh->beginTransaction();//开启事务$dbh->exec("insertintostaff(id,first,last)values(23,'Joe','Bloggs')");$dbh->exec("insertintosalarychange(id,amount,changedate)values(23,50000,NOW())");$dbh->commit();//提交事务}catch(Exception$e){$dbh->rollBack();//错误回滚echo"Failed:".$e->getMessage();}?>复制代码4. 错误处理a. 静默模式(默认模式)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 herecatch (PDOException $e) {echo 'PDO Exception Caught. ';echo 'Error with the database:';echo 'SQL Query: ', $sql;echo 'Error: ' . $e->getMessage();}?>复制代码1，使用 query()$dbh->query($sql); 当$sql 中变量可以用$dbh->quote($params); //转义字符串的数据$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，使用 prepare, bindParam和 execute [建议用,同时可以用添加、修改、删除]$dbh->prepare($sql); 产生了个PDOStatement对象PDOStatement->bindParam()PDOStatement->execute();//可以在这里放绑定的相应变量?>复制代码3、php pdo事务例子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();}?>复制代码