This article describes the PHP implementation with read and write separation function of the MySQL class. Share to everyone for your reference, specific 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 corresponding to different instances, no more times New
The specific code is as follows:
<?php class Dbrwmysql {private static $Instance = null; Private $links = Array ();//link arrays private $link = null;
Current connection Public $dbType = ' read '; Public $_host= '; Database host name Public $_database = ';//Current database name public $_tablename = ';//The table name of the current table public $_dt = ';//database.tablename p Ublic $isRelease = 0;
Whether to release public $fields = ' * ' After the query is completed;
Public $arrWhere = [];
Public $order = ';
Public $arrOrder = [];
Public $limit = ';
Public $sql = '; Public $rs//result set Private function __construct ($database = ', $tablename = ', $isRelease =0) {$this->_database = $ Database;//database name $this->_tablename = $tablename;//table name $this->_dt = "' {$this->_database} '. '
{$this->_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 does not change and there is already a MySQL connection, no new connection is created//if the host changes, regenerate to an instance create a connection//type = = ' write ' or ' read ' Public function GetLink ($type)
{$this->dbtype = $ $type;
Randomly select a database connection (distinguish read and 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 namesUTF8MB4; ");
Support emoji expression $this->link->query ("Use {$this->_database};");
The 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 completes 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; //+//Insert multiple records/each record has the same field, but the value is not the same. 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;
//Get 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 returns////If field is not empty, the returned arrays $field the public function rstoarray ($field = ') {$arrRs = $this->r S->fetch_all (MYSQLI_ASSOC);
This function can only be used for PHP's MYSQLND drive $this->rs->free ()//release result set if ($field) {$arrResult = [];
foreach ($arrRs as $v) {$arrResult [$v [$field]] = $v;
return $arrResult;
return $arrRs;
///Add inverted quotes to field names 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 inbound data, converting 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 integer public function select_in ($key, $arrData, $fields = ') {$fields = $fields ?
$fields: ' * ';
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 the 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 ') {$ma
x} ');
$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;
}//assembly Where condition Public function addwhere ($where) {$this->arrwhere[] = $where;
///Get the Where Condition public function getwhere () {if (Empty ($this->arrwhere)) of the final query {return ' where 1 ';
else {return ' where '. Implode (' and ', $this->arrwhere);
}///comma-delimited public function setfields ($fields) {$this->fields = $fields;
return $this;
Desc Public Function Setorder ($order) {$this->arrorder[] = $order;
return $this;
//Get the 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 limit can be added where condition optimization: SELECT ... where ID > 1234 limit 0, Public function setlimit ($limit) {$this->limit= ' limit '. $limit;
return $this;
//Direct query SQL statement, 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 result returned is indexed with the value of the field//temporarily does not support join public 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 a field 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 ();
The//transaction completes submitting 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);
}
}
More about PHP Interested readers can view the site topics: "PHP+MYSQLI Database Programming Skills Summary", "PHP based on PDO Operation Database Skills summary", "PHP operation and operator Usage Summary", "PHP Network Programming Skills Summary", " Introduction to PHP object-oriented programming, "PHP string (String) Usage Summary", "Php+mysql Database Operations Introduction Tutorial" and "PHP Common database Operation tips Summary"
I hope this article will help you with the PHP program design.