PHP Database Class โดยใช้ adodb And pdo singleton pattern

   $con = mysql_connect($myServer,$myUser,$myPass);  //รีเฟรชหน้าจอแต่ละครั้งจะได้การเชื่อมต่อฐานข้อมูลใหม่ทุกครั้ง
   mysql_select_db("my_db", $con);
   $con = mssql_connect($myServer, $myUser, $myPass); //เปลี่ยนชนิดฐานข้อมูลต้องแก้โค๊ดใหม่
   mssql_select_db("my_db", $con);

   DB::setDBConfig("adodb","mysql",$myServer,$myUser, $myPass, $myDatabaseName,"utf8");
   DB::setDBConfig("adodb","mssql",$myServer,$myUser, $myPass, $myDatabaseName,"utf8");

<?php
interface DatabaseModel{
	function openConnect();
	
	function closeConnect();
	
	function query($sql);
	
	/*For insert, update, delete return true/false*/
	function queryData($sql);
	
	/*For select return array*/
	function executeData($sql);
	
	/*For select one record return array*/
	function fetchOne($sql);
	
	function numRow($sql);
	
	function insertData($dbTable="",$fields=array());
	
	function updateData($dbTable="",$fields=array(),$where="");	
	
	function getInsertId();
	
	/*Not support pdo*/
	function selectLimit($sql, $perpage, $startrow);
}

<?php
class ADO_Model implements DatabaseModel{
	private $db;
	public $sql;
	public $rec_num=20;
	public $pageUrl='';
	public $read_table='';
	public $start_rec=0;
	public $end_rec =1;
	public $order_by='';
	public $page_limit=30;
	protected $driver="mysql";
	
	public function __construct($driver, $host, $user, $password, $dbname,$charset){
		$this->driver=$driver;
		$this->db = ADONewConnection($driver); 
		$this->db->Connect($host, $user, $password, $dbname) or die('No Select Database');
		$this->db->Execute('SET NAMES utf8');
		$this->db->SetFetchMode(ADODB_FETCH_ASSOC);		
	}
	
	public function openConnect(){
		return $this->db;		
	}
	
	public function closeConnect($conn=""){
		$conn=null;
		$this->db=null;
	}
	
	public function cleanText($txt=""){
		return addslashes(trim($txt));
	}
	
	public function convertText($txt=""){
		return iconv("windows-874", "UTF-8", $txt); 
	}
	
	public function query($sql){
		return  $this->db->Execute($sql);
	}
	
	public function fetchOne($sql){
		if($rs=$this->db->Execute($sql)){return $rs->fetchRow();}else {return false;}
	}
	
	public function queryData($sql){
		return $this->db->Execute($sql);
	}
	
	public function executeData($sql){
		return $this->db->Execute($sql);
	}
	
	public function numrowData($select_table=""){
		$rec=$this->db->Execute("select count(*) as total from $select_table");
		return (int)$rec->fields["total"];
	}
	
	public function numRow($sql){
		$rs=$this->db->Execute($sql);
		return (int)$rs->RecordCount();
	}
	
	public function getInsertId(){
		return $this->db->Insert_ID();	
	}
	
	public function insertData($dbTable="",$fields=array()){
		$arrFieldsName=array();
		$txtFieldsName="";
		$arrFieldsValue=array();
		$txtFieldsValue="";
		foreach($fields as $field_name=>$field_value){
			if(is_string($field_name)){
				$arrFieldsName[]=$field_name;
				$arrFieldsValue[]=$field_value;
			}
		}
		if(count($arrFieldsName)>0 && (count($arrFieldsName)==count($arrFieldsValue)) && $dbTable!=""){
			$txtFieldsName=implode(",",$arrFieldsName);
			$txtFieldsValue=implode(",",$arrFieldsValue);
			$sql="INSERT INTO $dbTable ( $txtFieldsName ) values( $txtFieldsValue )";
			unset($arrFieldsName,$arrFieldsValue,$fields);
			return $this->queryData($sql);
		}else{
			return false;
		}
	}
	
	public function updateData($dbTable="",$fields=array(),$where="1=1"){
		$arrUpdate=array();
		$txtUpdate="";
		foreach($fields as $field_name=>$field_value){
			if(!empty($field_name)){
				$arrUpdate[]=$field_name."=".$field_value;
			}
		}
		if(count($arrUpdate)>0 && $dbTable!=""){
			$txtUpdate=implode(",",$arrUpdate);
			$sql="UPDATE $dbTable SET $txtUpdate WHERE $where";
			unset($arrUpdate,$fields);
			return $this->queryData($sql);
		}else{
			return false;
		}	
	}
	
	
	public function getRows($sql){
		$res = $this->query($sql);
		if($res){
			$rows = $res->GetRows();
			return $rows;
		}else{
			return false;
		}
	}
	
	public function fetchRow($sql){
		$res = $this->query($sql);
		if($res){
			$row = $res->FetchRow();
			return $row;
		}else{
			return false;
		}
	}

	public function selectLimit($sql, $perpage, $startrow){
		return $this->db->SelectLimit($sql, $perpage, $startrow);
	}
	
	public function startTrans(){
		$this->db->StartTrans();
	}
	
	public function failedTrans(){
		return $this->db->FailTrans();
		#return $this->db->HasFailedTrans();
	}
	
	public function stopTrans(){
		$this->db->CompleteTrans();
	}
	
	protected function getJsPage($newPage=1,$txtJs=""){
		//Set javascript
		if(strpos($txtJs,"newPageX")){
			$js= str_replace("newPageX",$newPage,$txtJs);
			return $js;
		}elseif(strpos($js,"usePage")){
			$js=str_replace("usePage","",$txtJs);
			return $js;
		}else{return $txtJs;}
	}

