PHP implementation of MySQL class with read and write separation function

This article mainly introduces the PHP implementation with read and write separation function of the MySQL class, has a certain reference value, now share to everyone, the need for friends can refer to

In this paper, we describe the MySQL class with read and write separation function in PHP. Share to everyone for your reference, as follows:

Overview:

1. According to the SQL statement to determine whether to connect read library or write library
2. Chained call $this->where ()->get ()
3. Different hosts correspond to different instances, no more multiple new

The specific code is as follows:

<?phpclass dbrwmysql{private static $Instance = null; Private $links = Array ();//link array private $link = null;  Current connection Public $dbType = ' read '; Public $_host= '; Database host name Public $_database = ';//Current database name public $_tablename = ';//table name of the current table public $_dt = ';//database.tablename publi c $isRelease = 0;  Whether to release public $fields = ' * ' When the query is complete;  Public $arrWhere = [];  Public $order = ';  Public $arrOrder = [];  Public $limit = ';  Public $sql = '; public $rs;//result set Private function __construct ($database = ", $tablename =", $isRelease =0) {$this->_database = $dat Abase;//database name $this->_tablename = $tablename;//table name $this->_dt = "' {$this->_database} '. ' {$thi    S->_tablename} ' ";  $this->isrelease = $isRelease;      public static function getinstance ($database = ", $tablename =", $isRelease =0) {if (self:: $Instance = = null) {    Self:: $Instance = new Dbrwmysql ($database, $tablename, $isRelease);    } Self:: $Instance->_database = $database;Self:: $Instance->_tablename = $tablename;    Self:: $Instance->_dt = "' {$database} '. ' {$tablename} ';    Self:: $Instance->isrelease = $isRelease;  Return self:: $Instance;    }//If the host is not changed and there is already a MySQL connection, no new connection is created//if the host changes, it is regenerated into an instance to create a connection//type = = ' write ' or ' read ' Public function GetLink ($type) {    $this->dbtype = $ $type;    Random selection of a database connection (read-write) $dbConfig = dbconfig::$ $type;    $randKey = Array_rand ($dbConfig);    $config = $dbConfig [$randKey];    Link database $host = $config [' Host '];    $username = $config [' username '];    $password = $config [' Password '];      if (Empty ($this->links[$host])) {$this->_host = $host;      $this->links[$host] = new Mysqli ($host, $username, $password);      if ($this->links[$host]->connect_error) {$this->error ($this->links[$host]->connect_error);    }}//Initialize link $this->link = $this->links[$host]; $this->link->query ("Set names utf8mb4;"); Supports emoji expression $this->link->query ("Use {$this->_database}; ");}  Public Function Getcurrentlinks () {return $this->links;    }//destructor public Function __destruct () {foreach ($this->links as $v) {$v->close ();    }}//query encapsulates public function query ($sql) {$this->sql = $sql; if (Strpos ($sql, ' select ')!== false) {$this->getlink (' read ');//Read Library} else {$this->getlink (' write ');//    Write Library} $this->rs = $this->link->query ($sql); ($this->rs = = = False) && $this->error (' SQL error: ' $sql.    Php_eol. $this->link->error);      Release the link after the query is complete and delete the linked object if ($this->isrelease) {$this->link->close ();    unset ($this->links[$this->_host]);  } return $this->rs;      }//Add Public Function Insert ($arrData) {foreach ($arrData as $key + = $value) {$fields [] = $key;      $values [] = "'". $value. "'"; $fields [] = "". $key. '      `';    $values [] = "'". $value. "'";    } $strFields = Implode (', ', $fields); $strValues = Implode(', ', $values);    $sql = "INSERT INTO {$this->_dt} ($strFields) VALUES ($strValues)";    $this->query ($sql);    $insert _id = $this->link->insert_id;  return $insert _id;      }//Add Public Function replace ($arrData) {foreach ($arrData as $key + = $value) {$fields [] = $key;    $values [] = "' {$value} '";    } $strFields = Implode (', ', $fields);    $strValues = Implode (', ', $values);    $sql = "Replace into {$this->_dt} ($strFields) VALUES ($strValues)";    $this->query ($sql);  return $this->link->insert_id;      }//increment//Insert multiple records//each record has the same field, but the value is not the same as public function Insertm ($arrFields, $arrData) {foreach ($arrFields as $v) {      $fields [] = "' {$v} '";    $fields [] = $v;    } foreach ($arrData as $v) {$data [] = ' ('. Implode (', ', $v). ') ';    } $strFields = Implode (', ', $fields);    $strData = Implode (', ', $data);    $sql = "INSERT INTO {$this->_dt} ($strFields) values {$strData}";    $this->query ($sql); return $this->link->insert_id;    }//Delete public Function Delete () {$where = $this->getwhere ();    $limit = $this->getlimit ();    $sql = "Delete from {$this->_dt} {$where} {$limit}";    $this->query ($sql);  return $this->link->affected_rows;    }//Change public Function Update ($data) {$where = $this->getwhere ();    $ARRSQL = Array ();    foreach ($data as $key = + $value) {$ARRSQL [] = "{$key}= ' {$value} '";    } $STRSQL = Implode (', ', $ARRSQL);    $sql = "Update {$this->_dt} set {$STRSQL} {$where} {$this->limit}";    $this->query ($sql);  return $this->link->affected_rows;    }//Gets total public function GetCount () {$where = $this->getwhere ();    $sql = "SELECT count (1) as n from {$this->_dt} {$where}";    $resault = $this->query ($sql);    ($resault ===false) && $this->error (' GetCount error: '. $sql);    $arrRs = $this->rstoarray ($resault);    $num = Array_shift ($arrRs);  return $num [' n ']; }//Convert result set to array return///If field is not empty, returnAn array of $field to re-index public function rstoarray ($field = ') {$arrRs = $this->rs->fetch_all (MYSQLI_ASSOC);//The function can only be used with the      MYSQLND Driver for PHP $this->rs->free ();//Release result set if ($field) {$arrResult = [];      foreach ($arrRs as $v) {$arrResult [$v [$field]] = $v;    } return $arrResult;  } return $arrRs;    }//Add the field name with the back quotation mark public function QW ($strFields) {$strFields = preg_replace (' #\s+# ', ' ', $strFields);    $arrNewFields = Explode (' ', $strFields);    $arrNewFields = Array_filter ($arrNewFields);    foreach ($arrNewFields as $k = + $v) {$arrNewFields [$k]= '. $v. ';  } return implode (', ', $arrNewFields); }//processing the inbound data, converting the data in string format to ...  Format (not implemented) Public function Getinsertdata ($strData) {//$bmap = "Jingdu, $jingdu Weidu, $weidu content, $content"; }//select in//arrdata integer array, preferably an integer public function select_in ($key, $arrData, $fields = ') {$fields = $fields? $fie    lds: ' * ';    Sort ($arrData);    $len = count ($arrData);    $cur = 0; $pre = $arrData [0];    $new = Array (' 0 ' = = Array ($arrData [0]));      for ($i = 1; $i < $len; $i + +) {if ($arrData [$i]-$pre) = = 1) {$new [$cur] = $arrData [$i];        } else {$cur = $i;      $new [$cur] = $arrData [$i];    } $pre = $arrData [$i];    } $ARRSQL = Array ();      foreach ($new as $v) {$len = count ($v)-1;        if ($len) {$s = $v [0];        $e = End ($v);      $sql = "(select $fields from {$this->_dt} where $key between $s and $e)";        } else {$s = $v [0];      $sql = "(select $fields from {$this->_dt} where $key = $s)";    } $ARRSQL [] = $sql;    } $strUnion = Implode (' UNION all ', $ARRSQL);    $res = $this->query ($strUnion);  return $this->rstoarray ($res);      //where in Public function Setwherein ($key, $arrData) {if (empty ($arrData)) {$str = "(' {$key} ' (' 0 '))";      $this->addwhere ($STR);    return $str;    } foreach ($arrData as & $v) {$v = "' {$v} '"; }    $str = implode (', ', $arrData);    $str = "(' {$key} ' in ({$str}))";    $this->addwhere ($STR);  return $this;    }//where in Public function Setwhere ($arrData) {if (empty ($arrData)) {return ';      } foreach ($arrData as $k + $v) {$str = "(' {$k} ' = ' {$v} ')";    $this->addwhere ($STR);  } return $this; }//between and Public function Setwherebetween ($key, $min, $max) {$str = "(' {$key} ' between ' {$min} ' and ' {$max} ')    ";    $this->addwhere ($STR);  return $this;    }//where a>b Public function setwherebt ($key, $value) {$str = "(' {$key} ' > ' {$value} ')";    $this->addwhere ($STR);  return $this;    }//where a<b Public function Setwherelt ($key, $value) {$str = "(' {$key} ' < ' {$value} ')";    $this->addwhere ($STR);  return $this;  }//assemble Where Condition public function addwhere ($where) {$this->arrwhere[] = $where; }//Get the Where condition for the final query public function Getwhere () {if (Empty ($this->arrwhere)) {return' Where 1 ';    } else {return ' where '. Implode (' and ', $this->arrwhere);    }}//comma-separated public function setfields ($fields) {$this->fields = $fields;  return $this;    }//ORDER BY a DESC public function Setorder ($order) {$this->arrorder[] = $order;  return $this;    }//Get Order statement public Function GetOrder () {if (Empty ($this->arrorder)) {return ';      } else {$str = implode (', ', $this->arrorder);    $this->order = "ORDER by {$str}";  } return $this->order;    }//e.g. ' 0, 10 '//When using limit, you can add where Condition optimization: SELECT ... where ID > 1234 limit 0, Public function setlimit ($limit) {    $this->limit = ' limit '. $limit;  return $this;    }//Query SQL statements directly, return array format public function arrquery ($sql, $field = ") {$this->query ($sql);    $this->clearquery ();    ($this->rs===false) && $this->error (' Select Error: '. $sql);  return $this->rstoarray ($field); }//If the $field is not empty, the returned result is indexed with the value of the field//the join Publi is not supportedC function Get ($field = ") {$where = $this->getwhere ();    $order = $this->getorder ();    $sql = "Select {$this->fields} from {$this->_dt} {$where} {$order} {$this->limit}";  return $this->arrquery ($sql, $field);    }//Get a record public function GetOne () {$this->setlimit (1);    $rs = $this->get (); Return!empty ($RS)?  $rs [0]: [];    }//Gets the value of one field of a record public function Getonefield ($field) {$this->setfields ($field);    $rs = $this->getone (); return!empty ($rs [$field])?  $rs [$field]: ';    }//Gets the value of all fields in the DataSet public Function GetFields ($field) {$this->setfields ($field);    $rs = $this->get ();    $result = [];    foreach ($rs as $v) {$result [] = $v [$field];    } unset ($rs);  return $result;    }//Clear query condition//Prevent interference next query public function Clearquery () {$this->fields = ' * ';    $this->arrwhere = [];    $this->order = ";    $this->arrorder = [];  $this->limit = ";  }//Disconnect the database connection public function close (){$this->link->close ();  }//Transaction//auto-commit switch Public function autocommit ($bool) {$this->link->autocommit ($bool);  }//Transaction completion commit public function commit () {$this->link->commit ();  }//Rollback public Function rollback () {$this->link->rollback ();  }//Output error SQL statement public Function error ($sql) {//if (is_test) {} exit ($sql); }}

The above is the entire content of this article, more relevant content please pay attention to topic.alibabacloud.com.