pdo 協助類 pdo class php 資料庫 處理層 pdo dbhelper

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