PHP后台技术-数据库对象技术PDO(一)
什么是PDO?
PDO一是PHP数据对象(PHP Data Object)的缩写。
extension=php_pdo_mysql.dll //MySQL扩展
extension=php_pdo_mssql.dll //SQL Server扩展
extension=php_pdo_oci.dll //Oracle扩展
2)(linux)
--width-pdo-mysql=/usr/local/mysql //其中/usr/local/mysql是目录(linux)
try{
$pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
}catch(PDOException $e){
die("数据库连接失败".$e->getMessage());
}
$stmt = $pdo->query($sql);
$sql = "insert into stu values(null,'oracle','w',44)";$sql = "delete from stu where id=11";
$sql = "insert into stu values(null,'oracle','w',44)";$sql = "delete from stu where id=11";$sql = "update stu set name='js' where id=3";$res = $pdo->exec($sql);
fetch() 是用来获取一条记录;
fetchAll() 是获取所有记录集到一个中,获取结果可以通过 PDOStatement::setFetchMode 来设置需要结果集合的类型;
fetchColumn() 是获取结果指定第一条记录的某个字段,缺省是第一个字段;
$list = $stmt->fetch();
(1)foreach语句$sql = "select * from stu";foreach($pdo->query($sql) as $val){
echo $val['id']."-----".$val['name']."<br>";
}
$stmt = $pdo->query($sql);
foreach($stmt->fetchAll() as $val){
echo $val['id']."-------".$val['name']."<br>";
}
$stmt = null;$pdo = null;
//默认是不提示的需要用 errorCode() errorInfo();try{$pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
//$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);//$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION );$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT);
}catch(PDOException $e){
die("数据库连接失败".$e->getMessage());
}$sql = "insert into stuu values(null,'jike','w',55)";try{
$res = $pdo->exec($sql);
}catch(PDOException $e){
echo $e->getMessage();
}//还有一种备用方法:
/* if($res){
echo "OK";}else{//echo $pdo->errorCode();//print_r($pdo->errorInfo());} */
1)?号式的预处理语句 一共有3种绑定方式//1.连接数据库try{
$pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
}catch(PDOException $e){
die("数据库连接失败".$e->getMessage());
}//2.预处理的SQL语句$sql = "insert into stu(id,name,sex,age) values(?,?,?,?)";$stmt = $pdo->prepare($sql);//3.对?号的参数绑定//(第一种绑定方式)/* $stmt->bindValue(1,null);$stmt->bindValue(2,'test55');$stmt->bindValue(3,'w');$stmt->bindValue(4,22); *///第二种绑定方式/* $stmt->bindParam(1,$id);$stmt->bindParam(2,$name);$stmt->bindParam(3,$sex);$stmt->bindParam(4,$age);$id=null;$name="test66";$sex="m";$age=33; *///第三种绑定方式//$stmt->execute(array(null,'test77','22',55));//4.执行$stmt->execute(array(null,'test77','22',55));echo $stmt->rowCount();
2)别名式号式的预处理语句 一共有3种绑定方式//1.连接数据库try{
$pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
}catch(PDOException $e){
die("数据库连接失败".$e->getMessage());
}//2.预处理的SQL语句$sql = "insert into stu(id,name,sex,age) values(:id,:name,:sex,:age)";$stmt = $pdo->prepare($sql);//3.对?号的参数绑定//(第一种绑定方式)/* $stmt->bindValue("id",null);$stmt->bindValue("name",'ceshi1');$stmt->bindValue("sex",'w');$stmt->bindValue("age",22); *///第二种绑定方式/* $stmt->bindParam("id",$id);$stmt->bindParam("name",$name);$stmt->bindParam("sex",$sex);$stmt->bindParam("age",$age);$id=null;$name="ceshi2";$sex="m";$age=33; *///第三种绑定方式//$stmt->execute(array(null,'test77','22',55));//4.执行$stmt->execute(array("id"=>null,"name"=>"ceshi3","sex"=>"w","age"=>66));echo $stmt->rowCount();
//1.连接数据库try{
$pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
}catch(PDOException $e){
die("数据库连接失败".$e->getMessage());
}//2.预处理的SQL语句$sql = "select id,name,sex,age from stu";$stmt = $pdo->prepare($sql);//3.执行$stmt->execute();$stmt->bindColumn(1,$id);$stmt->bindColumn(2,$name);$stmt->bindColumn("sex",$sex);$stmt->bindColumn("age",$age);while($row=$stmt->fetch(PDO::FETCH_COLUMN)){
echo "{$id}:{$name}:{$sex}:{$age}<br>";
}/* foreach($stmt as $row){
echo $row['id']."--------".$row['name']."<br>";
}*/
//1.连接数据库try{
$pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
die("数据库连接失败".$e->getMessage());
}//2.执行数据操作try{
//开启事物//$pdo->beginTransaction();$sql = "insert into stu(id,name,sex,age) values(?,?,?,?)";$stmt = $pdo->prepare($sql);//传入参数$stmt->execute(array(null,"test4","w",11));$stmt->execute(array(null,"test5","w",11));$stmt->execute(array(null,"test3","w",11));//提交事物//$pdo->commit();
}catch(PDOException $e){
die("执行失败".$e->getMessage());
//$pdo->roolback();
}
//采用预处理+事务处理执行SQL操作//1.连接数据库try{
$pdo = new PDO("mysql:host=localhost;dbname=jikexueyuan","root","");
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
die("数据库连接失败".$e->getMessage());
}//2.执行数据操作//开启事物$pdo->beginTransaction();$sql = "insert into stu(id,name,sex,age) values(?,?,?,?)";$stmt = $pdo->prepare($sql);$datalist = array(
array(null,"test9","w",11),array(null,"test10","w",11),array(null,"test11","w",11)
);$isCommit = true;foreach($datalist as $data){
$stmt->execute($data);if($stmt->errorCode()>0){
$pdo->roolback();$isCommit = false;break;
}
}if($isCommit){//提交事物$pdo->commit();}
PDO::CASE_NATURAL -- 列名按照原始的方式
PDO::CASE_UPPER -- 强制列名为大写
2 设置获取结果集的返回值的类型: $db- > setFetchMode (PDO::FETCH_ASSOC )
PDO::FETCH_ASSOC -- 关联数组形式
PDO::FETCH_NUM -- 数字索引数组形式
PDO::FETCH_BOTH -- 两者数组形式都有,这是缺省的
PDO::FETCH_OBJ -- 按照对象的形式,类似于以前的 mysql_fetch_object()
$pdo->exec("SET CHARACTER SET UTF8");//编码问题:解决插入中文数据乱码问题
<?
php
$
dbms
=
'mysql'
;
//
数据库类型 Oracle 用ODI, 对于开发者来说,使用不同的数据库,只要改这个,不用记住那么多的函数了
$
host
=
'localhost'
;
//
数据库主机名
$
dbName
=
'test'
;
//
使用的数据库
$
user
=
'root'
;
//
数据库连接用户名
$
pass
=
''
;
//
对应的密码
$
dsn
=
"$dbms:host=$host;dbname=$dbName"
;
//
try
{
$
dbh
=
new
PDO
(
$
dsn
,
$
user
,
$
pass
);
//
初始化一个PDO 对象,就是创建了数据库连接对象$dbh
echo
"
连接成功<br/>"
;
/*
你还可以进行一次搜 索操作
foreach
(
$
dbh
->
query
(
'SELECT * from FOO'
)
as
$
row
)
{
print_r
(
$
row
);
//
你可以用 echo($GLOBAL); 来看到这些值
}
*/
$
dbh
=
null
;
}
catch
(
PDOException
$
e
)
{
die
(
"Error!: "
.
$
e
->
getMessage
()
.
"<br/>"
);
}
//
默认这个不是长连 接,如果需要数据库长连接,需要最后加一个参数:array(PDO::ATTR_PERSISTENT => true) 变成这样:
$
db
=
new
PDO
(
$
dsn
,
$
user
,
$
pass
,
array
(
PDO
::
ATTR_PERSISTENT
=>
true
));
?>
|