PDO 对 mysql的基本操作
PDO扩展操作 <?php $dsn = 'mysql:dbname=yii2;host=localhost'; $user = 'root'; $password = '123456'; try { $dbh = new PDO($dsn,$user,$password,array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8")); }catch(PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); } //事务使用 - beginTransaction(),commit(),rollBack(),exec() /* // 增加了 new PDO() 中的最后参数 try { $dbh->beginTransaction(); $sqlDel = "delete from country where code = 'PK'"; $sqlIn = "insert into country(code,name,pop) values('TT','TEST', 9999)"; $dbh->exec($sqlDel); $dbh->exec($sqlIn); $dbh->commit(); }catch(PDOException $e) { echo "<br />error:<br />"; echo "<pre>"; print_r($e->getMessage()); $dbh->rollBack(); } */ // 事务使用 - setAttribute(),beginTransaction(),commit(),rollBack() /* // 设置错误模式,一定要设置,不然不会回滚与抛出异常,也可以在 new PDO()最后一个参数加这个值 $dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); try{ $dbh->beginTransaction(); $sqlDel = "delete from country where code = 'GX'"; $sqlIn = "insert into country(code,name,population) values('PK','good','4444444')"; $delFlag = $dbh->exec($sqlDel); $inFlag = $dbh->exec($sqlIn); var_dump($delFlag); var_dump($inFlag); echo ' commit '; var_dump($dbh->inTransaction()); // true $dbh->commit(); var_dump($dbh->lastInsertId()); echo ' commit222222 '; }catch(PDOException $e) { echo ' rollBack '; $dbh->rollBack(); echo $e->getMessage(); } $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT,1); */ // 删除 - exec() /* $sql = "delete from country where code = 'FK'"; $count = $dbh->exec($sql); var_dump($count); // int(1) int(0) */ //新增 - exec() /* $sql = "insert into country(code,name,population) values('FK','yes',13000)"; $count = $dbh->exec($sql); var_dump($count); // int(1) */ // 查询 - query() /* $sql = "select * from country where code ='AU'"; $res = $dbh->query($sql, PDO::FETCH_ASSOC);
if($res->rowCount() > 0)
{ foreach($res as $row) { echo "<pre>"; print_r($row); }
} Array ( [code] => AU [name] => Australia [population] => 18886000 ) */ // 查询 - fetchAll() /* $sql = "select * from country where code = :code"; $sth = $dbh->prepare($sql); $sth->execute(array(":code"=>"AU")); $res = $sth->fetchAll(PDO::FETCH_ASSOC); // 也可以用在 $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC),设置只关联数组 print_r($res); */ /* Array ( [0] => Array ( [code] => AU [0] => AU [name] => Australia [1] => Australia [population] => 18886000 [2] => 18886000 ) ) Array ( [0] => Array ( [code] => AU [name] => Australia [population] => 18886000 ) ) */ // PDOStatement 操作 <?php // http://php.net/manual/zh/pdostatement.execute.php $dsn = 'mysql:host=localhost;dbname=yii2'; $username = 'root'; $password = '123456'; try { $dbh = new PDO($dsn,$username,$password); }catch(PDOException $e) { echo "failure : "; echo $e->getMessage(); exit(); } echo "<pre>"; /* 打印一条SQL预处理命令 - debugDumpParams $name = 'GD'; $sql = "select * from country where name = :name"; $res = $dbh->prepare($sql); $res->bindValue(":name", $name); $res->execute(); $res->debugDumpParams(); $rr = $res->fetchAll(PDO::FETCH_ASSOC); print_r($rr); SQL: [40] select * from country where name = :name Params: 1 Key: Name: [5] :name paramno=-1 name=[5] ":name" is_param=1 param_type=2 */ /* 获取记录的列数 columnCount() $sql = "select * from country"; $res = $dbh->prepare($sql); $res->execute(); $rr = $res->columnCount(); print_r($rr); // 3 */ /* 返回受影响的行数 - rowCount(), prepare(),bindValue(),execute(), $code = 'PK'; $sql = "update country set name = 'GD' where code = :code"; $res = $dbh->prepare($sql); $res->bindValue(":code", $code); $res->execute(); $affectCount = $res->rowCount(); print_r($affectCount); // 1 */ /* 查询 - prepare(),bindValue(),fetchAll(),execute() $name = 'good'; $sql = "select count(1) as total from country where name = :name"; $res = $dbh->prepare($sql); $res->bindValue(":name",$name); $res->execute(); $rr = $res->fetchAll(PDO::FETCH_ASSOC); print_r($rr); Array ( [0] => Array ( [total] => 2 ) ) */ /* 查询 - bindValue(),execute(),fetchAll() $name = 'good'; $code = 'FK'; $sql = "select * from country where name = ? and code = ? limit 1"; $res = $dbh->prepare($sql); $res->bindValue(1,$name); $res->bindValue(2,$code); $res->execute(); $rr = $res->fetchAll(PDO::FETCH_ASSOC); print_r($rr); Array ( [0] => Array ( [code] => FK [name] => good [population] => 4444444 ) ) */ /* 查询 - prepare(),bindValue(),execute(),fetchAll() $name = "good"; $code = 'FK'; $sql = "select * from country where name = :name and code = :code limit 1"; $res = $dbh->prepare($sql); $res->bindValue(":code", $code); $res->bindValue(":name", $name); $res->execute(); $rr = $res->fetchAll(); print_r($rr); Array ( [0] => Array ( [code] => FK [0] => FK [name] => good [1] => good [population] => 4444444 [2] => 4444444 ) ) */ /* 查询 - prepare(),bindParam(),execute(),fetchAll() $name = 'good'; $code = 'PK'; $sql = "select * from country where name = ? and code = ?"; $res = $dbh->prepare($sql); $res->bindParam(1, $name); $res->bindParam(2, $code); $res->execute(); $rr = $res->fetchAll(PDO::FETCH_ASSOC); print_r($rr); Array ( [0] => Array ( [code] => PK [name] => good [population] => 4444444 ) ) */ // 查询 - prepare(),bindParam(),execute(),fetchAll() /* $name = 'good'; $code = 'PK'; $population = 4444444; $sql = "select * from country where name = :name and code = :code and population = :population"; $res = $dbh->prepare($sql); $res->bindParam(":code", $code); $res->bindParam(":name", $name,PDO::PARAM_STR); $res->bindParam(":population", $population); $res->execute(); $rr = $res->fetchAll(PDO::FETCH_ASSOC); print_r($rr); Array ( [0] => Array ( [code] => PK [name] => good [population] => 4444444 ) ) */ // 查询 - prepare(),execute(),fetch() /* $sql = "select * from country limit 2"; $res = $dbh->prepare($sql); $res->execute(); while($rs = $res->fetch(PDO::FETCH_ASSOC)) { print_r($rs); } Array ( [code] => AU [name] => Australia [population] => 18886000 ) Array ( [code] => BR [name] => Brazil [population] => 170115000 ) */ // 查询 - prepare(),execute(),fetchAll() /* $sql = "select * from country limit 1"; $res = $dbh->prepare($sql); $res->execute(); $rr = $res->fetchAll(); print_r($rr); Array ( [0] => Array ( [code] => AU [0] => AU [name] => Australia [1] => Australia [population] => 18886000 [2] => 18886000 ) ) */ // 查询 - prepare(),execute(),fetchAll() /** $sql = "select * from country limit 1"; $sth = $dbh->prepare($sql); $sth->execute(); $res = $sth->fetchAll(PDO::FETCH_ASSOC); echo "<pre>"; print_r($res); Array ( [0] => Array ( [code] => AU [name] => Australia [population] => 18886000 ) ) */