host = DB_HOST; $this->database = DB_DATABASE; $this->username = DB_USERNAME; $this->password = DB_PASSWORD; //設定 Options $options = array( PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8", //設定資料庫語系 PDO::ATTR_PERSISTENT => true, //資料庫長連接 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION //顯示異常 ); //例外處理 try { $this->dbh = new PDO("mysql:host=".$this->host.";dbname=".$this->database,$this->username,$this->password,$options); } catch (PDOException $e) { var_dump($e->getMessage()); } } } //取得單筆資料 function GetRow($sql) { $st = $this->dbh->prepare($sql); $st->execute(); return $st->fetch(); } //取得筆數 function GetNumRow($sql) { $st = $this->dbh->prepare($sql); $st->execute(); return $st->rowCount(); } //資料操作 function Execute($sql) { $sql = trim($sql); if ((strncasecmp('SELECT', $sql, 6) == 0) OR (strncasecmp('SHOW', $sql, 4) == 0)) { return $this->dbh->query($sql); } else { return $this->_Query($sql); } } //新增 function Insert($table, $arr = '') { $post = $this->GetPost(); if (!empty($arr)) { $post = array_merge($post, $arr); } return $this->Execute($this->GetInsertSQL($this->Select($table, $where = ''), $post)); } //處理新增的SQL字串 function GetInsertSQL($sql, $array) { $sql_k = array(); $sql_v = array(); $rs = $this->dbh->query($sql); for ($i = 0; $i < $rs->columnCount(); $i++) { $col = $rs->getColumnMeta($i); $name = $col['name']; $type = $col['pdo_type']; $len = $col['len']; $flags = $col['flags']; $table = $col['table']; if (isset($array[$name])) { $sql_k[] = "`" .$name. "`"; if (strncasecmp('date', $type, 4) == 0 OR strncasecmp('int', $type, 3) == 0) { $sql_v[] = $this->Qstr($array[$name]); } else { $sql_v[] = "'" .$array[$name]. "'"; } } } $count = count($sql_k); $sql_k_str = implode(',', $sql_k); $sql_v_str = implode(',', $sql_v); $sql = "INSERT INTO $table ($sql_k_str) VALUES ($sql_v_str)"; unset($sql_k,$sql_v); return $sql; } //更新 function Update($table, $where = '', $arr = '') { $post = $this->GetPost(); if (!empty($arr)) { $post = array_merge($post, $arr); } return $this->Execute($this->GetUpdateSQL($this->Select($table, $where), $post)); } //處理更新的SQL字串 function GetUpdateSQL($sql, $array) { $where = ''; $sql_upper = strtoupper($sql); $pos = strpos($sql_upper, " WHERE "); if ($pos !== false) { $sql_len = strlen($sql); $offset = $sql_len - $pos; if (false !== ($pos3 = strpos($sql_upper, " GROUP BY "))) { $offset = $pos3 - $pos; } elseif(false !== ($pos3 =strpos($sql_upper, " ORDER BY "))) { $offset = $pos3 - $pos; } $where = substr($sql, $pos, $offset); } $set_arr = array(); $rs = $this->dbh->query($sql); for ($i = 0; $i < $rs->columnCount(); $i++) { $col = $rs->getColumnMeta($i); $name = $col['name']; $type = $col['pdo_type']; $len = $col['len']; $flags = $col['flags']; $table = $col['table']; if(isset($array[$name])) { if(strncasecmp('date', $type, 4) == 0 || strncasecmp('int', $type, 3) == 0) { $v = $array[$name]; } else { $v = "\"" .$array[$name]. "\""; } $set_arr[] = " `$name` = $v"; } } if (!empty($set_arr)) { $set = ' SET '. implode(',', $set_arr); } else { $set = ' SET 1=1 '; } $sql = "UPDATE $table $set $where"; echo $sql; return $sql; } //刪除 function Delete($table, $where = '') { if ($where != '') { $where = " WHERE $where"; } return $this->Execute("DELETE FROM $table $where"); } //新增表格欄位 function AlterAdd($table, $tfield, $tmemo) { return $this->Execute("ALTER TABLE `$table` ADD `$tfield` $tmemo"); } //刪除表格欄位 function AlterDelete($table, $tfield) { return $this->Execute("ALTER TABLE `$table` DROP `$tfield`"); } //選取記錄並轉成陣列 function GetArray($sql) { $rs = $this->Execute($sql); $rows = array(); while ($row = $rs->fetch()) { $rows[] = $row; } return $rows; } //選取異動的單筆記錄 function Select($table, $where = '') { if ($where != '') { $prefix = substr(strtoupper(trim($where)), 0, 8); if (!in_array($prefix, array('GROUP BY','ORDER BY','WHERE 1'))) { $where = " WHERE $where"; } } if ($where != '') return "SELECT * FROM $table $where"; else return "SELECT * FROM $table limit 0,1"; } //取得新增的記錄編號 function Insert_ID() { return $this->dbh->lastInsertId(); } //執行sql字串 function _Query($sql) { $sql = $this->PreFix($sql); if (DB_DEBUG == 'Y') { echo "
\n";
			echo "$sql\n";
			echo "
\n"; } $st = $this->dbh->prepare($sql); return $st->execute(); } //字串處理 function Qstr($str) { $str = trim($str); //$str = addslashes($str); return $str; } //資料表前綴 function PreFix($sql) { return str_replace("#@#", DB_PREFIX,$sql); } //處理HTML碼的字串編碼 function HtmlEncode($str) { $ret = $this->Qstr($str); return htmlspecialchars($ret, ENT_QUOTES, $this->charset); //return $ret; } //取得POST元素, For GetInsertSQL, GetUpdateSQL function GetPost() { $num_args = func_num_args(); $args = func_get_args(); $post = array(); foreach ($_POST as $k => $v) { if (is_array($v)) { $tmp_arr = array(); foreach ($v as $v_k => $v_v) { $tmp_arr[$v_k] = $this->HtmlEncode($v_v); } $post[$k] = implode(',', $tmp_arr); continue; } if ($num_args > 0 && in_array($k, $args)) { $post[$k] = $this->Qstr($v); } else { $post[$k] = $this->HtmlEncode($v); } } return $post; } } ?>