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
)
)
*/