php数据库pdo类

php

mysql｜postgresql

创建参数数组$db = array(    'dsn'=>'pgsql:host=localhost;port=5432;dbname=test',    'user'=>'root',    'password'=>'',    );注意 尽量用单引号。

pdo类内容 $sql     * @param $params     * @return $stmt     *     *     * $stmt = $dbh->prepare('INSERT INTO REGISTRY (name, value) VALUES (?, ?)');     * $stmt->bindParam(1, $name);     * $stmt->bindParam(2, $value);     * // insert one row     * $name = 'one';     * $value = 1;     * $stmt->execute();     *     * $sql = 'select * from SampleMarry where UserId=?';     * $arr[0] = $userId;     * return DAL::getall($sql,$arr);     */    private static function execute($sql,$params) {        try {            $stmt=self::$__PDO->prepare($sql);            if($params!==null) {                if(is_array($params) || is_object($params)) {                    $i=1;                    foreach($params as $param) {                        $stmt->bindValue($i++,$param);                    }                } else {                    $stmt->bindValue(1,$params);                }            }            if($stmt->execute()) {                self::$__queries++;                return $stmt;            } else {                $err=$stmt->errorInfo();                throw new PDOException($err[2],$err[1]);            }        } catch(PDOException $e) {            print_r($e);        }    }    /************************************************************************    public function start     ************************************************************************/    //for insert start    public static function insert($sql,$params=null) {        $stmt=self::execute($sql,$params);        return self::$__PDO->lastInsertId();    }    /**     *     * @param $table     * @param $entity     * @return     $data=new stdClass;    $data->SampleBook_Id=$bookid;    $data->UserId=$userId;    $data->EducationType='School';    $data->Name=$scoolname;    $data->Subject='';    $data->AttendedDate=strtotime($startdate);    $data->FinishedDate=strtotime($enddate);    return DAL::createRecord('SampleEducation',$item);     */    public static function createRecord($table,$entity) {        $f=array();        $v=array();        $c=0;        foreach($entity as $prop=>$val) {            $f[]=$prop;            $v[]=$val;            $c++;        }        $sql='INSERT INTO $table ('.implode(',',$f).') VALUES('.trim(str_repeat('?,',$c),',').')';        return self::insert($sql,$v);    }    //for insert end    //for delete start    /**     *     * @param $sql     * @param $params     * @return     $sql='delete from SampleLived where id=?';    $arr[] = $id;    DAL::delete($sql,$arr);     */    public static function delete($sql,$params=null) {        $stmt=self::execute($sql,$params);        return $stmt->rowCount();    }    public static function remove($sql,$params=null) {        $stmt=self::execute($sql,$params);        return $stmt->rowCount();    }    //for delete end    //for update start    public static function update($sql,$params=null) {        $stmt=self::execute($sql,$params);        return $stmt->rowCount();    }    public static function updateRecord($table,$entity,$pkdfiledname) {        $f=array();        $v=array();        foreach($entity as $prop=>$val) {            if($prop==$pkdfiledname)continue;            $f[]=$prop.'=?';            $v[]=$val;        }        $v[]=$entity->$pkdfiledname;        $sql='UPDATE $table SET '.trim(implode(',',$f),',').' WHERE $pkdfiledname=?';        return self::update($sql,$v);    }    //for update end    //for query start    public static function query($sql,$params=null) {        $stmt=self::execute($sql,$params);        return new DBRecordset($stmt);    }    public static function getOne($sql,$params=null) {        $stmt=self::execute($sql,$params);        return $stmt->fetchColumn();    }    public static function getRow($sql,$params=null) {        $stmt=self::execute($sql,$params);        return $stmt->fetch(PDO::FETCH_ASSOC);    }    public static function getAll($sql,$params=null) {        $stmt=self::execute($sql,$params);        return $stmt->fetchAll(PDO::FETCH_ASSOC);    }    //for query end    //for Transaction start    public static function beginTransaction() {        return self::$__PDO->beginTransaction();    }    public static function rollBack() {        return self::$__PDO->rollBack();    }    public static function commit() {        return self::$__PDO->commit();    }    //for Transaction end    public static function pagination($countSql,$selectSql,$params,&$pageNow,$pageSize,&$count) {        if($pageNow<=0)$pageNow=1;        $count=self::getOne($countSql,$params);        $pageCount=ceil($count/$pageSize);        if($pageNow>$pageCount)$pageNow=$pageCount;        if($pageNow<=0)$pageNow=1;        $offset=($pageNow-1)*$pageSize;        $sql=$selectSql.' LIMIT '.$offset.','.$pageSize;        return self::query($sql,$params);    }    /************************************************************************    public function end     ************************************************************************/}class DBRecordset {    private $PDOStatement;    public function __construct(&$stmt) {        $this->PDOStatement=&$stmt;        $this->PDOStatement->setFetchMode(PDO::FETCH_OBJ);    }    public function next() {        return $this->PDOStatement->fetch();    }    public function count() {        //for mysql PDOStatement will return the number of rows in the resultset        return $this->PDOStatement->rowCount();    }    public function getAllRows() {        return $this->PDOStatement->fetchAll();    }    public function columnCount() {        return $this->PDOStatement->columnCount();    }    public function free() {        $this->PDOStatement=null;    }    public function __destruct() {        $this->free();    }}?>

调用pdo类连接数据库include configinclude pdo$link = new DAL($db['dsn'],$db['user'],$db['password']);