	public function listPage( $total=1, $limit=20, $current_page=1, $baseurl='',$seoPage='' ,$jsClickFuncIncValue='',$jsClickFuncNameUpdateValue='',$jsParam='' ,$jsSentReturn=true,$arrCss=array()){
		#js onclick included param
		$jsClick="";
		if($jsSentReturn==false){$jsReturn="false;";}else{$jsReturn="true;";}
		if($jsClickFuncIncValue!=""){
			$js=" onclick=\"".$jsClickFuncIncValue."\"";
		#js onclick update new param
		}elseif($jsClickFuncNameUpdateValue!=""){
			#split more param Ex for other => int:0|string:xx|string:xx
			#split more param Ex for listPage => page:0|string:xx|string:xx   (page:0 => updateNewPage, page:1 or 2 or 3 ...=> use this page)
			if(strpos($jsParam,"|")){
				$arrJsParam=explode("|",$jsParam);
				#loop param => split param type,param value
				$jsAllValue="";
				$arrJsAllValue=array();
				foreach($arrJsParam as $key=>$v){
					list($paramType,$paramValue)=explode(":",$arrJsParam[$key]);
					if( $paramType=="page"){
						if($paramValue==0){
							$arrJsAllValue[]="newPageX";
						}elseif($paramValue>0){
							$arrJsAllValue[]="usePage".$paramValue;
						}else{
							$arrJsAllValue[]="''";
						}
					}elseif($paramType=="int" or $paramType=="float"){
						$arrJsAllValue[]=$paramValue;
					}elseif($paramType=="string"){
						$arrJsAllValue[]="'".$paramValue."'";
					}else{
						$arrJsAllValue[]="'".$paramValue."'";
					}
					
				}
				$jsAllValue=(count($arrJsAllValue)>0)?implode(",",$arrJsAllValue):"";
				$js=" onclick=\"".$jsClickFuncNameUpdateValue."(".$jsAllValue."); return ".$jsReturn."\"";
			#one param => split param type,param value
			}elseif(strpos($jsParam,":")){
				list($paramType,$paramValue)=explode(":",$jsParam);
				if($paramType=="page" && (int)$paramValue==0){
					$jsAllValue="newPageX";
				}elseif($paramType=="page" &&  (int)$paramValue>0){
					$jsAllValue="usePage".$paramValue;
				}else{
					$jsAllValue="''";
				}
				$js=" onclick=\"".$jsClickFuncNameUpdateValue."(".$jsAllValue.");return ".$jsReturn."\"";
			}else{$js="";}
		}else{$js="";}
		
		// how many page numbers to show in list at a time
		$showpages = "10"; // 1,3,5,7,9...
		// set up icons to be used
		$icon_path = 'icons/';
		$icon_param = 'align="middle" style="border:0px;" ';
		$icon_first= '[First page]';
		$icon_last= '[Lastpage]';
		$icon_previous= '<< Previous';
		$icon_next= 'Next >>';

		// do calculations
		$pages = ceil($total / $limit);
		$offset = ($current_page * $limit) - $limit;
		$end = $offset + $limit;

		//first
		if($js!=""){$jsClick=$this->getJsPage(1,$js);}
		$html .= "<a class='".$arrCss["first"]."' href='".$baseurl."1".$seoPage."'".$jsClick.">".$icon_first."</a> ";
		
		//previous
		$previous = ( $current_page>1)? $current_page - 1:1;
		if($js!=""){$jsClick=$this->getJsPage($previous,$js);}//get js onclick
		$html .= "<a class='".$arrCss["prev"]."' href='".$baseurl.$previous.$seoPage."'".$jsClick.">".$icon_previous."</a> | ";
		
		// print page numbers
		if ($pages>=1) { 
			$p=1;
			#$html .= "| Page: ";
			$pages_before =( $current_page>1)? $current_page - 1:1;
			$pages_after = $pages - $current_page;
			$show_before = floor($showpages / 2);
			$show_after = floor($showpages / 2);
			if ($pages_before < $show_before){
				$dif = $show_before - $pages_before;
				$show_after = $show_after + $dif;
			}
			if ($pages_after < $show_after){
				$dif = $show_after - $pages_after;
				$show_before = $show_before + $dif;
			}
			$minpage = $current_page - ($show_before+1);
			$maxpage = $current_page + ($show_after+1);

			if ($current_page > ($show_before+1) && $showpages > 0) {$html .= " ... ";}
			while ($p <= $pages) {
				if ($p > $minpage && $p < $maxpage) {
					//Set javascript
					if($js!=""){$jsClick=$this->getJsPage($p,$js);}//get js onclick
					if ($current_page == $p) {$html .= ' <a  class="'.$arrCss["active"].'" href="javascript:viod(0);">'.$p.'</a>';} else {$html .= ' <a  class="'.$arrCss["normal"].'" href="'.$baseurl.$p.$seoPage.'"'.$jsClick.'>'.$p.'</a>';}
				}
				$p++;
			}
			if ($maxpage-1 < $pages && $showpages > 0) {$html .= " ... ";}
		}
		// next
		$next = ($current_page== $pages)?$current_page:$current_page +1;
		#echo "next $next : p-1 =".($p-1);
		if($js!=""){$jsClick=$this->getJsPage($next,$js);}//get js onclick
		$html .= ' | <a class="'.$arrCss["next"].'" href="'.$baseurl.$next.$seoPage.'"'.$jsClick.'>'.$icon_next.'</a>';
		
		//  last
		$last = $p -1;
		if($js!=""){$jsClick=$this->getJsPage($last,$js);} //get js onclick
		$html .= ' <a class="'.$arrCss["last"].'" href="'.$baseurl.$last.$seoPage.'"'.$jsClick.'>'.$icon_last.'</a>';
		
		#$html .= '</div>';
		return $html;// return paging links
	}
}

