PHP implements mysql (MySQL and MySQLi mode) operation class-PHP Tutorial-php.cn

This article mainly introduces the mysql operation class implemented by PHP, and analyzes the common method encapsulation and usage skills of MySQL and MySQLi to connect and operate MySQL database in the form of examples. Friends in need can refer to it

The example of this article describes the mysql operation class implemented by PHP. Share it with everyone for your reference, the details are as follows:

First is the mysql method

<?php
class ConnectionMySQL{
  //主机
  private $host="localhost";
  //数据库的username
  private $name="root";
  //数据库的password
  private $pass="";
  //数据库名称
  private $table="phptest";
  //编码形式
  private $ut="utf-8";
  //构造函数
  function __construct(){
    $this->ut=$ut;
    $this->connect();
  }
  //数据库的链接
  function connect(){
    $link=mysql_connect($this->host,$this->name,$this->pass) or die ($this->error());
    mysql_select_db($this->table,$link) or die("没该数据库：".$this->table);
    mysql_query("SET NAMES &#39;$this->ut&#39;");
  }
  function query($sql, $type = &#39;&#39;) {
    if(!($query = mysql_query($sql))) $this->show(&#39;Say:&#39;, $sql);
    return $query;
  }
  function show($message = &#39;&#39;, $sql = &#39;&#39;) {
    if(!$sql) echo $message;
    else echo $message.&#39;<br>&#39;.$sql;
  }
  function affected_rows() {
    return mysql_affected_rows();
  }
  function result($query, $row) {
    return mysql_result($query, $row);
  }
  function num_rows($query) {
    return @mysql_num_rows($query);
  }
  function num_fields($query) {
    return mysql_num_fields($query);
  }
  function free_result($query) {
    return mysql_free_result($query);
  }
  function insert_id() {
    return mysql_insert_id();
  }
  function fetch_row($query) {
    return mysql_fetch_row($query);
  }
  function version() {
    return mysql_get_server_info();
  }
  function close() {
    return mysql_close();
  }
  //向$table表中插入值
  function fn_insert($table,$name,$value){
    $this->query("insert into $table ($name) value ($value)");
  }
  //根据$id值删除表$table中的一条记录
  function fn_delete($table,$id,$value){
    $this->query("delete from $table where $id=$value");
    echo "id为". $id." 的记录被成功删除!";
  }
}
$db = new ConnectionMySQL();
$db->fn_insert(&#39;test&#39;,&#39;id,name,sex&#39;,"&#39;&#39;,&#39;hongtenzone&#39;,&#39;M&#39;");
$db->fn_delete(&#39;test&#39;, &#39;id&#39;, 1);
?>

Copy after login

mysqli way:

<?php
class Mysql{
  private $LocalHost = &#39;localhost&#39;;
  private $LoaclUser = &#39;&#39;;
  private $LocalPass = &#39;&#39;;
  private $LocalBase = &#39;&#39;;
  private $LocalCode = &#39;UTF8&#39;;
  private $PreFix;
  private $Conn;
  private $Start   = 0;
  private $Error   = false; //数据库连接状态, false表示未连接或连接不正常
  public $Err    = true; //Sql执行结果
  private $Table;
  private $Field   = &#39;*&#39;;
  private $Where   = &#39;&#39;;
  private $Order   = &#39;&#39;;
  private $PageSize = 0; //分页显示->每页多少条，0为不分页显示
  private $PageCount = 1; //分页显示->总共有多少条
  private $PageNum  = 1; //分页显示->总共有多少页
  private $PageNo  = 1; //分页显示->当前第几页
  private $PageKey  = &#39;page&#39;; //分页url参数键
  private $PageStart = 0; //分页显示->当前从第几条开始返回
  private $Select;
  private $Rest;
  private $Result  = false;//结果集
  public $FormArray = array();
  public $Instr_ID = 0;
  private $j = 0;
  public function Parameter($Loca, $Root, $Pass, $Base, $Code, $PreFix = &#39;&#39;){
    $this->LoaclUser = $Root;
    $this->LocalBase = $Base;
    $this->LocalCode = $Code;
    $this->LocalHost = $Loca;
    $this->LocalPass = $Pass;
    $this->PreFix  = $PreFix;
    return $this;
  }
  private function Connection( $Sql ){
    !function_exists(mysqli_connect) ? die(&#39;查询失败，无法加载mysqli扩展&#39;) : null;
    $this->Conn = @new mysqli( $this->LocalHost, $this->LoaclUser, $this->LocalPass, $this->LocalBase);
    $this->Error = mysqli_connect_errno() == 0 ? true : false;
    !$this->Error ? die(&#39;数据库连接错误，请检查数据库连接参数&#39;) : null;
    $this->Conn->query(&#39;SET NAMES &#39; . $this->LocalCode);
    $this->Rest = $this->Conn->query($Sql);
    $this->Err = mysqli_error($this->Conn);
    $this->Instr_ID = mysqli_insert_id($this->Conn);
    $this->Rest->free_result;
    $this->Conn->close;
    $this -> FormArray = &#39;&#39;;
    return $this;
  }
  public function null(){
    $this->PageSize = 0;
    //$this->PageCount = 1;
    $this->PageStart = 1;
    $this->Field   = &#39; * &#39;;
    $this->Select  = &#39;&#39;;
    unset($this->Table, $this->Where,$this->Order, $this->Result);
  }
  public function Table( $TableName ) {//数据表
   $this -> null();
   $this->Table = &#39;`&#39; . $this->PreFix . $TableName . &#39;`&#39;;
   return $this;
  }
  public function Field( $Array = &#39;*&#39; ) {//数据字段
   !empty( $this->Field ) ? $this->Field = &#39;&#39; : null;
   $Array = explode(&#39;,&#39;, $Array);
   foreach ( $Array as $field ) {
     $this->Field .= !$this->Start ? &#39;`&#39; . $field . &#39;`&#39; : &#39;, `&#39; . $field . &#39;`&#39;;
     $this->Start++;
   }
   $this->Start = 0;
   return $this;
  }
  public function Where( $Where ) {//条件
    $this->Where = &#39; where &#39; .$Where;
    return $this;
  }
  public function Order( $Order ) {//排序
   $this->Order = &#39; order by &#39; . $Order;
   return $this;
  }
  public function pk( $key ) {//分页url参数键
   $this->PageKey = $key;
   return $this;
  }
  public function Page( $PageSize ) {//分页
   $this->PageSize = $PageSize;
   $this->PageNo  = $this->get( $this->PageKey );
   $this->PageNo  = empty( $this->PageNo ) || !isset( $this->PageNo ) || !is_numeric( $this->PageNo ) || $this->PageNo < 1 ? 1 : $this->PageNo;
   return $this;
  }
  public function post( $Key, $Filter = true ){
    return $Filter ? strip_tags($_POST[$Key]) : $_POST[$Key];
  }
  public function get( $Key, $Filter = true ){
    return $Filter ? strip_tags($_GET[$Key]) : $_GET[$Key];
  }
  public function Sel(){
    $this->Select = &#39;Select &#39; . $this->Field . &#39; from &#39; . $this->Table . $this->Where . $this->Order;
    $this->Connection( $this->Select );
    if ( $this->Rest->num_rows ) {
     while ( $Rs = $this->Rest->fetch_assoc() ) {
       $this->Result[] = $Rs;
     }
    }
    $DataBase = $this->Result;
    return empty($DataBase) ? false : $DataBase;
  }
  public function querys( $Sql = &#39;&#39;, $Type = &#39;not&#39;, $biao = false ) {
    $this->Select = $Sql;
    $this->Connection( $this->Select );
    if ( $this->Rest->num_rows ) {
    if ( !$biao ) {
     while ( $Rs = $this->Rest->fetch_array() ) {
       $this->Result[] = !preg_match(&#39;/^\d+$/i&#39;, $Type) ? $Rs : $Rs[ $Type ];
     }
    } else {
     while ( $Rs = $this->Rest->fetch_assoc() ) {
       $this->Result[] = $Rs;
     }
    }
    }
    $DataBase = $this->Result;
    return empty($DataBase) ? false : $DataBase;
  }
  public function executes( $Sql = &#39;&#39; ){
    $this->Connection( $Sql );
    return $this->Rest;
  }
  public function exists( $T = &#39;&#39;, $F = &#39;&#39;, $W = &#39;&#39;){
    if ( empty( $F ) ) { return 0; }
    $cmd = empty( $W ) ? &#39;Select sum(&#39; . $F . &#39;) as `baiyinum` from `&#39; . $this->PreFix . $T .&#39;`&#39; : &#39;Select sum(&#39; . $F . &#39;) as `baiyinum` from `&#39; . $this->PreFix . $T .&#39;` Where &#39; . $W;
    $this->Connection( $cmd );
    unset( $T, $F, $W, $cmd );
    $Rel = $this->Rest->fetch_array();
    return round( $Rel[&#39;baiyinum&#39;], 2 );
  }
  public function ExistsTo( $Bili = 10000, $T = &#39;&#39;, $F = &#39;&#39;, $W = &#39;&#39;){
    if ( empty( $F ) ) { return 0; }
    $cmd = empty( $W ) ? &#39;Select sum(&#39; . $F . &#39;) as `baiyinum` from `&#39; . $this->PreFix . $T .&#39;`&#39; : &#39;Select sum(&#39; . $F . &#39;) as `baiyinum` from `&#39; . $this->PreFix . $T .&#39;` Where &#39; . $W;
    $this->Connection( $cmd );
    unset( $T, $F, $W, $cmd );
    $Rel = $this->Rest->fetch_array();
    return round( $Rel[&#39;baiyinum&#39;] * $Bili );
  }
  public function Select( $Type = true, $ListNum = 1 ){ //返回记录（数组形式， 返回条数）
   $this->Select = &#39;Select &#39; . $this->Field . &#39; from &#39; . $this->Table . $this->Where . $this->Order;
   if ( is_numeric( $ListNum ) ) {
    if ( $this->PageSize > 0 ) {
      $this->Connection( $this->Select );//执行查询
      $this->PageCount = $this->Rest->num_rows;//取得记录总数
      $this->PageNum  = ceil($this->PageCount / $this->PageSize); //总共有多少页
      $this->PageNo  = $this->PageNo > $this->PageNum ? $this->PageNum : $this->PageNo;
      $this->PageStart = ( $this->PageNo - 1 ) * $this->PageSize;  //当前从第几条开始返回
      $this->Select  .= &#39; limit &#39; . $this->PageStart . &#39;, &#39; .$this->PageSize; //重新构造sql语句
    } else {
      $this->Select  .= &#39; limit &#39; . $ListNum; //重新构造sql语句
    }
   } else {
      $this->Select  .= &#39; limit 1&#39;; //重新构造sql语句
   }
   //echo $this->Select;
   $this->Connection( $this->Select );//再次执行查询
   if ( $this->Rest->num_rows ) {//如果记录存在
    if ( $Type ) {
     while ( $Rs = $this->Rest->fetch_array() ) {
       $this->Result[] = $Rs;
     }
    }else{
     while ( $Rs = $this->Rest->fetch_assoc() ) {
       $this->Result[] = $Rs;
     }
    }
   }
   if ( ( $ListNum == 1 or !is_numeric( $ListNum ) ) && !$this->PageSize) { $this->Result = $this->Result[0]; }
   $DataBase = $this->Result;
   return empty($DataBase) ? false : $DataBase;
  }
  public function Num() { //返回记录总数
   $this->Select = &#39;Select &#39; . $this->Field . &#39; from &#39; . $this->Table . $this->Where . $this->Order;
   $this->Connection( $this->Select );//执行查询
   return $this->Rest->num_rows;//取得记录总数
  }
  public function PageNav($NumNav = false ) { //分页
   $Action = $this -> get(&#39;action&#39;);
   !empty( $Action ) or $Action = &#39;index&#39;;
   $Module = $this -> get(&#39;module&#39;);
   !empty( $Module ) or $Module = &#39;index&#39;;
   $NavUrl  = &#39;/&#39; . $Module . &#39;/&#39; . $Action . &#39;/&#39; . $this -> PageKey .&#39;/&#39;;
   $NaIndex = &#39;/&#39; . $Module . &#39;/&#39; . $Action;
   $PageHtml = "\n<p class=\"pagenav\">";
   $PageHtml .= &#39;<span>&#39; . $this->PageCount . &#39;条记录    &#39; . $this->PageNo . &#39;/&#39; . $this->PageNum . &#39;页</span>      &#39;;
   $this->PageNo <= 1 or $PageHtml .= "<a href=\"" . $NaIndex . "\">首页</a>\n<a href=\"" . $NavUrl . ($this->PageNo - 1) . "\">上一页</a>\n";
  if ( $NumNav ) { $PageHtml .= $this->NumPage($NavUrl); }
  $this->PageNo >= $this->PageNum or $PageHtml .= "<a href=\"" . $NavUrl. ($this->PageNo + 1) . "\">下一页</a>\n<a href=\"" . $NavUrl . $this->PageNum . "\">尾页</a>\n";
  $PageHtml .= "</p>\n";
  return $PageHtml;
  }
  private function NumPage( $Can = &#39;&#39; ) { //数字分页
    $NumHtml = &#39;&#39;;
    $First  = 1;
    $Last  = $this->PageNum;
    if ( $this->PageNum > 5 ) {
      if ( $this->PageNo < $this->PageNum ) {
       $First = $this->PageNo - 2;
       $Last = $this->PageNo + 2;
      }else{
       $First = $this->PageNo - 4;
       $Last = $this->PageNum;
      }
    }
    if ( $First < 1 ) { $First  = 1; $Last = $First + 4;}
    if ( $Last > $this->PageNum ) { $First  = $this->PageNum - 4; $Last = $this->PageNum;}
    for( $i = $First; $i <= $Last; $i++) {
      $NumHtml .= $this->PageNo != $i ? "\n\t" . &#39;<a href="&#39; . $Can . $i . &#39;" rel="external nofollow" >&#39; . $i . &#39;</a>&#39; . "\n\t" : "\n\t" .&#39;<a class="hover" disabled="disabled">&#39; . $i . &#39;</a>&#39; . "\n\t";
    }
    unset($Can, $First, $i, $Last);
    return $NumHtml;
  }
  public function UserPage($NumNav = false, $PageName = &#39;index&#39;, $Mulu = &#39;user&#39; ) { //会员中心分页
   $NavUrl  = &#39;/&#39; . $Mulu . &#39;/&#39; . $PageName . &#39;/&#39; . $this->PageKey . &#39;/&#39;;
   $PageHtml = "\n<p class=\"pagenav\">";
   $PageHtml .= &#39;<span>&#39; . $this->PageCount . &#39;条记录    &#39; . $this->PageNo . &#39;/&#39; . $this->PageNum . &#39;页</span>      &#39;;
   $this->PageNo <= 1 or $PageHtml .= "<a href=\"" . $NavUrl . "1\">首页</a>\n<a href=\"" . $NavUrl . ($this->PageNo - 1) . "\">上一页</a>\n";
  if ( $NumNav ) { $PageHtml .= $this->NumPage($NavUrl); }
  $this->PageNo >= $this->PageNum or $PageHtml .= "<a href=\"" . $NavUrl. ($this->PageNo + 1) . "\">下一页</a>\n<a href=\"" . $NavUrl . $this->PageNum . "\">尾页</a>\n";
  $PageHtml .= "</p><p class=\"clear\"></p>\n";
  return $PageHtml;
  }
  //表单处理开始
  //判断表单时候提交
  public function FormIs( $Keys = &#39;mm&#39; ) {
    return $_POST[ $Keys ] == 1 ? true : false;
  }
  //post方式获取数据
  public function _post( $Keys = &#39;&#39;, $TiHuan = &#39;&#39;) {
    $Values = strip_tags( $_POST[ $Keys ] );
    $this->FormArray[$Keys] = empty( $Values ) ? $TiHuan : $Values;
    return empty( $Values ) ? $TiHuan : $Values;
  }
  //get方法获取数据
  public function _get( $Keys = &#39;&#39;, $TiHuan = &#39;&#39;) {
    $Values = strip_tags( $_GET[ $Keys ] );
    return empty( $Values ) ? $TiHuan : $Values;
  }
  //判断是否为数字并且不小于0
  public function IsNum( $Num = 0, $Mesg = &#39;参数必须为数字&#39; ) {
    if ( is_numeric( $Num ) && !empty( $Num ) && $Num >= 0 ) {
      return $Num;
    }else{
      die( $Mesg );
    }
  }
  //判断是否为数字并且不小于0返回True/False
  public function NumBer( $Num = 0) {
     return is_numeric( $Num ) && !empty( $Num ) && $Num >= 0 ? true : false;
  }
  //检测相关数据似乎存在
  public function IsData($Types = true, $memg = &#39;数据已经存在&#39; ){
    $this->Connection(&#39;select &#39; . $this->Field . &#39; from &#39; . $this->Table . $this->Where);
    if ( $Types ){
      $this->Rest->num_rows > 0 ? die( $memg ) : null;
    } else {
      return $this->Rest->num_rows;
    }
  }
  //写入数据库记录
  public function into( $Mesg = &#39;&#39; ){
    !is_array( $this->FormArray ) ? die( $Mesg ) : null;
    $Sql = &#39;insert into &#39; . $this->Table . &#39; (`&#39;;
    $I  = 0;
    foreach ( $this->FormArray as $Key => $Val ){
      $Duan .= !$I ? $Key . &#39;`&#39; : &#39;, `&#39; . $Key . &#39;`&#39;;
      if ( is_numeric( $Val ) ){
        $Vals .= !$I ? $Val : &#39;, &#39; . $Val;
      }else{
        $Vals .= !$I ? &#39;\&#39;&#39; . $Val . &#39;\&#39;&#39; : &#39;, \&#39;&#39; . $Val . &#39;\&#39;&#39;;
      }
      $I++;
    }
    $Sql .= $Duan . &#39;) values (&#39; . $Vals . &#39;)&#39;;
    //@file_put_contents(&#39;1.sql&#39;, $Sql, FILE_APPEND);
    $this->Connection( $Sql );
    return !empty( $this->Err ) ? false : true;
  }
  //数组形式写入数据
  public function MsgBox( $Table = &#39;&#39;, $Filed = array() ) {
    $this -> Table($Table);
    foreach( $Filed as $Key => $Val ) {
     $this -> FormArray[ $Key ] = $Val;
    }
   return $this -> Into(&#39;未取得数据&#39;);
  }
  //修改数据库记录
  public function Edit( $Array = array() ) {
    if ( empty( $Array ) ) { $Array = $this -> FormArray; }
    if ( !is_array( $Array ) || empty( $Array ) ) {
      return false;
    } else {
     $Sql = &#39;update &#39; . $this -> Table . &#39; set &#39;;
     $I  = 0;
     $Sub = &#39;&#39;;
     $Huan = array(&#39;-&#39; => &#39;[jian]&#39;, &#39;+&#39; => &#39;[jia]&#39;, &#39;*&#39; => &#39;[cheng]&#39;, &#39;/&#39; => &#39;[chu]&#39;);
     $Zhan = array(&#39;[jian]&#39; => &#39;-&#39;, &#39;[jia]&#39; => &#39;+&#39;, &#39;[cheng]&#39; => &#39;*&#39;, &#39;[chu]&#39; => &#39;/&#39;);
     foreach ( $Array as $Files => $Val ) {
       $Val = !is_numeric( $Val ) && !preg_match(&#39;/\`\w+\`\s*(\+|\-|\*|\/)/i&#39;, $Val) ? &#39;\&#39;&#39; . $Val . &#39;\&#39;&#39; : $Val;
       foreach ( $Huan as $key => $val ){
         $Val = str_replace($key, $val, $Val);
       }
       $duan = !$I ? &#39;`&#39; . $Files . &#39;` = &#39; : &#39;, `&#39; . $Files . &#39;` = &#39;;
       $Sub .= $duan . $Val;
       $I++;
     }
     $Sql .= $Sub . $this -> Where;
     foreach ( $Zhan as $Fan => $Hui ) {
       $Sql = str_replace($Fan, $Hui, $Sql);
     }
     //echo $Sql; die;
    $this -> Connection( $Sql );
    unset( $Array, $duan, $Fan, $Files, $Huan, $Hui, $I, $key, $Sql, $Sub, $Val, $Zhan, $val );
    return !empty( $this -> Err ) ? false : true;
   }
  }
  //删除数据库记录
  public function del(){
    $Sql = &#39;delete from &#39; . $this->Table . $this->Where;
    $this->Connection( $Sql );
    unset($Sql);
    return !empty( $this->Err ) ? false : true;
  }
  //表单处理结束
  //页面跳转
  public function Msg( $Text = &#39;操作成功&#39; ) {
    echo &#39;<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />&#39;;
    echo &#39;<script type="text/javascript">
 <!--
  alert("&#39; . $Text . &#39;");
  document.location="&#39; . $_SERVER[&#39;HTTP_REFERER&#39;] . &#39;";
 //-->
</script>&#39;;
 exit;
  }
  #取得系统当前时间
  public function Times(){
    return str_replace(&#39;-&#39;, &#39;[jian]&#39;, date(&#39;Y-m-d H:i:s&#39;));
  }
  #取得用户IP地址
  public function GetIP(){
   if (getenv("HTTP_CLIENT_IP") && strcasecmp(getenv("HTTP_CLIENT_IP"), "unknown"))
    $ip = getenv("HTTP_CLIENT_IP");
    else if (getenv("HTTP_X_FORWARDED_FOR") && strcasecmp(getenv("HTTP_X_FORWARDED_FOR"), "unknown"))
    $ip = getenv("HTTP_X_FORWARDED_FOR");
    else if (getenv("REMOTE_ADDR") && strcasecmp(getenv("REMOTE_ADDR"), "unknown"))
    $ip = getenv("REMOTE_ADDR");
    else if (isset($_SERVER[&#39;REMOTE_ADDR&#39;]) && $_SERVER[&#39;REMOTE_ADDR&#39;] && strcasecmp($_SERVER[&#39;REMOTE_ADDR&#39;], "unknown"))
    $ip = $_SERVER[&#39;REMOTE_ADDR&#39;];
    else
    $ip = "unknown";
   return($ip);
  }
  //最后关闭数据库连接
  public function Close(){
    !is_object( $this -> Conn ) or mysqli_close( $this -> Conn );
  }
}

Copy after login

The above is the detailed content of PHP implements mysql (MySQL and MySQLi mode) operation class. For more information, please follow other related articles on the PHP Chinese website!