Php design mode-data ing mode (Application and database Interaction Mode)

The design pattern mentioned above greatly improves the readability and maintainability of the Code. However, there is a basic requirement and challenge in Web Application Design and Development: database applications, these design patterns are not involved. The data ing mode allows you to better organize your applications to interact with databases.

The following uses the actual code to describe if a table changes. You can modify the client code. Especially for game projects, requirements may change frequently. Modifying the table structure may cause a large amount of code changes.

First, we use PDO for database access:

<? Php/*** Filename: db. class. php ** db class, use PDO lib ** @ author guisu. huang * @ version 1.0 **/class Db {public static $ db = null; private $ _ dbh = null; public static function getInstance () {if (self :: $ db = null) {self: $ db = new self (BACKEND_DBHOST, BACKEND_DBUSER, BACKEND_DBPW, BACKEND_DBNAME);} return self: $ db ;} private function _ construct ($ host, $ user, $ pass, $ dbname) {try {$ this-> _ dbh = ne W PDO ('mysql: dbname = '. $ dbname. '; host = '. $ host, $ user, $ pass); $ this-> _ dbh-> query ('set names '. BACKEND_DBCHARSET); $ this-> _ dbh-> setAttribute (PDO: MYSQL_ATTR_USE_BUFFERED_QUERY, true); $ this-> _ dbh-> setAttribute (PDO: ATTR_ERRMODE, true );} catch (PDOException $ e) {throw new Exception ('can not connect db') ;}} private function getExecuteResult ($ SQL, $) {$ type = strtolower (substr (trim ($ SQL), 0, 6); switch ($ t Ype) {case 'update': case 'delete': $ result = $ something-> rowcount (); // return the affected number of rows break; case 'insert ': $ result = $ this-> getLastId (); break; case 'select': $ result = $ th-> fetchAll (PDO: FETCH_ASSOC); break; default: break ;} return $ result ;} /************************************ SQL * **********************/public function getOne ($ SQL) {try {$ rs = $ this-> _ dbh-> query ($ SQL); $ result = $ rs-> fetch (PDO: FETCH_ASSOC); if (! Empty ($ result) {return $ result;} catch (PDOException $ e) {throw new Exception ($ this-> _ dbh-> errorInfo ());} return false;} public function getAll ($ SQL) {try {$ rs = $ this-> _ dbh-> query ($ SQL ); $ result = $ rs-> fetchAll (PDO: FETCH_ASSOC); if (! Empty ($ result) {return $ result;} catch (PDOException $ e) {throw new Exception ($ this-> _ dbh-> errorInfo ());} return false;} public function exec ($ SQL) {try {$ exec = $ this-> _ dbh-> exec ($ SQL);} catch (PDOException $ e) {throw new Exception ($ this-> _ dbh-> errorInfo ();} return $ exec ;} /*** ignore the key value * Execute a prepared statement by passing an array of values $ something = $ dbh-> prepare ('select name, color, calories FROM Fruit WHERE calories <? AND color =? '); $ Something-> execute (array (150, 'red'); $ red = $ something-> fetchAll (); $ something-> execute (array (175, 'yellow'); $ yellow = $ something-> fetchAll (); * @ param unknown_type $ SQL * @ param unknown_type $ arr * @ return unknown */public function executeArr ($ SQL, $ arr) {try {$ something = $ this-> _ dbh-> prepare ($ SQL); $ r = $ something-> execute ($ arr); if ($ r) {return $ this-> getExecuteResult ($ SQL, $ something) ;}} catch (PDOException $ e) {throw new Exception ($ E-> getMessage (). $ this-> _ dbh-> errorInfo ();}/*** join array: * Execute a prepared statement by passing an array of values $ SQL = 'select name, color, calories FROM fruit WHERE calories <: calories AND color =: color '; $ something = $ dbh-> prepare ($ SQL, array (PDO: ATTR_CURSOR => PDO :: CURSOR_FWDONLY); $ th-> execute (array (': calories' => 150, ': color' => 'red ')); $ red = $ something-> fetchAll (); ** @ param unknown_typ E $ SQL * @ param unknown_type $ arr * @ return unknown */public function executeAsoc ($ SQL, $ arr) {try {$ array = array (); if ($ arr) {foreach ($ arr as $ key => $ v) {if (strpos ($ SQL ,':'. $ key )! = False) {$ array [':'. $ key] = $ v ;}}$ something = $ this-> _ dbh-> prepare ($ SQL); $ r = $ something-> execute ($ array ); if ($ r) {return $ this-> getExecuteResult ($ SQL, $ something) ;}} catch (PDOException $ e) {throw new Exception ($ e-> getMessage (). $ this-> _ dbh-> errorInfo () ;}} public function beginTransaction () {return $ this-> _ dbh-> beginTransaction ();} public function commit () {return $ this-> _ dbh-> commit ();} public function rollBack (){ Return $ this-> _ dbh-> rollBack ();} public function getLastId () {return $ this-> _ dbh-> lastInsertId () ;}}?>

Class related to data ing. Use _ call to dynamically generate the getter and setter methods.

<? Php/*** abstract data ing **/abstract class Table {public function _ call ($ method, $ args) {if (preg_match ('/^ (get | set) (\ w +)/', strtolower ($ method), $ match) & $ attribute = $ this-> validateAttribute ($ match [2]) {if ('get' = $ match [1]) {return $ this-> $ attribute ;} else {$ this-> $ attribute = $ args [0] ;}} else {throw new Exception ('Call to undefined method '. _ CLASS __. '::'. $ method. '()') ;}} protected function valid AteAttribute ($ method) {if (in_array (strtolower ($ method), array_keys (get_class_vars (get_class ($ this) {return strtolower ($ method );}}} /*** ing data to a table * is generally automatically generated by the tool based on the table structure, * Otherwise, programmers often have to copy and modify this class **/class UserTable extends Table {/*** fields ** @ var unknown_type */protected $ uid = null; protected $ username = null; protected $ level = null; protected $ exp = null; protected $ ctime = null; protected $ mtime = Null;/*** table ** @ var unknown_type */public $ tableName = 'user'; public $ primaryKey = 'uid '; public static $ tablefileds = array ('uid', 'username', 'level', 'exp ', 'ctime', 'mtime ',); /*** object generation array ** @ return array */function toArray () {$ arr = array (); foreach (UserTable: $ tablefileds as $ filed) {$ getMethod = 'get '. ucwords ($ filed); $ value = $ this-> $ getMethod (); if ($ value! = Null) {$ arr [$ filed] = $ value ;}} return $ arr ;} /*** array generation object ** @ return array */function toObj ($ arr) {if (! $ Arr) {return $ this;} foreach (UserTable: $ tablefileds as $ filed) {$ setMethod = 'set '. ucwords ($ filed); $ this-> $ setMethod ($ arr [$ filed]);} return $ this ;}} /*****/class Mapper {protected $ conn = null;/***** automatic insertion * you do not want to insert a column, set the corresponding property to null. ** @ param Table $ table * @ return unknown */function save (Table $ table) {$ arr = $ table-> toArray (); $ set = ''; if ($ arr) {foreach ($ arr as $ field => $ v) {if ($ set) $ set. = ','; $ Set. = $ field. "= '". $ v. "'" ;}} if ($ set) {$ this-> conn-> exec ('insert '. $ table-> tableName. 'set '. $ set); return $ this-> conn-> getLastId () ;}}/*** update * does not want to update a column, set the corresponding property to null. ** @ param Table $ table * @ return unknown */function update (Table $ table) {$ arr = $ table-> toArray (); $ set = ''; if ($ arr) {foreach ($ arr as $ field => $ v) {if ($ set) $ set. = ','; $ set. = $ field. "= '". $ v. "'" ;}}$ p RimayGet = 'get '. ucwords ($ table-> primaryKey); if ($ set) {return $ this-> conn-> exec ('update '. $ table-> tableName. 'set '. $ set. 'where '. $ table-> primaryKey. "= '". $ table-> $ primayGet (). "'") ;}} class UserMapper extends Mapper {const INSERT_ SQL = "insert into user (username, level, exp, ctime, mtime) values (: username,: level ,: exp, now (), now () "; const UPDATE_ SQL =" update user SET username =: Username, level =: level, exp =: exp WHERE uid =: uid "; const SELECT_ SQL =" select * from user WHERE uid =: uid "; const DELETE_ SQL = "delete from user WHERE uid =: uid"; function _ construct () {$ this-> conn = Db: getInstance ();} /*** we can override save ** @ param unknown_type $ userTable */public function save2 ($ userTable) {$ rs = Db: getInstance () -> executeArr (self: INSERT_ SQL, $ userTable-> toArray (); return $ rs ;}/ * ** Enter description here... ** @ param unknown_type $ userTable */public function update2 ($ userTable) {return $ this-> conn-> execute (self: UPDATE_ SQL, $ userTable-> toArray ();}/*** Enter description here... ** @ param unknown_type $ arr */public function find ($ userTable) {$ rs = $ this-> conn-> executeAsoc (self: SELECT_ SQL, $ userTable-> toArray (); return $ rs? $ UserTable-> toObj ($ rs [0]): $ userTable ;}}?>

Actual customer test:

<? PHP/*** Database Configuration File **/define ('backend _ dbhost', 'localhost'); define ('backend _ dbuser', 'root '); define ('backend _ dbpw ', '000000'); define ('backend _ dbname', 'sample'); define ('backend _ dbcharset ', 'utf-8'); // SQL/* Create Table if not exists 'user' ('uid' int (11) not null auto_increment, 'username' varchar (64) not null, 'level' int (11) not null default '0', 'expires' int (11) not null default '0 ', 'ctime' datetime not null default '2017-00-00 00:00:00 ', 'mtime' timestamp not null default '2017-00-00 00:00:00', primary key ('uid '), key 'username' ('username') engine = InnoDB default charset = Latin1 auto_increment = 12; */class client {static function main () {$ usermapper = new usermapper (); $ user = new usertable (); // insert // $ user-> setusername ('guisu'); // $ user-> setlevel (1 ); // $ user-> setexp (10); // $ usermapper = new usermapper (); // $ r = $ usermapper-> Save ($ user ); // find $ user-> setuid (10); $ user = $ usermapper-> Find ($ user); var_dump ($ user ); // update $ user-> setusername ('guisu2 '); $ r = $ usermapper-> Update ($ user); var_dump ($ R );}}

Generally, the client is the business logic layer, and the usermapper is the data access layer. The underlying data structure of usertable.

If the table user is modified:

1) The tool automatically generates the usertable class again

2) modify only the client code and a small amount of usermapper code. Generally, modify the content of the constant const of usermapper.

The best reprinted description Source: PHP design mode-data ing mode (Application and database interaction mode) http://blog.csdn.net/hguisu/article/details/7569968