pdo 表单提交mysql_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 "
error:
";

echo "

";

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 "

";

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 操作

$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 "

";/*打印一条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 "

";

print_r($res);

Array

(

[0] => Array

(

[code] => AU

[name] => Australia

[population] => 18886000

)

)*/