本文共 3633 字,大约阅读时间需要 12 分钟。
1,数据库连接
<?php
$dsn = ‘mysql:dbname=test1;host=localhost;port=3306′;
$user=”dbadmin”;
$pwd=”*****”;
$dbh = new PDO($dsn, $user, $pwd);
?>
2,数据查寻
a)用query
<?php
$sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
foreach ($conn->query($sql) as $row) { print $row['name'] . "/t"; print $row['color'] . "/t"; print $row['calories'] . "/n"; }
?>
b)用prepare
<?php
$sqlcheck = “select attribute_id from eav_attribute ” .
” where eav_attribute.attribute_code=: attribute_code ” . ” and eav_attribute.entity_type_id=:entity_type_id”; //sql语句中的attribute_code对应execute数组中的KEY $sthcheck = $dbh1->prepare($sqlcheck); $sthcheck->execute(array(‘attribute_code ‘=>$tmp['attribute_code'],’entity_type_id’=>$tmp['entity_type_id'])); $col = $sthcheck->fetch(PDO::FETCH_ASSOC);?>
c)对比一下b)中sql
<?php
$sqlcheck = “select attribute_id from eav_attribute ” .
” where eav_attribute.attribute_code=? ” . ” and eav_attribute.entity_type_id=?”; //sql语句中的attribute_code对应execute数组中的KEY $sthcheck = $dbh1->prepare($sqlcheck); $sthcheck->execute(array($tmp['attribute_code'],$tmp['entity_type_id'])); $col = $sthcheck->fetch(PDO::FETCH_ASSOC);?>
3,数据更新/插入
a)
$sql_update = “update catalog_eav_attribute set attribute_id = ? where attribute_id = ? “;
$sthupdate = $dbh1->prepare($sql_update); $aaa=$sthupdate->execute(array($col['attribute_id'],$tmp['attribute_id']));b)
$sql_update = “update catalog_eav_attribute set attribute_id = :aaa where attribute_id = :bbb “;
$sthupdate = $dbh1->prepare($sql_update); $aaa=$sthupdate->execute(array(“aaa “=>$col['attribute_id'],”bbb”=>$tmp['attribute_id']));c)
$sql_update = “update catalog_eav_attribute set attribute_id = “.$col['attribute_id'].” where attribute_id = “.$tmp['attribute_id'].” “;
$sthupdate = $dbh1->exec($sql_update);这里以update为例,插入的话,把sql变一下就行了。
4,数据删除
$count = $dbh->exec("DELETE FROM
catalog_eav_attribute WHERE
attribute_id = 23");
5,取得刚插入数据的ID
function pgsqlLastInsertId($sqlQuery, $pdoObject)
{ // Checks if query is an insert and gets table name if( preg_match("/^INSERT[/t/n ]+INTO[/t/n ]+([a-z0-9/_/-]+)/is", $sqlQuery, $tablename) ) { // Gets this table's last sequence value $query = "SELECT currval('" . $tablename[1] . "_id_seq') AS last_value";
$temp_q_id = $pdoObject->prepare($query);
$temp_q_id->execute();if($temp_q_id)
{ $temp_result = $temp_q_id->fetch(PDO::FETCH_ASSOC); return ( $temp_result ) ? $temp_result['last_value'] : false; } }return false;
}$sql = 'INSERT INTO table (column) VALUES (/'some_value/');';
$result = $pdoObject->prepare($sql); $result->execute();
echo 'Last Insert ID: ' . pgsqlLastInsertId($sql, $pdoObject);
6,查看报错信息
$stmt = $dbh->prepare($sql);
if (!$stmt) { echo "/nPDO::errorInfo():/n"; print_r($dbh->errorInfo()); }
还有好多,只举了几个常用的例子,里面的东西还是要自己去实践的。
四,用pdo有什么优点,个人一些看法
1,它能接好多数据库,现有的数据库绝大多数都可以用PDO来连,并且是统一的接口
2,用pdo来封装sql语句很方便。从开发者的工作效率来说会很高,prepare的sql语句当中可以不含有变量,这个挺爽的
3,用pdo封装sql后,对于做memcache也方便了好多,比如直接拿hash过的sql做key。最直接,最土的方法。
[]
转载地址:http://eyadi.baihongyu.com/