PHP之封装MySQL类

config.inc.php内容如下

<?php
return array(
    'DB_HOST' => '192.168.188.134',
    'DB_NAME' => 'scoreboard', 
    'DB_USER' => 'score',
    'DB_PASS' => '123456',
    'DB_CHARSET' => 'utf8',
    'IS_LOG' => 1,//开启日志
    'LOGFILEPATH' => '../log.txt'//日志路径
);
/*
    $database = require('./config.php');
    echo $database['DB_TYPE'];   //输出'DB_TYPE'
*/

?>

表设计如下

create database scoreboard;
use scoreboard;

create table users(
id int not null auto_increment primary key,
gid int not null,                --组id
username varchar(20) not null,
password varchar(32) not null,
sex varchar(2) not null,
totalscore int  --个人总积分
);

create table share(
id int not null auto_increment primary key,
uid int not null,
content varchar(1024) not null,  --分享内容
comment varchar(1024) not null,   --点评
date varchar(15) not null       --分享日期
);

create table score(
id int not null auto_increment primary key,
uid int not null,               --用户id
score int not null,             --用户单次积分
);

grant all privileges on scoreboard.* to 'score'@'%' identified by '123456';
flush privileges;

封装类如下

<?php

class mysql {
    private $logfilepath;
    private $is_log;
    private $hlog;
    private $conn;

    //构造函数
    public function __construct()
    {
        $config = include_once(dirname(__FILE__)."/../config/config.inc.php");
        $this->is_log = $config['IS_LOG'];
        $this->logfilepath = $config['LOGFILEPATH'];

        if ($this->is_log){
            $handle = fopen($this->logfilepath,"a+");
            $this->hlog = $handle;
        }

        $this->conn = $this->connect($config['DB_HOST'],$config['DB_USER'],$config['DB_PASS'],$config['DB_NAME'],$config['DB_CHARSET']);
    }

    //连接数据库
    public function connect($dbhost, $dbuser, $dbpass, $dbname, $dbcharset)
    {
        $this->conn = @mysql_connect($dbhost,$dbuser,$dbpass);
        if (!$this->conn) {
            $msg = "连接数据库失败：".mysql_error();
            $this->write_log($msg);
            die($msg);
        } else {
            if (!@mysql_select_db($dbname)) {
                $msg = "连接数据库成功，但选择数据库失败：".mysql_error();
                $this->write_log($msg);
                die($msg);
            } else {
                $msg = "连接数据库成功，且选择数据库成功";
                $this->write_log($msg);
            }
        }

        @mysql_query("set names ".$dbcharset);

    }

    //执行语句
    public function query($sql){
        
        $result = @mysql_query($sql);

        if (!$result) {
            $this->write_log('mysql_query error:'.mysql_error());
        } else {
            $this->write_log('执行语句：'.$sql.' 且执行成功');
        }
        return $result;
    }

    //查询一条数据
    public function select_one($tab,$column = "*",$condition = '',$debug=False)   //查询函数
    {
        $condition = $condition ? ' where ' . $condition : NULL;
        $sql = "select $column from $tab $condition ";
        if ($debug) {
            echo '将执行语句：'.$sql.'<br />';
        } else {
            $result = $this->query($sql);
            $row = @mysql_fetch_assoc($result);
            return $row;
        }
    }

    //查询多条数据
    public function select_more($tab,$column = "*",$condition = '',$debug=False)   //查询函数
    {
        $condition = $condition ? ' where ' . $condition : NULL;
        $sql = "select $column from $tab $condition";
        if ($debug) {
            echo '将执行语句：'.$sql;
        } else {
            $result = $this->query($sql);
            $i = 0;
            $rows = array();
            while ($row = @mysql_fetch_assoc($result)) {
                $rows[$i] = $row;
                // print_r($rows[$i]);
                $i++; 
            }
            return $rows;
        }
    }

    //返回结果集
    public function echo_result($tab,$column = "*",$condition = '',$debug=False)   //查询函数
    {
        $condition = $condition ? ' where ' . $condition : NULL;
        $sql = "select $column from $tab $condition ";
        if ($debug) {
            echo '将执行语句：'.$sql.'<br />';
        } else {
            return $this->query($sql);
        }
    }

    //插入数据
    public function insert($tab,$arr,$debug=False)
    {
        $value = '';
        $column = '';
        foreach ($arr as $k => $v) {
            $column .= ",{$k}";
            $value .= ",'{$v}'";
        }
        $column = substr($column, 1);
        $value = substr($value, 1);

        $sql = "insert into $tab($column) values($value)";
        if ($debug) {
            echo '将执行语句：'.$sql;
        } else {
            $this->query($sql);
            $num = $this->affected_num();
            $this->write_log("受影响行数：".$num);
            return $num;    //返回受影响行数
        }
    }

    //获取最后插入的id
    public function insert_id() {
        $id = mysql_insert_id($this->link_id);
        $this->write_log('最后插入的id为：'.$id);
        return $id;
    }

    //更新数据
    public function update($tab,$arr,$condition = '',$debug=False)
    {
        if (!$condition) {
            die("error".mysql_error());
        } else {
            $condition = 'where ' . $condition;
        }
        
        $value = '';
        foreach ($arr as $k => $v) {
            $value .= "{$k}='{$v}',";
        }
        $value = substr($value,0,-1);

        $sql = "update $tab set $value $condition";
        if ($debug) {
            echo '将执行语句：'.$sql;
        } else {
            $this->query($sql);
            $num = $this->affected_num();
            $this->write_log("受影响行数：".$num);

            return $num;            
        }
    }

    //删除数据
    public function delete($tab,$condition='',$debug=False)
    {
        $condition = $condition ? ' where ' . $condition : NULL;
        $sql = "delete from $tab $condition";
        if ($debug) {
            echo '将执行语句：'.$sql;
        } else {
            $this->query($sql);
            $num = $this->affected_num();
            $this->write_log("受影响行数：".$num);
            return $num;    //返回受影响行数
        }
    }

    //返回受影响行数
    public function affected_num()
    {
        $num = @mysql_affected_rows();
        return $num;
    }

    //写入日志
    public function write_log($msg='')
    {
        if ($this->is_log){
            $text = date("Y-m-d H:i:s")." ".$msg."\r\n";
            fwrite($this->hlog,$text);
        }
    }

    //关闭数据库连接
    public function close()
    {  
        mysql_close($this->conn);
    }

    //析构函数
    public function __destruct()
    {
        if($this->is_log){
            fclose($this->hlog);
        }
    }
}


    //$db = new mysql();
    
    // //select_one($tab,$column = "*",$condition = '')
    // $rows = $db->select_more('share','*');
    // print_r($rows[0]);
    // print_r($rows[1]);


    // //insert($tab,$arr)
    // $arr = array();
    // $arr['uid'] = '3';
    // $arr['content'] = 'xss';
    // $arr['comment'] = 'very good';
    // $arr['date'] = '1464082630';
    // $db->insert('share',$arr);


    // //update($tab,$arr,$condition = '')
    // $arr = array();
    // $arr['content'] = 'xssxssxssxssxss';
    // $arr['comment'] = 'goodgoodgoodgood';
    // $condition = 'id > 5';
    // $db->update('share',$arr,$condition);


    //$db->delete("share","id between 10 and 15");


    //$db->close();

?>