<?php
class PDO_Model implements DatabaseModel{
	private $db;
	public $sql;
	public $rec_num=20;
	public $pageUrl='';
	public $read_table='';
	public $start_rec=0;
	public $end_rec =1;
	public $order_by='';
	public $page_limit=30;
	public $debug_query=false;
	protected $driver="mysql";
	
	public function __construct($driver, $host, $user, $password, $dbname,$charset){
		try {
				$dsn = $driver.":host=".$host.";dbname=".$dbname.";charset=".$charset;
				$this->driver=$driver;
				$this->db =  new PDO($dsn,$user,$password);
				$this->db->query("SET NAMES '".$charset."'");
				$this->db->query("SET character_set_results=".$charset);
				$this->db->query("SET character_set_client=".$charset);
				$this->db->query("SET character_set_connection=".$charset);
				return  $this->db;
    	} catch (PDOException $e) {
     		 print "Error!: " . $e->getMessage();
    	}	
	}	
	
	public function openConnect(){
		return $this->db;		
	}
	
	public function closeConnect($conn=""){
		$conn=null;
		$this->db=null;
	}
	
	public  function cleanText($txt=""){
		return addslashes(trim($txt));
	}
	
	public  function convertText($txt=""){
		return iconv("windows-874", "UTF-8", $txt); 
	}
	
	public function query($sql){
		$rs =$this->db->prepare($sql); 
  		return $rs->execute(); 
	}
	
	public function queryData($sql){
		try {
			$rs=$this->db->exec($sql);
			if($rs>0){unset($rs,$sql);return true;}else{unset($rs,$sql);return false;}
		}catch (PDOException $e) {echo $e->getMessage(); return false;}
	}
	
	public function fetchOne($sql){
		try {
			$rs =$this->db->prepare($sql); 
			$rs->execute(); 
			return $rs->fetch(PDO::FETCH_ASSOC);
		}catch (PDOException $e) {echo $e->getMessage(); return false;}
	}
	
	public function executeData($sql){
		try {
			$rs =$this->db->prepare($sql); 
  			$rs->execute(); 
			$rs->setFetchMode(PDO::FETCH_ASSOC);
			#$rs->fetchAll();
			return $rs->fetchAll();
		}catch (PDOException $e) {echo $e->getMessage(); return false;}
	}
	
	public function numrowData($select_table=""){
		try {
			$rs =$this->db->prepare("select count(*) as total from $select_table"); 
  			$rs->execute(); 
			$rec=$rs->fetch(PDO::FETCH_ASSOC); 
			return (int)$rec["total"];
		}catch (PDOException $e) {echo $e->getMessage(); return 0;}
	}
	
	public  function numRow($sql){
		try {
			$rs = $this->db->prepare($sql); 
  			$rs->execute(); 
			return (int)$rs->rowCount();
		}catch (PDOException $e) {echo $e->getMessage(); return false;}
	}
	
	public function getInsertId(){
		return $this->db->lastInsertId();
	}
	
	public function insertData($dbTable="",$fields=array()){
		$arrFieldsName=array();
		$txtFieldsName="";
		$arrFieldsValue=array();
		$txtFieldsValue="";
		
		foreach($fields as $field_name=>$field_value){
			if(!empty($field_name)){
				$arrFieldsName[]=$field_name;
				$arrFieldsValue[]=$field_value;
			}
		}
		
		if(count($arrFieldsName)>0 && (count($arrFieldsName)==count($arrFieldsValue)) && $dbTable!=""){
			$txtFieldsName=implode(",",$arrFieldsName);
			$txtFieldsValue=implode(",",$arrFieldsValue);
			$sql="INSERT INTO $dbTable ( $txtFieldsName ) values( $txtFieldsValue )";
			return $this->queryData($sql);
		}else{
			return false;
		}
	}
	
	public function updateData($dbTable="",$fields=array(),$where="1=1"){
		$arrUpdate=array();
		$txtUpdate="";
		foreach($fields as $field_name=>$field_value){
			if(!empty($field_name)){
				$arrUpdate[]=$field_name."=".$field_value;
			}
		}
		if(count($arrUpdate)>0 && $dbTable!=""){
			$txtUpdate=implode(",",$arrUpdate);
			$sql="UPDATE $dbTable SET $txtUpdate WHERE $where";
			return $this->queryData($sql);
		}else{
			return false;
		}	
		
	}
	
	public function getRows($sql){
		$res = $this->query($sql);
		if($res){
			$rows = $res->GetRows();
			return $rows;
		}else{
			return false;
		}
	}
	
	public function fetchRow($sql){
		try {
			$rs =$this->db->prepare($sql); 
			$rs->execute(); 
			return $rs->fetch(PDO::FETCH_ASSOC);
		}catch (PDOException $e) {echo $e->getMessage(); return false;}
	}

	public function selectLimit($sql, $perpage, $startrow){
	}
	
	public function startTrans(){
		$this->db->beginTransaction();
	}
	
	public function stopTrans(){
		$this->db->commit();
	}
	
	public function failedTrans(){
		return $this->db->rollBack();
	}
	
	protected function getJsPage($newPage=1,$txtJs=""){
		//Set javascript
		if(strpos($txtJs,"newPageX")){
			$js= str_replace("newPageX",$newPage,$txtJs);
			return $js;
		}elseif(strpos($js,"usePage")){
			$js=str_replace("usePage","",$txtJs);
			return $js;
		}else{return $txtJs;}
	}

