Start your MVC with PHP (ii) Abstract Database interface

Data | database

II. Abstract database interfaces (using data manipulation management classes)

In the MVC pattern development, the model layer is responsible for all the operations of the database, in order to the operation of the database unified management, we need to define a database operations management class, by him to replace all the database operations, that is, the entire system only this database operation management class, You can manipulate the database directly, and if other classes need to operate on the database, it must be invoked and implemented through this class.

The following DB class is one such class.

<?php
/**************************************************************
* Descript:mysql Database Operations Management class
*
* Author: older Youth
* email:wenadmin@sina.com
* qq:303015292
* msn:wenguangqing@hotmail.com
*************************************************************/
/*
* Example 1: Get sequence
*;?
* $db = new db ();
* $result = $db->getseq (' art_id ', 2, 1);
*?>
*/
/*
* Example 2: Paging query
*;?
* $db = new db ();
* $result = $db->&querypage (' Select id,name from table ', 2, DB_FETCH_ASSOC);
* foreach ($result as $row)
* Echo $row [' id '], '------'. $row [' name ']. ' <br> ';
*?>
*/
/*
* Example 3: Inserting data
*;?
* $db = new db ();
* $result = $db->execute ("INSERT into table (ID, name) VALUES (1, ' name ')");
* IF ($result >0) echo ' Insert data successfully ';
* Else echo ' Insert data failed ';
*?>
*/
/*
* Define database Join options
* @var db_host String Database host name or address
* @var db_name String database name
* @var db_user String Database user name
* @var db_pwd String database user's password
* @var Db_pconnect Boolean to establish a persistent connection
*/
Define (' db_host ', ' localhost ');
Define (' db_name ', ' test ');
Define (' Db_user ', ' root ');
Define (' db_pwd ', ');
Define (' Db_pconnect ', true);
/*
* Define types that return data query results
* @var DB_FETCH_ASSOC int result invocation: $result [' name ']
* @var db_fetch_num int result invocation mode: $result [0]
* @var db_fetch_both int result invocation mode: $result [' name '] or $result [0]
* @var Db_fetch_object int result invocation method: $result->name
*/
Define (' Db_fetch_assoc ', 0);
Define (' Db_fetch_num ', 1);
Define (' Db_fetch_both ', 2);
Define (' Db_fetch_object ', 3);
/*
* Define the name of the default sequence generator
*/
Define (' Db_sequence_tablename ', ' sequences ');

Class db{
/*
* Current database Join options
*/
var $dbHost = Db_host;
var $dbName = db_name;
var $dbType = ' Mysql ';
var $dbUser = Db_user;
var $dbPwd = db_pwd;
var $pcnn = Db_pconnect;
/*
* Current Database connection
*/
var $cnn = ';
/*
* Return type of data query results
*/
var $queryFetchType = Db_fetch_assoc;
/*
* Initialization function
*/
function Db () {
$this->cnn = ($this->pcnn mysql_connect ($this->dbhost, $this->dbuser, $this->dbpwd):
Mysql_connect ($this->dbhost, $this->dbuser, $this->dbpwd)) or
$this->_halt (' Database connection error ');
mysql_select_db ($this->dbname, $this->cnn) or $this->_halt (' Database selection error ');
}
/*
* Data Query function
*
* @param $sql String data query statement
* @param return type $fetchType int data query Results
*
* @return Array Data query results
*/
function &query ($sql, $fetchType =db_fetch_assoc) {
$data = Array ();
$rs = &mysql_query ($sql, $this->cnn) or $this->_halt (' Data query error ', $sql);
$exe = $this->_getcommand ($fetchType);
while ($row =& $exe ($rs))
$data [] = & $row;
return $data;
}
/*
* Paging data query function
*
* @param $sql String data query statement
* @param $page int current pre-query page number
* @param how many records $pageSize int display per page
* @param return type $fetchType int data query Results
*
* Data query results, and data paging information
* @return Array (' pageSize ' => the number of bars displayed per page
* Total records of ' RecordCount ' =>
* ' PageCount ' => total pages
* ' page ' => current page number
* ' Isfirst ' => is the first page
* ' Islast ' => is the last page
* ' Start ' => returns the serial number of the first record of the result
* ' SQL ' => SQL statement for query
* ' data ' => query results
*                 )
* Data query results, and data paging information
*/
function &querypage ($sql, $page =1, $pageSize =20, $fetchType =db_fetch_assoc) {
$COUNTSQL = preg_replace (' | Select.*from|i ', ' SELECT count (*) Count from ', $sql);
$data [' pageSize '] = (int) $pageSize <1? (int) $pageSize;
$data [' recordCount '] = $this->getone ($countSql, ' count ');
$data [' pagecount '] = ceil ($data [' RecordCount ']/$data [' pageSize ']);
$data [' page '] = $data [' PageCount ']==0? 0: ((int) $page <1 1: (int) $page);
$data [' page '] = $data [' page ']> $data [' PageCount ']? $data [' PageCount ']: $data [' page '];
$data [' isfirst '] = $data [' Page ']>1? False:true;
$data [' islast '] = $data [' page ']< $data [' PageCount ']? False:true;
$data [' start '] = ($data [' page ']==0]? 1: ($data [' page ']-1] * $data [' pageSize ']+1;
$data [' sql '] = $sql. ' LIMIT '. ($data [' Start ']-1]. $data [' pageSize '];
$data [' data '] = & $this->query ($data [' SQL '], $fetchType);
return $data;
}
/*
* Data query to return only the 1th row of data
*
* @param $sql String data query statement
* @param return type $fetchType int data query Results
*
* @return Array Data query results
*/
function &queryrow ($sql, $fetchType =db_fetch_assoc) {
$rs = &mysql_query ($sql, $this->cnn) or $this->_halt (' single-row data query error ', $sql);
$exe = $this->_getcommand ($fetchType);
Return $exe ($RS);
}
/*
* Data query to return only the 1th row nth column of data
*
* @param $sql String data query statement
* @param $field int returns the name or number ordinal of a data column
*
* @return String returns the value of a single field
*/
function &getone ($sql, $field = 0) {
$rs = &mysql_query ($sql, $this->cnn) or $this->_halt (' Single data query error ', $sql);
$row = Mysql_fetch_array ($RS);
return $row [$field];
}
/*
* For SQL statements, including delect/insert/update ... The execution statement
*
* @param $sql String data query statement
*
* @return String returns the number of data rows affected by the statement
*/
function Execute ($sql) {
$rs = mysql_query ($sql) or $this->_halt (' Statement execution error ', $sql);
Return Mysql_affected_rows ($this->cnn);
}
/*
* The number of the last time the data was inserted
*/
function Getinsertid () {
Return mysql_insert_id ($this->cnn);
}
/*
* Sequence generator, used to generate a sequence value that is not repeated
*
* @param $fieldName The name of the string sequence
* @param $step int serial number interval
* @param $start The starting value of the int serial number
*
* @return int New sequence value
*/
function Getseq ($fieldName, $step =1, $start =1) {
$table = Db_sequence_tablename;
$step = (int) $step;
$start = (int) $start;
$rs = mysql_query ("UPDATE $table SET seq_num=seq_num+ ($step) WHERE seq_name= ' $fieldName '");
if (! $rs | | | mysql_affected_rows ($this->cnn) <1) {
$rs = mysql_query (' SELECT * from '. Db_sequence_tablename, $this->cnn);
if (! $rs) {
$sql = "CREATE TABLE $table (
Seq_name VARCHAR is not NULL,
Seq_num BIGINT () DEFAULT 1 not NULL,
PRIMARY KEY (Seq_name)) ";
$rs = mysql_query ($sql) or $this->_halt (' Create sequence generator table failed ', $sql);
}
$rs = mysql_query ("INSERT into $table VALUES (' $fieldName ', $start)") or
$this->_halt (' Add new sequence error ', $sql);
$seq = $start;
}else{
$seq = & $this->getone ("Select Seq_num from $table WHERE seq_name= ' $fieldName '");
}
return $seq;
}

    function _getcommand ($fetchType) {
         switch ($fetchType) {
            case db_ FETCH_ASSOC: $exe = ' MYSQL_FETCH_ASSOC '; Break
            case db_fetch_num: $exe = ' mysql_fetch_row '; Break
            case Db_fetch_both: $exe = ' Mysql_fetch_ Array '; Break
            case db_fetch_object: $exe = ' Mysql_fetch_ Object '; Break
            Default: $exe = ' mysql_fetch_array ';
       }
        return $exe;
   }

function _halt ($msg) {
$errNo = Mysql_errno ($this->cnn);
$ERRSTR = mysql_error ($this->cnn);
Die ("Database error: $msg <br> $errNo: $errStr");
}
}
?>