PDO 對 mysql的基本操作

標籤：ctc   pass   ams   lag   dos   cat   country   logs   comm   

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));}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);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 = :nameParams:  1Key: Name: [5] :nameparamno=-1name=[5] ":name"is_param=1param_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        ))*/

 

PDO 對 mysql的基本操作