Object-oriented database operations classes

Specific implementation features:

1, connect the database;

2, inserting data;

3, update the data;

4. Delete Data '

5, modify the data;

6, to find the maximum value;

7, to find the minimum value;

8, to find the average;

9, sum;

10, the designated query;

The specific code is divided into three parts:

Config file: Mainly used to connect the database

<?phpreturn Array (' db_host ' = ' 127.0.0.1 ',//Host ' Db_user ' + ' root ',//username ' db_pwd ' = ' 123456 ',//password ' db_name ' = = ' blog ',//database name ' db_charset ' + ' UTF8 ',//Character set ' db_prefix ' = ' bbs_ ',//prefix);

Second, the database operation class:

<?php/** *  database Operations Class  */class usermodel{/* member properties  *///link protected  $link;// Host protected  $host;//database name protected  $dbName;//Character Set protected  $charset;//table name protected  $table;// User name protected  $user;//password protected  $pwd;//table prefix protected  $prefix;//field protected  $fields;//Saved Query , updated, added parameters protected  $options;/* Member Method  *///initialize database public function __construct (array $ Config) {//To initialize a batch of member properties $this->host =  $config [' db_host ']; $this->user =  $config [' Db_user $this->pwd =  $config [' db_pwd ']; $this->dbname =  $config [' db_name ']; $this charset =  $config [' Db_charset ']; $this->prefix =  $config [' db_prefix '];//connection $this->link  =  $this->connect ();//Determine connection Success    failure handling if  (! $this->link)  {exit (' Database connection or database selection failed. ‘);} Table name    need to process $this->table =  $this->gettable ();//field   need to process $this->fields =   $this->getfields ();}//Connection Database Member method Protected function connect () {$conn  = mysqli_connect ($this->host, $this User, $this->pwd);//Connection Database failed processing if  (! $conn)  {return flase;} Select data failure handling if  (!mysqli_select_db ($conn, $this->dbname))  {return false ;} Sets the character set Mysqli_set_charset ($conn, $this->charset);//returns the processing result return  $conn;} Initialize table   "Temporary error, followed by namespace resolution" protected function gettable () {//Judgment user time set? if  (Isset ($this->table))  {//set over to the user, first discard the user prefix, and then stitching the prefix, to ensure unity return  $this->prefix   ltrim ($this->table, $this->prefix);}  else {//is not set up with the   class name stitching prefix, a brand-new table name//get_class ()   Get the class name   equivalent to  __class__//get the class name after string extraction [ SUBSTR ( string,start,length )], and convert all to lowercase [Strtolower ()]return  $this->prefix .  Strtolower (substr (Get_class ($this), 0,-5));}} Initialize field   need to cache the field into a file Protected function getfields () {//If there is a file description of the field that previously cached the file, it can be included directly, But you need to know the rules for file path//definition file path $filepath =  './caceh/' .  $this->table .  '. php ';//Determine when there are cache files if  (file_exists ($filePath))  {// The presence of the cache file directly contains the return include  $filePath;}  else {//do not need to generate a cache file//first need to query the field $fields =  $this->queryfields ();//var_export ()   Output or return a variable string   true means not to print    save it $str =  "<?php \n return ".  var_export ($fields, true)  .  ';? > ';//write to the cache file file_put_contents (file save path, what needs to be written in) file_put_contents ($filePath,  $str);} return  $fields;} Query field handles Protected function queryfields () {//Print field's SQL statement $sql =  ' desc  '  . $ This->table;//var_dump ($sql);//Execute SQL statement    need to define member method query$data =  $this->query ($sql); fields = [];//you want to get the field, you need to traverse the returned data foreach  ($data  as  $key  =>  $value)   {$fields [] =  $value [' Field '];if  ($value [' Key '] ==  ' PRI ')  {$fields [' _PK '] =   $value [' Field '];}} return  $fields;} SystemLevel query (defined as  public ), when called Externally, a custom SQL statement can just call the member method//query The corresponding result, which is only for reading using the query corresponding results public function  Query ($sql) {//executes an SQL statement $result = mysqli_query ($this->link, $sql);if  ($result)  {$data  = [];//gets each row of data while  ($row  = mysqli_fetch_assoc ($result))  {$data [] = $ Row;} return  $data;}  else {return false;}} Query member method//Prepare a SQL statement that does not need to be changed//when the user invokes the query, the parameters that are stored in the call are replaced with the SQL statement//Send SQL statement//Return the result of Public function select () { Splicing SQL statement $sql =  "select %fields% from %table% %where% %group% %having%  %order% %limit% ";//replace the corresponding part of the SQL statement with $newsql = str_replace (Array ('%fields% ', '%table% ', '%where % ', '%group% ', '%having% ', '%order% ', '%limit% ', ', ' Array ($this->parsefields (), $this->parsetable (), $this- Parsewhere (), $this->parsegroup (), $this->parsehaving (), $this->parseorder (), $this->parselimit (),), $ SQL);echo  $newsql $this->sql =  $newsql;return $this->query ($newsql);} field handling Protected function parsefields () {///Because we don't need to compare the primary key to the field, we need to unset ()//Assign the current field to a variable $fields  =  $this->fields;unset ($fields [' _pk ']);//What is the form of the judging field (string, array) if  (Is_array ($this->options[' fields ') [0]))  {//Traverse Remove key value foreach  ($this->options[' Fields '][0] as  $key  =>  $value)  {// if  (!in_array ($value,  $fields))  {//If an incoming field is valid (a field in the table structure), unset () unset ($this) If it does not belong to a valid field options[' Fields '][0][$key]);}} Return join (', ', $this->options[' Fields '][0]);}  else if  (is_string ($this->options[' Fields '][0])) {//If the string is first transformed into an array for processing $this->options[' Fields '][0] = explode (', ',  $this->options[' Fields '][0]);//Traverse foreach  ($this->options[ ' Fields '][0]  as  $key  =>  $value)  {//Determine if the field is legitimate if  (!in_array ($value, $fields ))  {unset ($this->options[' Fields '][0][$key]);} Return join (', ', $this->options[' Fields '][0]);}}  else  {Return join (', ', $fields);}} Determine whether the user has manually specified the query which use//if specified, then the user-defined options in the table in the list of//if not set, then the default is Protected function parsetable () {if   (isset ($this->options[' table '][0])  {return  $this->options[' table '][0];}  else {return  $this->table;}} Determine if the user has set where  if set to the user settings, no setting is empty Protected function parsewhere () {if  (Isset ($this options[' where '][0]))  {return  ' where  '  . $this->options[' where '][0];}  else {return  ';}} Determine if the user has set group  if set to the user settings, no setting is empty Protected function parsegroup () {if  (Isset ($this options[' where '][0]))  {return  ' group by  '  . $this->options[' GROUP '][0];}  else {return  ';}} Determine if user settings have been set to the user settings, no setting is empty protected function parsehaving () {if  (Isset ($this options[' have '][0])  {return  ' having  '  . $this->options[' having '][0];}  else {return  ';}} Determine if the user has set orDer If set to the user set to prevail, no setting is empty Protected function parseorder () {if  (isset ($this->options[' order '][0] )  {return  ' order by  '  . $this->options[' ORDER '][0];}  else {return  ';}} Limit can have the following methods Protected function parselimit () {if  (isset ($this->options[' limit '][0])   {if  (Is_int ($this->options[' limit '][0]))  {//the user is passing in an integer  , which is the number of queries specified return  ' limit  '  .  $this->options[' limit '][0];}  else if  (Is_array ($this->options[' limit '][0])) {//the user passes in an array, the first element in the array is $offset, the second element is $ numreturn  ' limit  '  . join (', ', $this->options[' LIMIT '][0]);}  else {//If the user passes in a string, then the return  ' limit  '  .  $this->options[' LIMIT '][0] will be passed on by users;}}  else {return  ';}} The inserted member method Public function insert ($data) {//sql statement $sql =  "insert into %table% (% fields%)  values (%values%)   ";//Replace $newsql = str_replace (aRray ('%table% ', '%fields% ', '%values% '), Array ($this->parsetable (), $this->parseinsertfieldes ($data), join   (', ', $this->parsevalue ($data)), $sql);//re-assign $this->sql =  $newsql;echo  $this sql;//calls exec and executes return  $this->exec ($newsql, True);} fields to process insertion Protected function parseinsertfieldes (& $data) {foreach  ($data  as  $key  =>  $value)  {if  (!in_array ($key, $this->fields))  {unset ($data [$key]);} Return join (', ', Array_keys ($data));} Handle insertion time value//divided into string    array   empty case processing protected function parsevalue ($data) {if  ($ is_string ($ Data)  {$data  =  ' \ '  .  $data  .  ' \ ';}  else if  (Is_array ($data)) {$data  = array_map (array ($this,  ' Parsevalue '), $data);}  else if  (Is_null ($data)) {$data  =  ' null ';} return  $data;} Public function exec ($sql, $isInsertId  = false) {$result  = mysqli_querY ($this->link, $sql);if  ($result)  {if  ($isInsertId)  {//insertfan return to auto-growing idreturn  mysqli_insert_id ($this->link);}  else {//update delete  returns the number of rows affected Return mysqli_affected_rows ($this->link);}}  else {return false;}} Update method Public function update ($data) {$sql  =  "update %table% set %sets%  %where% %order% %limit% "; $newsql  = str_replace (Array ('%table% ', '%sets% ', '%where% ', '%ORDER% ', '%limit% '), Array ($this->parsetable (), $this->parsesets ($data), $this->parsewhere (), $this- Parseorder (), $this->parselimit (),), $sql); $this->sql =  $newsql;//echo  $newsql;return  $this->exec ($newsql);} Update content Settings Protected function parsesets ($data) {$sets  = [];foreach  ($data  as $ key =>  $value)  { if  (In_array ($key, $this->fields))  {  $sets [] =   $key  .  ' = '  .  $this-&Gt;parsevalue ($value);  }}return join (', ', $sets);} Delete Method Public function delete () {$sql  =  "delete from %table% %where% %o rder% %limit% "; $newsql  = str_replace (Array ('%table% ', '%where% ', '%order% ', '%limit% '), Array ($ This->parsetable (), $this->parsewhere (), $this->parseorder (), $this->parselimit (),), $sql); $this sql =  $newsql;return  $this->exec ($newsql);} Total Public function sum ($field  = null ) {if  (Is_null ($field))  {$field   =  $this->fields[' _pk '];} $sql  =  "Select count ($field)  as sum from %TABLE% %WHERE% "; $ Newsql = str_replace (Array ('%table% ', '%where% ',), Array ($this->parsetable (), $this->parsewhere () ,), $sql), $this->sql =  $newsql, $data  =  $this->query ($newsql);return  $data [0][' Sum '];} Maximum number of Public function max ($field  = null ) {if  (Is_null ($field))  {$field  =  $this->fields[' _pk ';} $sql  =  "Select max ($field)  as max from %TABLE% %WHERE% "; $newsql  = str_replace (Array ('%table% ', '%where% ',), Array ($this->parsetable (), $this->parsewhere (),), $ SQL), $this->sql =  $newsql, $data  =  $this->query ($newsql);return  $data [0][' Max '];} The minimum number of public function min ($field  = null ) {if  (Is_null ($field))  {$field  =  $this->fields[' _pk ');} $sql  =  "Select min ($field)  as min from %TABLE% %WHERE% "; $newsql  = str_replace (Array ('%table% ', '%where% ',), Array ($this->parsetable (), $this->parsewhere (),), $ SQL), $this->sql =  $newsql, $data  =  $this->query ($newsql);return  $data [0][' min '];} Averaging public function avg ($field  = null ) {if  (Is_null ($field))  {$field  =  $this->fields[' _pk '];} $sql  =  "Select avg ($field)  as avg from %TABLE% %WHERE% "; $newsql  = str_replace (Array ('%table% ', '%where% ',), Array ($this->parsetable (), $this->parsewhere (),), $ SQL), $this->sql =  $newsql, $data  =  $this->query ($newsql);return  $data [0][' avg '];} An intelligent query method that is automatically queried by field Protected function getby ($field, $value) {$sql  =  select %fields%  from %table% %where% "; $newsql  = str_replace (Array ('%fields% ', '%table% ', '%WHERE% '), Array ($this->parsefields (), $this->parsetable (), '  WHERE  '. $field  .  "= ' $value '",), $sql ); $this->sql =  $newsql;echo  $newsql;return  $this->query ($newsql);} __call method, for user request limit (),  order (), group () and so on to save it to the options,  judge the legality of its method;//And return  $this   Allow it to operate coherently, Public function __call ($func, $args) {//Legal $allow = [' where ', ' table ', ' Fields ', ' Order ', ' limit ', ' group ', ' having '];//to turn the incoming unifiedTo lowercase $func = strtolower ($func);if  (In_array ($func, $allow))  {$this->options[$func] =   $args;return  $this;}  else if (substr ($func, 0,5)  ==  ' Getby ') {$field  = substr ($func, 5);if  (in_ Array ($field, $this->fields))  {return  $this->getby ($field, $args [0]);}}  else {exit  (' method is not legal! ‘);}} Structure method   Close page/image consumption Public function __destruct () {mysqli_close ($this->link);}}

Third, test (verification) Code:

Include file $config = include  ' config.php '; $blog  = new usermodel ($config);//test Query $data  =  $blog->fields (' Uid,uesrname,password ')->table (' Bbs_user ')->limit ([up])->order (' UID  desc  ')->group (' username ')->select () var_dump ($data);//Insert Test $_post[' uesrname '] =  ' Chen '; $_post[' password '] = 123456;$_post[' creatime '] = 123423;$_post[' Senlin '] =   ' Non-existent field processing ';echo  $blog->insert ($_post);//Update Test $_post[' uesrname '] =  ' 1kkkkk12 '; $_post[' Password '] = 123456;$_post[' createtime '] = 234567;$_post[' Haiyan '] =  ' You can have a long dim sum eye '; echo  $blog->where (' uid>0 ')->limit (' 1 ')->update ($_post);//delete Test echo  $blog->where (' UID >0 and uid<2 ')->delete ();//test summation echo  $blog->sum (' uid ');//test for maximum number echo  $blog Max (' uid ');//test to find the minimum number of echo  $blog->min ();//test averaging echo  $blog->avg ();//test automatic one by field to query $data =   $blog->getbypassword(' 123456 '); Var_dump ($data); 

This article from "Hello I am the Forest" blog, reprint please contact the author!

Object-oriented database operations classes