欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

PHP的PDO常用类库实例分析

程序员文章站 2024-04-02 08:42:46
本文实例讲述了php的pdo常用类库。分享给大家供大家参考,具体如下: 1、db.class.php 连接数据库

本文实例讲述了php的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 curl用法总结》、《php运算与运算符用法总结》、《php网络编程技巧总结》、《php基本语法入门教程》、《php操作office文档技巧总结(包括word,excel,access,ppt)》、《php日期与时间用法总结》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总

希望本文所述对大家php程序设计有所帮助。