	/* 
	Ex:
	js function 
	function listAdmin(page,focusId,perpage,mode){
		alert("Page:"+page+" select ID:"+focusId+" select perpage:"+perpage)
	}
	function test(){
		alert("test");
	}
	
	php use javascript onClick
	$arrCss=array("first"=>"css_class","prev"=>"css_class","normal"=>"css_class", "active"=>"css_class","next"=>"css_class","last"=>"css_class");
	$pageHtml=listPage(1,20,1,'www.xx.com/index-','.html','','listAdmin','page:0|int:0|int:10|string|search',false,$arrCss);
	#$pageHtml -> <a href="www.xx.com/index-1.html" onclick="listAdmin(1,0,10,'search');">1</a>
	or
	$pageHtml=listPage(1,20,1,'.html','','listAdmin','page:0|int:0|string|search');
	
	or
	$pageHtml=listPage(1,20,1,'.html','test','','');
	*/
	public function listPage( $total=1, $limit=20, $current_page=1, $baseurl='',$seoPage='' ,$jsClickFuncIncValue='',$jsClickFuncNameUpdateValue='',$jsParam='' ,$jsSentReturn=true,$arrCss=array()){
		#js onclick included param
		$jsClick="";
		if($jsSentReturn==false){$jsReturn="false;";}else{$jsReturn="true;";}
		if($jsClickFuncIncValue!=""){
			$js=" onclick=\"".$jsClickFuncIncValue."\"";
		#js onclick update new param
		}elseif($jsClickFuncNameUpdateValue!=""){
			#split more param Ex for other => int:0|string:xx|string:xx
			#split more param Ex for listPage => page:0|string:xx|string:xx   (page:0 => updateNewPage, page:1 or 2 or 3 ...=> use this page)
			if(strpos($jsParam,"|")){
				$arrJsParam=explode("|",$jsParam);
				#loop param => split param type,param value
				$jsAllValue="";
				$arrJsAllValue=array();
				foreach($arrJsParam as $key=>$v){
					list($paramType,$paramValue)=explode(":",$arrJsParam[$key]);
					if( $paramType=="page"){
						if($paramValue==0){
							$arrJsAllValue[]="newPageX";
						}elseif($paramValue>0){
							$arrJsAllValue[]="usePage".$paramValue;
						}else{
							$arrJsAllValue[]="''";
						}
					}elseif($paramType=="int" or $paramType=="float"){
						$arrJsAllValue[]=$paramValue;
					}elseif($paramType=="string"){
						$arrJsAllValue[]="'".$paramValue."'";
					}else{
						$arrJsAllValue[]="'".$paramValue."'";
					}
					
				}
				$jsAllValue=(count($arrJsAllValue)>0)?implode(",",$arrJsAllValue):"";
				#print_r($arrJsAllValue);
				$js=" onclick=\"".$jsClickFuncNameUpdateValue."(".$jsAllValue."); return ".$jsReturn."\"";
			#one param => split param type,param value
			}elseif(strpos($jsParam,":")){
				list($paramType,$paramValue)=explode(":",$jsParam);
				if($paramType=="page" && (int)$paramValue==0){
					$jsAllValue="newPageX";
				}elseif($paramType=="page" &&  (int)$paramValue>0){
					$jsAllValue="usePage".$paramValue;
				}else{
					$jsAllValue="''";
				}
				$js=" onclick=\"".$jsClickFuncNameUpdateValue."(".$jsAllValue.");return ".$jsReturn."\"";
			}else{$js="";}
		}else{$js="";}
		
		// how many page numbers to show in list at a time
		$showpages = "10"; // 1,3,5,7,9...
		// set up icons to be used
		$icon_path = 'icons/';
		$icon_param = 'align="middle" style="border:0px;" ';
		$icon_first= '[First page]';
		$icon_last= '[Lastpage]';
		$icon_previous= '<< Previous';
		$icon_next= 'Next >>';

		// do calculations
		$pages = ceil($total / $limit);
		$offset = ($current_page * $limit) - $limit;
		$end = $offset + $limit;

		//first
		if($js!=""){$jsClick=$this->getJsPage(1,$js);}
		$html .= "<a class='".$arrCss["first"]."' href='".$baseurl."1".$seoPage."'".$jsClick.">".$icon_first."</a> ";
		
		//previous
		$previous = ( $current_page>1)? $current_page - 1:1;
		if($js!=""){$jsClick=$this->getJsPage($previous,$js);}//get js onclick
		$html .= "<a class='".$arrCss["prev"]."' href='".$baseurl.$previous.$seoPage."'".$jsClick.">".$icon_previous."</a> | ";
		
		// print page numbers
		if ($pages>=1) { 
			$p=1;
			#$html .= "| Page: ";
			$pages_before =( $current_page>1)? $current_page - 1:1;
			$pages_after = $pages - $current_page;
			$show_before = floor($showpages / 2);
			$show_after = floor($showpages / 2);
			if ($pages_before < $show_before){
				$dif = $show_before - $pages_before;
				$show_after = $show_after + $dif;
			}
			if ($pages_after < $show_after){
				$dif = $show_after - $pages_after;
				$show_before = $show_before + $dif;
			}
			$minpage = $current_page - ($show_before+1);
			$maxpage = $current_page + ($show_after+1);

			if ($current_page > ($show_before+1) && $showpages > 0) {$html .= " ... ";}
			while ($p <= $pages) {
			if ($p > $minpage && $p < $maxpage) {
				//Set javascript
				if($js!=""){$jsClick=$this->getJsPage($p,$js);}
				if ($current_page == $p) {$html .= ' <a  class="'.$arrCss["active"].'" href="javascript:void(0);">'.$p.'</a>';} else {$html .= ' <a  class="'.$arrCss["normal"].'" href="'.$baseurl.$p.$seoPage.'"'.$jsClick.'>'.$p.'</a>';}
			}
			$p++;
			}
			if ($maxpage-1 < $pages && $showpages > 0) {$html .= " ... ";}
		}
		// next
		$next = ($current_page< $pages)?$current_page +1:$current_page;
		#echo "next $next : p-1 =".($p-1);
		if($js!=""){$jsClick=$this->getJsPage($next,$js);}
		$html .= ' | <a class="'.$arrCss["next"].'" href="'.$baseurl.$next.$seoPage.'"'.$jsClick.'>'.$icon_next.'</a>';
		
		//  last
		$last = $p -1;
		if($js!=""){$jsClick=$this->getJsPage($last,$js);} 
		$html .= ' <a class="'.$arrCss["last"].'" href="'.$baseurl.$last.$seoPage.'"'.$jsClick.'>'.$icon_last.'</a>';
		return $html;// return paging links
	}
}

