20여개의 mysql 관련 클래스중에서 필요한 것만 모아서 짜집기한 클래스…
일반적인 mysql 클래스에 리스트와 페이징까지 한번에 처리하는 함수와 xml로 결과를 출력하는
함수 추가.
클래스내용 >>
[code]
//DB클래스
class dbH {
// PRIVATE 변수
private $CONN; // DB 커넥트
private $ERR_QUERY; // 에러가 발생한 쿼리
private $ERR_MSG; // 에러 메세지
private $RESULT; // 쿼리 결과
private $REC_COUNT; // 레코드 합계
private $ROW; // 레코드 결과 FETCH
private $ROWNUM; // 현재 레코드 번호
// 클래스 초기화
function dbH() {
$DB_HOST = “localhost”;
$DB_USER = “new_chong”;
$DB_PW = “new3432”;
$DB_NAME = “new_chong”;
$this->CONN = mysql_connect($DB_HOST, $DB_USER, $DB_PW) or die(“데이터베이트 연결 실패! 환경설정을 확인해주세요.”);
mysql_select_db($DB_NAME, $this->CONN) or die(“데이터베이트 접근 실패! 환경설정을 확인해주세요.”);
}
function getMessage()
{
return $this->message;
}
//GET NUM ROWS
function getNumRows()
{
return mysql_num_rows($this->result);
}
//GET AFFECTED ROWS
function getAffectedRows()
{
return mysql_affected_rows($this->result);
}
// INSERT, UPDATE, DELETE 등의 쿼리문 실행. (결과 없음)
function Open($query) {
$this->ERR_MSG = “”;
if (!mysql_query($query)) {
$this->ERR_QUERY = $query;
$this->ERR_MSG = mysql_error();
return false;
}
}
// SELECT 쿼리문 실행. (결과 있음)
function Execute($query) {
$this->ERR_MSG = “”;
if (!$this->RESULT = mysql_query($query)) {
$this->ERR_QUERY = $query;
$this->ERR_MSG = mysql_error();
return false;
}
else {
$this->REC_COUNT = mysql_num_rows($this->RESULT);
$this->ROW = mysql_fetch_assoc($this->RESULT);
$this->ROWNUM = 0;
}
}
// RecordCount
function RecordCount() {
return $this->REC_COUNT;
}
//insert_ID
function InsertID() {
if($this->result=mysql_insert_id($this->CONN)){
return($this->result);
} else {
return $this->errorMessage(“Cannot retrieve auto_increment value: $this->CONN”);
}
}
// 레코드 처음
function MoveFirst() {
mysql_data_seek($this->RESULT, 0);
$this->ROW = mysql_fetch_assoc($this->RESULT);
}
// 레코드 마지막
function MoveLast() {
mysql_data_seek($this->RESULT, $this->REC_COUNT – 1);
$this->ROW = mysql_fetch_assoc($this->RESULT);
}
// 다음 레코드
function MoveNext() {
$this->ROWNUM = $this->ROWNUM + 1;
if ($this->ROWNUM < $this->REC_COUNT) {
mysql_data_seek($this->RESULT, $this->ROWNUM);
$this->ROW = mysql_fetch_assoc($this->RESULT);
return true;
}
else { return false; }
}
// 이전 레코드
function MovePrev() {
$this->ROWNUM = $this->ROWNUM – 1;
if ($this->ROWNUM >= 0) {
mysql_data_seek($this->RESULT, $this->ROWNUM);
$this->ROW = mysql_fetch_assoc($this->RESULT);
return true;
}
else { return false; }
}
// 필드값 가져오기
function Field($field_name) {
return $this->ROW[$field_name];
}
// 데이터베이스 접속 종료
function Close() {
mysql_close($this->CONN);
}
// 가장 최근 에러메세지 표시
function ShowError() {
if (strlen($this->ERR_MSG) > 0) {
$msg = “<big><b>오류 내역입니다</b></big><br>”;
$msg .= “<table width=’100%’ border=’0′ cellpadding=’4′ cellspacing=’1′ bgcolor=’#BCBCBC’>”;
$msg .= “<tr>”;
$msg .= “<td bgcolor=’#EFEFEF’><span style=’font-size : 9pt;’><b>실행된 쿼리문</b></span></td>”;
$msg .= “<td bgcolor=’#FFFFFF’><span style=’font-size : 9pt; color : #666666;’>”.$this->ERR_QUERY.”</span></td>”;
$msg .= “</tr>”;
$msg .= “<tr>”;
$msg .= “<td bgcolor=’#EFEFEF’><span style=’font-size : 9pt;’><b>에러 메세지</b></span></td>”;
$msg .= “<td bgcolor=’#FFFFFF’><span style=’font-size : 9pt; color : #666666;’>”.$this->ERR_MSG.”</span></td>”;
$msg .= “</tr>”;
$msg .= “</table>”;
$msg .= “<br><br>”;
$msg .= “<div align=’center’><input type=’button’ value='<< MOVE BACK’ onClick=’history.go(-1)’ style=’border : solid 1 #000000; background-color : #5D5D5D; color : #FFFFFF;’>”;
echo $msg;
}
}
/********************************************
DO QUERY METHOD:결과 아이디 받아옴
*******************************************/
function doQuery($query)
{
$this->query=$query;
$this->result=mysql_query($this->query,$this->CONN) or die(mysql_error() );
return $this->result;
}
/********************************************
DO LIST:리스트 출력
*******************************************/
function doList($recordPerPage,$currentPage)
{
if($this->i==0)
{
$firstRecord=$recordPerPage*($currentPage-1);
$this->query=$this->query.’ LIMIT ‘.$firstRecord.’,’.$recordPerPage;
$this->result=$this->doQuery($this->query);
}
if(!$row=mysql_fetch_array($this->result) )
{
$this->query=explode(‘LIMIT’,$this->query);
$this->query=$this->query[0];
$this->result=$this->doQuery($this->query);
$this->i=0;
return false;
}
//RETURN ROWS
$this->i++;
return $row;
}
/********************************************
GET PAGE INDEX:페이지 인텍스 HTML코드 반환
*******************************************/
function getPageIndex($recordPerPage,$currentPage)
{
//SET ARRAY REFER TO PARAMETERS:페이지 인덱스 생성을 위한 기본값 정렬
$pageIndex[totalRecord] = $this->getNumRows();
$pageIndex[recordPerPage] = $recordPerPage;
$pageIndex[pagePerBlock] = 10;
$pageIndex[currentPage] = $currentPage;
//CALCULATE PAGE IDNEX:
$pageIndex[totalPage]=ceil($pageIndex[totalRecord]/$pageIndex[recordPerPage]);
$pageIndex[currentBlock]=ceil($pageIndex[currentPage]/$pageIndex[pagePerBlock]);
$pageIndex[totalBlock]=ceil($pageIndex[totalPage]/$pageIndex[pagePerBlock]);
//FIRST PAGE/LAST PAGE
$pageIndex[firstPage]=($pageIndex[currentBlock]*$pageIndex[pagePerBlock]) – ($pageIndex[pagePerBlock]-1);
$pageIndex[lastPage]=($pageIndex[currentBlock]*$pageIndex[pagePerBlock]);
/****************************************
//DEFINE NEW QUERY_STRING
페이지 링크 정의를 위한 URL파싱
***************************************/
parse_str($_SERVER[QUERY_STRING],$QUERY_STRING);
unset($QUERY_STRING[page]);
foreach($QUERY_STRING as $key=>$value)
{
if(!$temp){
$temp=”$key=$value”;
}else{
$temp.=”&$key=$value”;
}
}
$QUERY_STRING=$temp;
/****************************************
CREATE PAGE INDEX HTML CODE
HTML코드 생성
***************************************/
##PREVIOUS BLOCK
if($pageIndex[currentPage] > $pageIndex[pagePerBlock])
{
$pageIndex[htmlCode]='<a href=”.$_SERVER[PHP_SELF].’?’.$QUERY_STRING.’&page=’.( ($pageIndex[currentBlock]-2)*$pageIndex[pagePerBlock]+1).” target=’_self’ onfocus=’blur();’ > 이전 </a>’;
}
##PAGE INDEX
for($i=$pageIndex[firstPage]; $i<=$pageIndex[lastPage]; $i++)
{
if($i<=$pageIndex[totalPage])
{
if($i==$pageIndex[currentPage])
{
$pageIndex[htmlCode].=’ <b>’.$i.'</b>’;
}else{
$pageIndex[htmlCode].='<a href=”.$_SERVER[PHP_SELF].’?’.$QUERY_STRING.’&page=’.$i.” target=’_self’ onfocus=’blur();’ > ‘.$i.’ </a>’;
}
}
}
##NEXT BLOCK
if($pageIndex[currentBlock] <= ($pageIndex[totalBlock]-1) )
{
$pageIndex[htmlCode].='<a href=”.$_SERVER[PHP_SELF].’?’.$QUERY_STRING.’&page=’.($pageIndex[currentBlock]*$pageIndex[pagePerBlock]+1).” target=’_self’ onfocus=’blur();’ > 다음 </a>’;
}
##RETURN PAGE INDEX ARRAY
return $pageIndex;
}//END METHOD
/********************************************
DO SELECT
*******************************************/
function doSelect($query)
{
$this->query=$query;
$this->result = $this->doQuery($this->query);
return mysql_fetch_array($this->result);
}
/********************************************
DO INSERT METHOD
배열을 참조,데이타베이스에 입력
*******************************************/
function doInsert($table,$array)
{
##GET TABLE STRUCTURE
$query=”SHOW COLUMNS FROM $table”;
$result=mysql_query($query,$this->CONN);
while($row=mysql_fetch_array($result) )
{
$columns[$i]=$row[Field];
$values[$i]=”‘”.$array[$columns[$i]].”‘”;
$i++;
}
$columns=implode(“,”,$columns);
$values=implode(“,”,$values);
##SEND QUERY STATEMENT;
$query=”INSERT INTO $table ($columns) VALUES ($values)”;
$result=mysql_query($query,$this->CONN);
if(!$result)
{
die(mysql_error() );
return false;
}else{
return true;
}
}
/********************************************
DO UPDATE METHOD
업데이트
*******************************************/
function doUpdate($table,$array,$key_name,$key_value)
{
##GET TABLE STRUCTURE
$query=”SHOW COLUMNS FROM $table”;
$result=mysql_query($query,$this->CONN);
$i=0;
while($row=mysql_fetch_array($result) )
{
if(isset($array[$row[Field]]) )
{
$set[$i]=$row[Field].”='”.$array[$row[Field]].”‘”;
$i++;
}
}
$set=implode(“,”,$set);
##EXECUTE QUERY STATEMENT
$query=”UPDATE $table SET $set WHERE $key_name=’$key_value'”;
$this->doQuery($query);
return true;
}
/********************************************
테이블 리스트 반환
*******************************************/
function GetTableList(){
if($this->result=mysql_list_tables($DB_NAME,$this->CONN)){
$i=0;
while($i < mysql_num_rows($this->result)){
$tb_names[$i]=mysql_tablename($this->result,$i);
$i++;
}
return($tb_names);
}else
return $this->errorMessage(“Unable to find any tables in database: $DB_NAME”);
}
/********************************************
필드명 반환
*******************************************/
function GetFieldList($tbl_name){
if($this->result=mysql_list_fields($DB_NAME,$tbl_name,$this->CONN)){
$i=0;
while($i < mysql_num_fields($this->result)){
$fd_names[$i]=mysql_field_name($this->result,$i);
$i++;
}
return($fd_names);
}else
return $this->errorMessage(“Unable to find any field list in table: $tbl_name”);
}
/********************************************
returns XML-formatted record rows from query results.
$this->data holds the XML only, and the function returns the XML header + the data
example:
header(“Content-type: text/xml”);
echo $sql->XML_Output(“SELECT * FROM yourTable”);
********************************************/
function XML_DataOutput($query, $tags = array(‘dataset’,’record’)) {
if($this->result=mysql_query($query,$this->CONN)) {
$this->fields=mysql_num_fields($this->result);
$xmlheader='<?xml version=”1.0″ ?>’.””;
$this->data.='<‘.$tags[0].’>’.””;
while($this->rows = mysql_fetch_array($this->result)) {
$this->data.= “‘”>\\t\\t”.'<‘.$tag.’>’. preg_replace(“/([\\r])/”, ”, strip_tags($this->rows[$i])). ‘</’.$tag.’>’.””;
}
$this->data.= “
에 배치하는 팁앤텍