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

        )

)

*/