PHP implementation with read-write separation function of MySQL class complete example _php skills

Source: Internet
Author: User
Tags php class rollback sql error string format

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.