php pdo公共类定义与用法实例分享

<?php
class db extends \PDO {
  private static $_instance = null;
  protected $dbName = &#39;&#39;;
  protected $dsn;
  protected $dbh;
  public function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset=&#39;utf8&#39;) {
    try {
      $this->dsn = &#39;mysql:host=&#39; . $dbHost . &#39;;dbname=&#39; . $dbName;
      $this->dbh = new \PDO($this->dsn, $dbUser, $dbPasswd);
      $this->dbh->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
      $this->dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
      $this->dbh->exec(&#39;SET character_set_connection=&#39;.$dbCharset.&#39;;SET character_set_client=&#39;.$dbCharset.&#39;;SET character_set_results=&#39;.$dbCharset);
    } catch (Exception $e) {
      $this->outputError($e->getMessage()); 
    }
  }
  public static function getInstance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset=&#39;utf8&#39;) {
    if (self::$_instance === null) {
      self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset);
    }
    return self::$_instance;
  }
  public function fetchAll($sql, $params = array()) {
    try {
      $stm = $this->dbh->prepare($sql);
      if ($stm && $stm->execute($params)) {
        return $stm->fetchAll(\PDO::FETCH_ASSOC);
      }
    } catch (Exception $e) {
      $this->outputError($e->getMessage());
    }
  }
  public function fetchOne($sql, $params = array()) {
    try {
      $result = false;
      $stm = $this->dbh->prepare($sql);
      if ($stm && $stm->execute($params)) {
        $result = $stm->fetch(\PDO::FETCH_ASSOC);
      }
      return $result;
    } catch (Exception $e) {
      $this->outputError($e->getMessage());
    }
  }
  public function fetchColumn($sql, $params = array()) {
    $result = &#39;&#39;;
    try {
      $stm = $this->dbh->prepare($sql);
      if ($stm && $stm->execute($params)) {
        $result = $stm->fetchColumn();
      }
      return $result;
    } catch (Exception $e) {
      $this->outputError($e->getMessage());
    }
  }
  public function insert($table, $params = array(), $returnLastId = true) {
    $_implode_field = &#39;&#39;;
    $fields = array_keys($params);
    $_implode_field = implode(&#39;,&#39;, $fields);
    $_implode_value = &#39;&#39;;
    foreach ($fields as $value) {
      $_implode_value .= &#39;:&#39;. $value.&#39;,&#39;;
    }
    $_implode_value = trim($_implode_value, &#39;,&#39;);
    $sql = &#39;INSERT INTO &#39; . $table . &#39;(&#39; . $_implode_field . &#39;) VALUES (&#39;.$_implode_value.&#39;)&#39;;
    try {
      $stm = $this->dbh->prepare($sql);
      $result = $stm->execute($params);
      if ( $returnLastId ) {
        $result = $this->dbh->lastInsertId();
      }
      return $result;
    } catch (Exception $e) {
      $this->outputError($e->getMessage());
    }
  }
  public function update($table, $params = array(), $where = null) {
    $_implode_field = &#39;&#39;;
    $_implode_field_arr = array();
    if ( empty($where) ) {
      return false;
    }
    $fields = array_keys($params);
    foreach ($fields as $key) {
      $_implode_field_arr[] = $key . &#39;=&#39; . &#39;:&#39;.$key;
    }
    $_implode_field = implode(&#39;,&#39;, $_implode_field_arr);
    $sql = &#39;UPDATE &#39; . $table . &#39; SET &#39; . $_implode_field . &#39; WHERE &#39; . $where;
    try {
      $stm = $this->dbh->prepare($sql);
      $result = $stm->execute($params);
      return $result;
    } catch (Exception $e) {
      $this->outputError($e->getMessage());
    }
  }
  public function delete($sql, $params = array()) {
    try {
      $stm = $this->dbh->prepare($sql);
      $result = $stm->execute($params);
      return $result;
    } catch (Exception $e) {
      $this->outputError($e->getMessage());
    }
  }
  public function exec($sql, $params = array()) {
    try {
      $stm = $this->dbh->prepare($sql);
      $result = $stm->execute($params);
      return $result;
    } catch (Exception $e) {
      $this->outputError($e->getMessage());
    }
  }
  private function outputError($strErrMsg) {
    throw new Exception("MySQL Error: " . $strErrMsg);
  }
  public function __destruct() {
    $this->dbh = null;
  }
}

<?php
require_once &#39;./db.class.php&#39;;
$pdo = db::getInstance(&#39;127.0.0.1&#39;, &#39;root&#39;, &#39;111111&#39;, &#39;php_cms&#39;);
$sql = "select id, title1 from cms_wz where id = :id limit 1";
$parame = array(&#39;id&#39; => 12,);
$res = $pdo->fetchOne($sql, $parame);
var_dump($res);
$sql = &#39;SELECT * FROM cms_link&#39;;
$result = $db->fetchAll($sql);
print_r($result);
//查询记录数量
$sql = &#39;SELECT COUNT(*) FROM cms_link&#39;;
$count = $db->fetchColumn($sql);
echo $count;
$data = array(
  &#39;siteid&#39; => 1,
  &#39;linktype&#39; => 1,
  &#39;name&#39; => &#39;google&#39;,
  &#39;url&#39; => &#39;http://www.google.com&#39;,
  &#39;listorder&#39; => 0,
  &#39;elite&#39; => 0,
  &#39;passed&#39; => 1,
  &#39;addtime&#39; => time()
  );
$lastInsertId = $db->insert(&#39;cms_link&#39;, $data);
echo $lastInsertId;
//用 try
 try {
     $result = $pdo->insert(&#39;news&#39;, $essay);
   } catch (Exception $e) {
     error_log($e->getMessage());
     error_log($e->getMessage() . &#39; in &#39; . __FILE__ . &#39; on line &#39; . __LINE__);
     saveLog(&#39;url文章 : &#39; . $essay[&#39;link&#39;] . &#39;  数据插入失败<br>&#39;);
     continue;
   }
$data = array(
  &#39;siteid&#39; => 1,
  &#39;linktype&#39; => 1,
  &#39;name&#39; => &#39;google&#39;,
  &#39;url&#39; => &#39;http://www.google.com&#39;,
  &#39;listorder&#39; => 0,
  &#39;elite&#39; => 0,
  &#39;passed&#39; => 1,
  &#39;addtime&#39; => time()
  );
$db->insert(&#39;cms_link&#39;, $data);
$sql = &#39;DELETE FROM cms_link WHERE linkid=4&#39;;
$result = $db->delete($sql);
var_dump($result);