<?php
class DB{
	protected static $_instance = NULL;
	
	protected static $_db_type;
	protected static $_db_driver;
	protected static $_db_host;
	protected static $_db_user;
	protected static $_db_password;
	protected static $_db_name;
	protected static $_db_charset;
	
	private function __construct(){}
	private function __clone(){}
	private function __wakeup(){}
	

	/**
	 * Set DB Configuration
	 * @params [$db_type, $db_driver, $db_host, $db_user, $db_password, $db_name]
	 * @return Void
     */
	public static function setDBConfig($db_type, $db_driver, $db_host, $db_user, $db_password, $db_name,$db_charset){
		self::$_db_type = $db_type;
		self::$_db_driver = $db_driver;
		self::$_db_host = $db_host;
		self::$_db_user = $db_user;
		self::$_db_password = $db_password;
		self::$_db_name = $db_name;
		self::$_db_charset = $db_charset;
	}


	/**
	 * A Factory method to provide DB instance.
	 * @return DB Object
     */
	public static function getInstance(){ 
		
		if (self::$_instance == NULL){ 
			if(self::$_db_type == 'adodb'){
				self::$_instance = new ADO_Model(self::$_db_driver, self::$_db_host, self::$_db_user, self::$_db_password, self::$_db_name,self::$_db_charset);
			}
			
			if(self::$_db_type == 'pdo'){
				self::$_instance = new PDO_Model(self::$_db_driver, self::$_db_host, self::$_db_user, self::$_db_password, self::$_db_name,self::$_db_charset);
			}
		}
		return self::$_instance;
	}
}

<?php
header("Content-Type:text/html; charset=utf-8");
include("adodb/adodb.inc.php");
include("db_model.class.php");
include("adodb_model_class.php");
include("pdo_model_class.php");
include("db_class.php");

/*
CREATE TABLE `products` (
  `product_id` int(11) NOT NULL auto_increment,
  `product_name_en` varchar(200) NOT NULL,
  `product_name_th` varchar(200) NOT NULL,
  `product_price` float NOT NULL,
  `product_update` datetime NOT NULL,
  PRIMARY KEY  (`product_id`),
  KEY `product_name` (`product_name_en`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`product_id`, `product_name_en`, `product_name_th`, `product_price`, `product_update`) VALUES
(2, 'Hyper  Shelves', 'Hyper  Shelves', 0, '2012-01-26 15:04:34'),
(1, 'Store Fixtures & Accessories', 'Store Fixtures & Accessories', 0, '2012-01-30 10:38:41'),
(3, 'POP & POS', 'POP & POS', 0, '2012-01-26 15:27:21'),
(4, 'Gondola & Display System', 'Gondola & Display System', 0, '2012-01-26 15:19:02'),
(5, 'Super Market Shelves', 'Super Market Shelves', 0, '2012-01-26 15:06:22'),
(6, 'Euro Cart Series', 'Euro Cart Series', 0, '2012-01-27 12:21:11'),
(7, 'Cyber Cart Series', 'Cyber Cart Series', 0, '2012-01-27 13:17:58'),
(8, 'Junior Cart Series', 'Junior Cart Series', 0, '2012-01-27 13:35:43'),
(9, 'Basket Cart', 'Basket Cart', 0, '2012-01-27 13:45:56'),
(10, 'Escalator Cart', 'Escalator Cart', 0, '2012-01-27 13:53:08'),
(11, 'Warehouse Trolley', 'Warehouse Trolley', 0, '2012-01-27 15:25:26'),
(12, 'Mezzanine Racking Systems', 'Mezzanine Racking Systems', 0, '2012-01-27 16:24:27'),
(13, 'Selective Racking Systems', 'Selective Racking Systems', 0, '2012-01-27 17:30:58'),
(14, 'Economy Shelves', 'Economy Shelves', 0, '2012-01-30 10:27:20'),
(15, 'test product 2', 'ทดสอบสินค้า2', 200, '2012-06-07 15:10:50'),
(16, 'Drive-in Racking Systems', 'Drive-in Racking Systems', 0, '2012-01-30 10:48:25'),
(17, 'Double Deep Racking System', 'Double Deep Racking System', 0, '2012-01-30 10:54:48'),
(18, 'Mobile Pallet racking System', 'Mobile Pallet racking System', 0, '2012-01-30 10:58:47'),
(19, 'Longspan Racking Systems', 'Longspan Racking Systems', 0, '2012-01-30 11:02:38'),
(20, 'Boltless Shelving Systems', 'Boltless Shelving Systems', 0, '2012-01-30 11:08:43'),
(21, '2-in-1 Boltless Shelving Systems', '2-in-1 Boltless Shelving Systems', 0, '2012-01-30 11:14:36');
*/

DB::setDBConfig("adodb","mysql","localhost","root","12345","test","utf8"); //เช็ตตัวแปรเชื่อมต่อฐานข้อมูล
$sql="select * from products";
$objRs=DB::getInstance()->executeData($sql);
$no=1;
#select all
echo "Select all<br>";
foreach($objRs as $k=>$rs){
	echo "No $no ) ID:".$rs["product_id"] ." | Name:".$rs["product_name_en"]."<br>";
	$no++;
}

#select one
$sql="select * from products where product_id=10";
$rs=DB::getInstance()->fetchOne($sql);
echo "<br><br>Select one<br>ID:".$rs["product_id"] ." | Name:".$rs["product_name_en"]."<br>";

