【php】使用mysqli完成的一个数据库操作类

来源：互联网发布：windows date命令编辑：程序博客网时间：2024/05/21 17:07

<?phpclass lib_mysqli {protected $mysqli;//mysqli实例对象public $sql;//sql语句protected $rs;//结果集protected $query_num= 0;//执行次数protected $fetch_mode= MYSQLI_ASSOC;//获取模式protected $cache;//缓存类对象protected $reload     = false;//是否重新载入protected $cache_mark = true;//缓存标记//构造函数：主要用来返回一个mysqli对象public function  __construct($dbhost, $dbuser, $dbpass, $dbname, $dbport) {$this->mysqli    = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport);if(mysqli_connect_errno()) {$this->mysqli    = false;echo '<h2>'.mysqli_connect_error().'</h2>';die();} else {$this->mysqli->set_charset("utf8");}}//缓存类对象：文件缓存、memcache键值对缓存public function cache_obj($cache) {$this->cache= $cache;}//析构函数：主要用来释放结果集和关闭数据库连接public function  __destruct() {$this->free();$this->close();}//释放结果集所占资源protected function free() {@$this->rs->free();}//关闭数据库连接protected function close() {$this->mysqli->close();}//获取结果集protected function fetch() {return $this->rs->fetch_array($this->fetch_mode);}//获取查询的sql语句protected function get_query_sql($sql, $limit = null) {if (@preg_match("/[0-9]+(,[ ]?[0-9]+)?/is", $limit) && !preg_match("/ LIMIT [0-9]+(,[ ]?[0-9]+)?$/is", $sql)) {$sql .= " LIMIT " . $limit;}return $sql;}//从缓存中获取数据protected function get_cache($sql,$method) {$cache_file    = md5($sql.$method);$res    = $this->cache->get($cache_file);if(!$res) {//如果缓存文件过期或不存在的话，返回false；如果缓存文件存在且未过期的话，则返回缓存数据$res    = $this->$method($sql);//先从缓存中取数据，如果缓存中没数据，则从数据库中取数据if($res && $this->cache_mark && !$this->reload) {$this->cache->set($cache_file, $res);//如果缓存文件过期或不存在的话，将重新将从数据库中查询的数据放入缓存文件}}return $res;}//获取查询次数public function query_num() {return $this->query_num;}//执行sql语句查询public function query($sql, $limit = null) {$sql    = $this->get_query_sql($sql, $limit);$this->sql[]    = $sql;$this->rs    = $this->mysqli->query($sql);if (!$this->rs) {echo "<p>error: ".$this->mysqli->error."</p>";echo "<p>sql: ".$sql."</p>";die();} else {$this->query_num++;return $this->rs;}}//返回单条记录的单个字段值public function get_one($sql) {$this->query($sql, 1);$this->fetch_mode    = MYSQLI_NUM;$row = $this->fetch();$this->free();return $row[0];}//缓存单个字段public function cache_one($sql, $reload = false) {$this->reload= $reload;$sql    = $this->get_query_sql($sql, 1);return $this->get_cache($sql, 'get_one');}//获取单条记录public function get_row($sql, $fetch_mode = MYSQLI_ASSOC) {$this->query($sql, 1);$this->fetch_mode    = $fetch_mode;$row = $this->fetch();$this->free();return $row;}//缓存行public function cache_row($sql, $reload = false) {$this->reload= $reload;$sql    = $this->get_query_sql($sql, 1);return $this->get_cache($sql, 'get_row');}//返回所有的结果集public function get_all($sql, $limit = null, $fetch_mode = MYSQLI_ASSOC) {$this->query($sql, $limit);$all_rows = array();$this->fetch_mode    = $fetch_mode;while($rows = $this->fetch()) {$all_rows[] = $rows;}$this->free();return $all_rows;}//缓存allpublic function cache_all($sql, $reload = false, $limit = null) {$this->reload= $reload;$sql    = $this->get_query_sql($sql, $limit);return $this->get_cache($sql, 'get_all');}//返回前一次mysql操作所影响的记录行数public function affected_rows() {return $this->mysqli->affected_rows;} /** * 获取插入语句     *     * @param    string     $tbl_name   表名     * @param    array      $info       数据     */    public function get_insert_db_sql($tbl_name,$info){//首先判断是否为数组，再判断数组是否为空        if(is_array($info)&&!empty($info))        {            $i = 0;            foreach($info as $key=>$val)            {                $fields[$i] = $key;//将所有的键名放到一个$fields[]数组中                $values[$i] = $val;//将所有的值放到一个$values[]数组中                $i++;            }            $s_fields = "(".implode(",",$fields).")";            $s_values  = "('".implode("','",$values)."')";            $sql = "INSERT INTO                        $tbl_name                        $s_fields                    VALUES                        $s_values";            Return $sql;        }        else        {            Return false;        }    }            /**     * 获取替换语句:replace into是insert into的增强版     * 区别：replace into跟insert功能类似，不同点在于：replace into 首先尝试插入数据到表中，如果发现表中 已经有此行数据(根据主键或唯一索引判断)，则先删除此行数据，然后插入新的数据，否则直接插入新数据     * @param    string     $tbl_name   表名     * @param    array      $info       数据     */    public function get_replace_db_sql($tbl_name,$info)    {        if(is_array($info)&&!empty($info))        {            $i = 0;            foreach($info as $key=>$val)            {                $fields[$i] = $key;                $values[$i] = $val;                $i++;            }            $s_fields = "(".implode(",",$fields).")";            $s_values  = "('".implode("','",$values)."')";            $sql = "REPLACE INTO                        $tbl_name                        $s_fields                    VALUES                        $s_values";            Return $sql;        }        else        {            Return false;        }    }      /**     * 获取更新SQL语句     *     * @param    string     $tbl_name   表名     * @param    array      $info       数据     * @param    array      $condition  条件     */    public function get_update_db_sql($tbl_name,$info,$condition)    {        $i = 0;        $data = '';        if(is_array($info)&&!empty($info))        {            foreach( $info as $key=>$val )            {                if(isset($val))                {                    $val = $val;                    if($i==0&&$val!==null)                    {                        $data = $key."='".$val."'";//第一次：如，update 表名 set username='admin'                    }                    else                    {                        $data .= ",".$key." = '".$val."'";//非第一次：如， ，password='123'                    }                    $i++;                }            }            $sql = "UPDATE ".$tbl_name." SET ".$data." WHERE ".$condition;            return $sql;        }        else        {            Return false;        }    }        /**     * 取得数据库最后一个插入ID     *     * @return int     */    public function last_id() {        return mysqli_insert_id($this->mysqli);    }        public function real_get($sql, $fetch_mode = MYSQLI_ASSOC) {$this->query($sql);$this->fetch_mode    = $fetch_mode;$row = $this->fetch();$this->free();return $row;}}

在model中使用：

$tbl_product = TBL_PRE . "products";$tbl_goods = TBL_PRE . "goods";    $tbl_brand = TBL_PRE . "goods_brand";    $tbl_category = TBL_PRE . "goods_category";    $tbl_type = TBL_PRE . "goods_type";    $tbl_spec = TBL_PRE ."goods_specification";    $sql = " SELECT a.*, b.brand_name, b.brand_name_second,b.brand_logo,b.brand_simple_intro, b.url_path,c.category_name,c.url_path AS cate_url_path, sp.spec_name, sp.spec_memo      FROM   (SELECT g.*,p.product_id, p.bn, p.bar_code, p.color_id, p.color_text,p.small_list_page_product_pic_default,  p.small_list_page_product_pic_replace,p.is_new, p.is_promotion,p.is_newstyle,p.is_limit,p.is_classic,p.is_new_spring,p.act_id,   p.product_name, p.price as product_price, p.market_price as product_market_price, p.intro,   p.editor_intro,p.size_image_path, p.size_intro, p.video_path, p.size_image_name,    p.status as pstatus, p.is_stop as pisstop     FROM $tbl_product p     LEFT JOIN $tbl_goods g ON p.goods_id = g.goods_id     WHERE p.product_id = $id $_filter) a      LEFT JOIN $tbl_brand b ON a.brand_id = b.brand_id     LEFT JOIN $tbl_category c ON a.category_id = c.category_id     LEFT JOIN $tbl_spec sp ON a.spec_id = sp.spec_id";$result['base'] = $this->db->cache_row($sql);