这篇文章主要介绍了php的pdo常用类库,结合实例形式分析了pdo类库常见的连接,初始化及增删改查等操作技巧,需要的朋友可以参考下
1、db.class.php 连接数据库
<?php
// 连接数据库
class db {
static public function getdb() {
try {
$pdo = new pdo(db_dsn, db_user, db_pwd);
$pdo->setattribute(pdo::attr_persistent, true); // 设置数据库连接为持久连接
$pdo->setattribute(pdo::attr_errmode, pdo::errmode_exception); // 设置抛出错误
$pdo->setattribute(pdo::attr_oracle_nulls, true); // 设置当字符串为空转换为 sql 的 null
$pdo->query('set names utf8'); // 设置数据库编码
} catch (pdoexception $e) {
exit('数据库连接错误，错误信息：'. $e->getmessage());
}
return $pdo;
}
}
?>
2、model.class.php 数据库操作类
<?php
/**
* 数据库操作类库
* author lee.
* last modify $date: 2012-1-19 13:59；04 $
*/
class m {
private $_db; //数据库句柄
public $_sql; //sql语句
/**
* 构造方法
*/
public function __construct() {
$this->_db = db::getdb();
}
/**
* 数据库添加操作
* @param string $tname 表名
* @param array $field 字段数组
* @param array $val 值数组
* @param bool $is_lastinsertid 是否返回添加id
* @return int 默认返回成功与否，$is_lastinsertid 为true，返回添加id
*/
public function insert($tname, $fields, $vals, $is_lastinsertid=false) {
try {
if (!is_array($fields) || !is_array($vals))
exit($this->geterror(__function__, __line__));
$fields = $this->formatarr($fields);
$vals = $this->formatarr($vals, false);
$tname = $this->formattabname($tname);
$this->_sql = "insert into {$tname} ({$fields}) values ({$vals})";
if (!$is_lastinsertid) {
$row = $this->_db->exec($this->_sql);
return $row;
} else {
$this->_db->exec($this->_sql);
$lastid = (int)$this->_db->lastinsertid();
return $lastid;
}
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 数据库修改操作
* @param string $tname 表名
* @param array $field 字段数组
* @param array $val 值数组
* @param string $condition 条件
* @return int 受影响的行数
*/
public function update($tname, $fieldval, $condition) {
try {
if (!is_array($fieldval) || !is_string($tname) || !is_string($condition))
exit($this->geterror(__function__, __line__));
$tname = $this->formattabname($tname);
$upstr = '';
foreach ($fieldval as $k=>$v) {
$upstr .= '`'.$k . '`=' . '\'' . $v . '\'' . ',';
}
$upstr = rtrim($upstr, ',');
$this->_sql = "update {$tname} set {$upstr} where {$condition}";
$row = $this->_db->exec($this->_sql);
return $row;
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 数据库删除操作（注：必须添加 where 条件）
* @param string $tname 表名
* @param string $condition 条件
* @return int 受影响的行数
*/
public function del($tname, $condition) {
try {
if (!is_string($tname) || !is_string($condition))
exit($this->geterror(__function__, __line__));
$tname= $this->formattabname($tname);
$this->_sql = "delete from {$tname} where {$condition}";
$row = $this->_db->exec($this->_sql);
return $row;
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 返回表总个数
* @param string $tname 表名
* @param string $condition 条件
* @return int
*/
public function total($tname, $condition='') {
try {
if (!is_string($tname))
exit($this->geterror(__function__, __line__));
$tname = $this->formattabname($tname);
$this->_sql = "select count(*) as total from {$tname}" .
($condition=='' ? '' : ' where ' . $condition);
$re = $this->_db->query($this->_sql);
foreach ($re as $v) {
$total = $v['total'];
}
return (int)$total;
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 数据库删除多条数据
* @param string $tname 表名
* @param string $field 依赖字段
* @param array $ids 删除数组
* @return int 受影响的行数
*/
public function delmulti($tname, $field, $ids) {
try {
if (!is_string($tname) || !is_array($ids))
exit($this->geterror(__function__, __line__));
$delstr = '';
$tname = $this->formattabname($tname);
$field = $this->formattabname($field);
foreach ($ids as $v) {
$delstr .= $v . ',';
}
$delstr = rtrim($delstr, ',');
$this->_sql = "delete from {$tname} where {$field} in ({$delstr})";
$row = $this->_db->exec($this->_sql);
return $row;
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 获取表格的最后主键（注：针对 int 类型）
* @param string $tname 表名
* @return int
*/
public function insertid($tname) {
try {
if (!is_string($tname))
exit($this->geterror(__function__, __line__));
$this->_sql = "show table status like '{$tname}'";
$result = $this->_db->query($this->_sql);
$insert_id = 0;
foreach ($result as $v) {
$insert_id = $v['auto_increment'];
}
return (int)$insert_id;
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 检查数据是否已经存在（依赖条件）
* @param string $tname 表名
* @param string $field 依赖的字段
* @return bool
*/
public function exists($tname, $condition) {
try {
if (!is_string($tname) || !is_string($condition))
exit($this->geterror(__function__, __line__));
$tname = $this->formattabname($tname);
$this->_sql = "select count(*) as total from {$tname} where {$condition}";
$result = $this->_db->query($this->_sql);
foreach ($result as $v) {
$b = $v['total'];
}
if ($b) {
return true;
} else {
return false;
}
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 检查数据是否已经存在（依赖 int 主键）
* @param string $tname 表名
* @param string $primary 主键
* @param int $id 主键值
* @return bool
*/
public function existsbypk($tname, $primary, $id) {
try {
if (!is_string($tname) || !is_string($primary)
|| !is_int($id))
exit($this->geterror(__function__, __line__));
$tname = $this->formattabname($tname);
$this->_sql = "select count(*) as total from {$tname} where {$primary} = ". $id;
$result = $this->_db->query($this->_sql);
foreach ($result as $v) {
$b = $v['total'];
}
if ($b) {
return true;
} else {
return false;
}
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 预处理删除（注：针对主键为 int 类型，推荐使用）
* @param string $tname 表名
* @param string $primary 主键字段
* @param int or array or string $ids 如果是删除一条为 int，多条为 array，删除一个范围为 string
* @return int 返回受影响的行数
*/
public function delbypk($tname, $primary, $ids, $mult=false) {
try {
if (!is_string($tname) || !is_string($primary)
|| (!is_int($ids) && !is_array($ids) && !is_string($ids))
|| !is_bool($mult)) exit($this->geterror(__function__, __line__));
$tname = $this->formattabname($tname);
$stmt = $this->_db->prepare("delete from {$tname} where {$primary}=?");
if (!$mult) {
$stmt->bindparam(1, $ids);
$row = $stmt->execute();
} else {
if (is_array($ids)) {
$row = 0;
foreach ($ids as $v) {
$stmt->bindparam(1, $v);
if ($stmt->execute()) {
$row++;
}
}
} elseif (is_string($ids)) {
if (!strpos($ids, '-'))
exit($this->geterror(__function__, __line__));
$split = explode('-', $ids);
if (count($split)!=2 || $split[0]>$split[1])
exit($this->geterror(__function__, __line__));
$i = null;
$count = $split[1]-$split[0]+1;
for ($i=0; $i<$count; $i++) {
$idarr[$i] = $split[0]++;
}
$idstr = '';
foreach ($idarr as $id) {
$idstr .= $id . ',';
}
$idstr = rtrim($idstr, ',');
$this->_sql ="delete from {$tname} where {$primary} in ({$idstr})";
$row = $this->_db->exec($this->_sql);
}
}
return $row;
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 返回单个字段数据或单条记录
* @param string $tname 表名
* @param string $condition 条件
* @param string or array $fields 返回的字段，默认是*
* @return string || array
*/
public function getrow($tname, $condition='', $fields="*") {
try {
if (!is_string($tname) || !is_string($condition)
|| !is_string($fields) || empty($fields))
exit($this->geterror(__function__, __line__));
$tname = $this->formattabname($tname);
$this->_sql = "select {$fields} from {$tname} ";
$this->_sql .= ($condition=='' ? '' : "where {$condition}") . " limit 1";
$sth = $this->_db->prepare($this->_sql);
$sth->execute();
$result = $sth->fetch(pdo::fetch_assoc);
if ($fields === '*') {
return $result;
} else {
return $result[$fields];
}
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 返回多条数据
* @param string $tname 表名
* @param string $fields 返回字段，默认为*
* @param string $condition 条件
* @param string $order 排序
* @param string $limit 显示个数
* @return pdostatement
*/
public function getall($tname, $fields='*', $condition='', $order='', $limit='') {
try {
if (!is_string($tname) || !is_string($fields)
|| !is_string($condition) || !is_string($order)
|| !is_string($limit))
exit($this->geterror(__function__, __line__));
$tname = $this->formattabname($tname);
$fields = ($fields=='*' || $fields=='') ? '*' : $fields;
$condition = $condition=='' ? '' : " where ". $condition ;
$order = $order=='' ? '' : " order by ". $order;
$limit = $limit=='' ? '' : " limit ". $limit;
$this->_sql = "select {$fields} from {$tname} {$condition} {$order} {$limit}";
$sth = $this->_db->prepare($this->_sql);
$sth->execute();
$result = $sth->fetchall(pdo::fetch_assoc);
return $result;
} catch (pdoexception $e) {
exit($e->getmessage());
}
}
/**
* 格式化数组（表结构和值）
* @param array $field
* @param bool $isfield
* @return string
*/
private function formatarr($field, $isfield=true) {
if (!is_array($field)) exit($this->geterror(__function__, __line__));
$fields = '';
if ($isfield) {
foreach ($field as $v) {
$fields .= '`'.$v.'`,';
}
} else {
foreach ($field as $v) {
$fields .= '\''.$v.'\''.',';
}
}
$fields = rtrim($fields, ',');
return $fields;
}
/**
* 格式化问号
* @param int $count 数量
* @return string 返回格式化后的字符串
*/
private function formatmark($count) {
$str = '';
if (!is_int($count)) exit($this->geterror(__function__, __line__));
if ($count==1) return '?';
for ($i=0; $i<$count; $i++) {
$str .= '?,';
}
return rtrim($str, ',');
}
/**
* 错误提示
* @param string $fun
* @return string
*/
private function geterror($fun, $line) {
return __class__ . '->' . $fun . '() line<font color="red">'. $line .'</font> error!';
}
/**
* 处理表名
* @param string $tname
* @return string
*/
private function formattabname($tname) {
return '`' . trim($tname, '`') . '`';
}
/**
* 析构方法
*/
public function __destruct() {
$this->_db = null;
}
}
总结：以上就是本篇文的全部内容，希望能对大家的学习有所帮助。
相关推荐：
php中文件上传后端处理的技巧
php书写格式详解及实例分析
php中file_get_contents函数抓取https地址出错的解决方法
以上就是php的pdo常用类库实例详解的详细内容。