#insert by sql
$sql="INSERT INTO products(product_id, product_name_en, product_name_th, product_price , product_update) values('', 'test product','ทดสอบสินค้า',100,now())";
#DB::getInstance()->queryData($sql);
#insert by array
$myarr=array(
	"product_name_en"=>"'test product 2'",
	"product_name_th"=>"'ทดสอบสินค้า2'",
	"product_price"=>200,
	"product_update"=>"now()"
);
#DB::getInstance()->insertData("products",$myarr);

#update
#DB::getInstance()->updateData("products",$myarr,"product_id=15");

#delete
#$sql="delete from product where product_id=23";
#DB::getInstance()->queryData($sql);
?>

final class Product{
	private $read_table="products";
	
	public function listProduct($page_no=1,$perpage=20,$id=0,$keyword=""){
		$page_no= ($page_no>0)?$page_no:1;
		$perpage=($perpage>0)?$perpage:20;
		$start_rec=($page_no-1)*$perpage;
		#Search
		$keyword=trim($keyword);
		if($keyword!=""){
			$where.=" and (product_id=".(int)$keyword." or product_name_en like'%".addslashes($keyword)."%' or product_name_th='%".addslashes($keyword)."%' ";
		}
		#page
		$sql="SELECT product_id from ".$this->read_table."  where 1=1 $where ";
		$all_rec=DB::getInstance()->numRow($sql);
		$order_by="order by product_id asc";
		
		#list data for pdo
		$sql="SELECT * from ".$this->read_table." $where ".$order_by." limit ".$start_rec.",".$perpage;
		$objRs=DB::getInstance()->executeData($sql);
		
		#list data for adodb
		#$sql="SELECT * from ".$this->read_table." $where ".$order_by;
		#$objRs=DB::getInstance()->selectLimit($sql,$perpage,$start_rec);
		$arrRs=array();
		$no=0;
		$num=$start_rec;
		foreach($objRs as $k=>$rs){
			$arrRs[$no]["product_id"]=$rs["product_id"];
			$arrRs[$no]["product_name_en"]=$rs["product_name_en"];
			$arrRs[$no]["product_name_th"]=$rs["product_name_th"];
			$arrRs[$no]["product_price"]=number_format($rs["product_price"],2);
			$arrRs[$no]["product_update"]=$rs["product_update"];
			$arrRs[$no]["no"]=$num+1;
			$arrRs[$no]["css"]=($no%2==0)?"":"css1";
			$no++;
			$num++;
		}
		$totalpage=round($all_rec/$perpage);
		$pageUrl=DB::getInstance()->listPage($all_rec, $perpage, $page_no, "index.php?p=","","","","".$perpage, false,$pageCss);
		return array("datas"=>$arrRs,"pages"=>$pageUrl,"totalpage"=>$totalpage);
	}
	
	/*
		method name addProduct
		param array $myArr=array(fieldName=>fieldValue)
		return boolean
	*/
	public function addProduct($myArr=array()){
		return DB::getInstance()->insertData($this->read_table,$myArr);
	}
	
	/*
		method name editProduct
		param array $myArr=array(fieldName=>fieldValue)
		param int $id
		return boolean
	*/
	public function editProduct($myArr=array(),$id=0){
		return DB::getInstance()->updateData($this->read_table,$myArr,"product_id=$id");
	} 

        /*
		method name getProduct
		param int $id
		return array
	*/
	public function getProduct($id=0){
                $sql="select * from ".$this->read_table." where product_id=".(int)$id;
		return DB::getInstance()->fetchOne( $sql);
	} 
	
	/*
		method name deleteProduct
		param int $id
		return boolean
	*/
	public function deleteProduct($id=0){
		$sql="delete from ".$this->read_table." where product_id=$id";
		return DB::getInstance()->queryData($sql);
	}
}
$product = new Product;  // สร้าง object สินค้า

if($_GET["mode"]=="edit" && $_GET["id"]>0){
   #Edit
   $appPro=$product->listProduct($p,10,$_GET["id"],'');
}else if($_GET["mode"]=="delete" && $_GET["id"]>0){
   #Delete
   $product->deleteProduct($_GET["id"]);
}else{
   //ลิสรายการสินค้า 10 รายการต่อหน้า
   $p=($_GET["p"]>0)?$_GET["p"]:1;
   $appPro=$product->listProduct($p,10,'','');
}
?>
<style type="text/css">
body{ font-family:Tahoma, Geneva, sans-serif; font-size:14px;}
a{ color:#000;}
table.table{ border:solid 1px; font-family:Tahoma, Geneva, sans-serif; font-size:14px;}
table.table thead tr td{ text-align:center; font-weight:bold; background:#000000; color:#fff; padding:6px;}
table.table td a{ text-decoration:none;}
table.table tbody tr td { padding:4px;}
table.table tbody tr.css1 td{ background:#EBEBEB;}
table.table tbody tr:hover td{ background:#C9C9C9;}
</style>
<br /><br />
<table width="810" border="0" class="table">
	<thead>
  <tr>
    <th colspan="6" align="left"><h3>List all product</h3></th>
  </tr>
  <tr>
    <td width="63">No</td>
    <td width="195">Product name (TH)</td>
    <td width="192">Product name (EN)</td>
    <td width="213">Update date</td>
    <td width="123">Action</td>
  </tr>
  </thead>
  <tbody>
  <?php
  foreach($appPro["datas"] as $k=>$rs){
  ?>
  <tr class="<?php echo $rs["css"];?>">
    <td align="center"><?php echo $rs["no"];?></td>
    <td><?php echo $rs["product_name_th"];?></td>
    <td><?php echo $rs["product_name_en"];?></td>
    <td><?php echo $rs["product_update"];?></td>
    <td align="center">
    <a href="index.php?p=<?php echo $p;?>&mode=edit&id=<?php echo $rs["product_id"];?>">Edit</a> | 
    <a href="index.php?p=<?php echo $p;?>&mode=delete&id=<?php echo $rs["product_id"];?>">Delete</a>
    </td>
  </tr>
  <?php
  }
  ?>
  </tbody>
  <tfoot>
  <tr>
    <td align="center">page</td>
    <td colspan="5" align="right"><?php echo $appPro["pages"]?></td>
    </tr>
  </tfoot>
</table>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
body{ font-family:Tahoma, Geneva, sans-serif; font-size:14px;}
a{ color:#000;}
/*Grid table*/
table.table{ border:solid 1px; font-family:Tahoma, Geneva, sans-serif; font-size:14px;}
table.table thead tr td{ text-align:center; font-weight:bold; background:#000000; color:#fff; padding:6px;}
table.table td a{ text-decoration:none;}
table.table tbody tr td { padding:4px;}
table.table tbody tr.css1 td{ background:#EBEBEB;}
table.table tbody tr:hover td{ background:#C9C9C9;}

/*Page*/
.firstPage{ display:inline-block;text-indent:-5000px;height: 24px;width: 24px; line-height:24px;text-align:center; background:url(icons/paging_far_left.gif) no-repeat left center;}
.prevPage{ display:inline-block;text-indent:-5000px;height: 24px;width: 24px; line-height:24px;text-align:center; background:url(icons/paging_left.gif) no-repeat left center;}
.nextPage{ display:inline-block;text-indent:-5000px;height: 24px;width: 24px; line-height:24px;text-align:center; background:url(icons/paging_right.gif) no-repeat left center;}
.lastPage{ display:inline-block;text-indent:-5000px;height: 24px;width: 24px; line-height:24px;text-align:center; background:url(icons/paging_far_right.gif) no-repeat left center;}
.normalPage{display:inline-block;height: 24px;width: 24px; line-height:24px;text-align:center; }
.activePage{display:inline-block;height: 24px;width: 24px; line-height:24px;text-align:center; text-decoration:underline; font-weight:bold; }
</style>
<script type="text/javascript" src="js/jquery-1.4.1.min.js"></script>
<script type="text/javascript">
//บันทึกข้อมูลสินค้า p= หน้า , id =รหัสสินค้าที่ต้องการบันทึก
function saveProduct(p,id){
	if(confirm("บันทึกข้อมูล?")){
		$.ajax({
			type: "GET",
			url: 'ajax_data.php',
			data:'action=save-product&p='+p+'&id='+id+'&'+$("#frm-product").serialize(),
			cache: false,
			dataType: "text/html",
			beforeSend:function(){$('#showContect').html("<img src='icons/loadings.gif'>");},
			success: function(data) {
				if($.trim(data)=="true"){
					listProduct(p,'','');
				}else{
					alert("บันทึกไม่สำเร็จ id: "+id);
				}
			}
		});
	}
	return false;
}

//ลิสข้อมูลสินค้า p= หน้า , perpage=จำนวนรายการต่อหน้า , id= หรัสสินค้าที่่ต้องการแก้ไข
//* ถ้า id ไม่เท่ากับค่าว่างให้เป็นโหมดแก้ไขรายการสินค้า รหัสดังกล่าว แต่ถ้า id เป็นค่าว่าง ให้เป็นโหมดลิสรายการสินค้าปกติ
function listProduct(p,perpage,id){
	$.ajax({
		type: "GET",
	  	url: 'ajax_data.php',
		data:'action=list-product&p='+p+'&pergage='+perpage+'&id='+id,
		cache: false,
		dataType: "text/html",
		beforeSend:function(){$('#showContect').html("<img src='icons/loadings.gif'>");},
	  	success: function(data) {
			$('#showContect').html(data);;
	  	}
	});
	return false;
}

//ลบรายการสินค้า p=หน้า , id=รหัสสินค้าที่ต้องการลบ
function deleteProduct(p,id){
	if(confirm("ยืนยันการลบข้อมูล?")){
	$.ajax({
		type: "GET",
	  	url: 'ajax_data.php',
		data:'action=delete-product&p='+p+'&id='+id,
		cache: false,async:false,
		dataType: "text/html",
		beforeSend:function(){$('#showContect').html("<img src='icons/loadings.gif'>");},
	  	success: function(data) {
			if($.trim(data)=="true"){
				listProduct(p,'','');
			}else{
				alert("Delete failed id: "+id);
			}
	  	}
	});
	}
	return false;
}
$(function(){
	listProduct(1,10,'');
});
</script>

<title>test ajax</title>
</head>
<body>
<div id="showContect"></div>
</body>
</html>

<?php
header("Content-Type:text/html; charset=utf-8");
include("adodb/adodb.inc.php");
include("db_model.class.php");
include("adodb_model_class.php");
include("pdo_model_class.php");
include("db_class.php");
$pageCss=array("first"=>"firstPage","prev"=>"prevPage", "normal"=>"normalPage","active"=>"activePage","next"=>"nextPage","last"=>"lastPage");
final class Product{
	private $read_table="products";
	
	/*
		method name listProduct
		@param int $page_no 
		@param int $perpage
		@param int $id
		@param mixed $keyword
		return array
	*/
	public function listProduct($page_no=1,$perpage=20,$id=0,$keyword=""){
		global $pageCss;
		$page_no= ($page_no>0)?$page_no:1;
		$perpage=($perpage>0)?$perpage:20;
		$start_rec=($page_no-1)*$perpage;
		#Search
		$keyword=trim($keyword);
		if($keyword!=""){
			$where.=" and (product_id=".(int)$keyword." or product_name_en like'%".addslashes($keyword)."%' or product_name_th='%".addslashes($keyword)."%' ";
		}
		#page
		$sql="SELECT product_id from ".$this->read_table."  where 1=1 $where ";
		$all_rec=DB::getInstance()->numRow($sql);
		$order_by="order by product_id asc";
		
		#list data for pdo
		$sql="SELECT * from ".$this->read_table." $where ".$order_by." limit ".$start_rec.",".$perpage;
		$objRs=DB::getInstance()->executeData($sql);
		
		#list data for adodb
		#$sql="SELECT * from ".$this->read_table." $where ".$order_by;
		#$objRs=DB::getInstance()->selectLimit($sql,$perpage,$start_rec);
		$arrRs=array();
		$no=0;
		$num=$start_rec;
		foreach($objRs as $k=>$rs){
			$arrRs[$no]["product_id"]=$rs["product_id"];
			$arrRs[$no]["product_name_en"]=$rs["product_name_en"];
			$arrRs[$no]["product_name_th"]=$rs["product_name_th"];
			$arrRs[$no]["product_price"]=number_format($rs["product_price"],2);
			$arrRs[$no]["product_update"]=$rs["product_update"];
			$arrRs[$no]["no"]=$num+1;
			$arrRs[$no]["css"]=($no%2==0)?"":"css1";
			$arrRs[$no]["edit"]=($rs["product_id"]==$id)?true:false;
			$no++;
			$num++;
		}
		$totalpage=round($all_rec/$perpage);
		$pageUrl=DB::getInstance()->listPage($all_rec, $perpage, $page_no, "index.php?p=","","","listProduct","page:0|int:".$perpage."|int:0", false,$pageCss);
		return array("datas"=>$arrRs,"pages"=>$pageUrl,"totalpage"=>$totalpage);
	}
	
	/*
		method name addProduct
		@param array $myArr=array(fieldName=>fieldValue)
		return boolean
	*/
	public function addProduct($myArr=array()){
		return DB::getInstance()->insertData($this->read_table,$myArr);
	}
	
	/*
		method name editProduct
		@param array $myArr=array(fieldName=>fieldValue)
		@param int $id
		return boolean
	*/
	public function editProduct($myArr=array(),$id=0){
		return DB::getInstance()->updateData($this->read_table,$myArr,"product_id=$id");
	} 
	
	/*
		method name deleteProduct
		@param int $id
		return boolean
	*/
	public function deleteProduct($id=0){
		$sql="delete from ".$this->read_table." where product_id=$id";
		return DB::getInstance()->queryData($sql);
	}
}
DB::setDBConfig("pdo","mysql","localhost","root","0chineji0","test","utf8");
$product=new Product;
if($_GET["action"]=="list-product"){
	$p=($_GET["p"]>0)?$_GET["p"]:1;
	$perpage=($_GET["perpage"]>0)?$_GET["perpage"]:10;
	$id=(int)$_GET["id"];
	$appPro=$product->listProduct($p,$perpage,$id,'');
	?>
    <form name="frm-product" id="frm-product">
    <table width="810" border="0" class="table">
	<thead>
  <tr>
    <th colspan="6" align="left"><h3>List all product</h3></th>
  </tr>
  <tr>
    <td width="63">No</td>
    <td width="195">Product name (TH)</td>
    <td width="192">Product name (EN)</td>
    <td width="213">Update date</td>
    <td width="123">Action</td>
  </tr>
  </thead>
  <tbody>
  <?php
  foreach($appPro["datas"] as $rs){
	if($rs["product_id"]==$id){
		?>
    <tr class="<?php echo $rs["css"];?>">
    <td align="center"><?php echo $rs["no"];?></td>
    <td><input type="text" name="product_name_th" id="product_name_th" value="<?php echo $rs["product_name_th"];?>"></td>
    <td><input type="text" name="product_name_en" id="product_name_en" value="<?php echo $rs["product_name_en"];?>"></td>
    <td><?php echo $rs["product_update"];?></td>
    <td align="center">
    <a href="javascript:void(0);" onClick="saveProduct('<?php echo $p;?>','<?php echo $rs["product_id"];?>')">Save</a> | 
    <a href="javascript:void(0);" onClick="listProduct('<?php echo $p;?>','','');">Cancle</a>
    </td>
  </tr>
        <?php
	}else{
  ?>
  <tr class="<?php echo $rs["css"];?>">
    <td align="center"><?php echo $rs["no"];?></td>
    <td><?php echo $rs["product_name_th"];?></td>
    <td><?php echo $rs["product_name_en"];?></td>
    <td><?php echo $rs["product_update"];?></td>
    <td align="center">
    <a href="javascript:void(0);" onClick="listProduct('<?php echo $p;?>',10,'<?php echo $rs["product_id"];?>');">Edit</a> | 
    <a href="javascript:void(0);" onClick="deleteProduct('<?php echo $p;?>','<?php echo $rs["product_id"];?>');">Delete</a>
    </td>
  </tr>
  <?php
	}
  }
  ?>
  </tbody>
  <tfoot>
  <tr>
    <td align="center">page</td>
    <td colspan="5" align="right"><?php echo $appPro["pages"]?></td>
    </tr>
  </tfoot>
</table>
</form>
    <?php
}elseif($_GET["action"]=="save-product" && $_GET["id"]>0){
	$myarr=array(
	"product_name_en"=>"'".$_GET["product_name_en"]."'",
	"product_name_th"=>"'".$_GET["product_name_th"]."'",
	"product_price"=>200,
	"product_update"=>"now()"
);
	$ss=$product->editProduct($myarr,$_GET["id"]);
	if($ss){echo"true";}else{echo "false";}
}elseif($_GET["action"]=="delete-product" && $_GET["id"]>0){
	$ss=$product->deleteProduct($_GET["id"]);
	if($ss){echo"true";}else{echo "false";}
}else{
	echo "55";
}
?>