php简单MYSQL操作类

<?php
/*
简单MYSQL操作类

include './mysqlDb.php';

$mysql = null;
try {
    $mysql = new db('localhost', 'root', 'root', 'test');
    //SELECT
    $mysql->select('SELECT * FROM abc');
    //UPDATE
    $mysql->update('UPDATE `abc` SET `e` = 101 WHERE `id` =1');
    //INSERT
    $mysql->insert('INSERT INTO `abc` (`a`, `b`, `c`) VALUES (11, 22, 33)');
    //DELETE
    $mysql->delete('DELETE FROM `abc` WHERE `id` = 5');
} catch (Exception $e) {
    echo $e->getMessage();
    exit;
}
*/

class db
{
    private $_conn = null;

    public function __construct($ip, $user, $password, $db, $port = 3306)
    {
        try {
            $this->_conn = mysqlDb::instance()
                ->setIp($ip)
                ->setUser($user)
                ->setPassword($password)
                ->setDb($db)
                ->setPort($port)
                ->connection();
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }

    public function select($sql)
    {
        try {
            return $this->_conn->select($sql);
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }

    public function update($sql)
    {
        try {
            return $this->_conn->update($sql);
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }

    public function insert($sql)
    {
        try {
            return $this->_conn->insert($sql);
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }

    public function delete($sql)
    {
        try {
            return $this->_conn->delete($sql);
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }
}

class mysqlDb
{
    private static $_instance = NULL;
    private $_conn = '';
    private $_ip = '';
    private $_user = '';
    private $_password = '';
    private $_db = '';
    private $_port = 3306;

    public function setIp($ip)
    {
        $this->_ip = $ip;
        return $this;
    }

    public function setUser($user)
    {
        $this->_user = $user;
        return $this;
    }

    public function setPassword($password)
    {
        $this->_password = $password;
        return $this;
    }

    public function setDb($db)
    {
        $this->_db = $db;
        return $this;
    }

    public function setPort($port = 3306)
    {
        $this->_port = $port;
        return $this;
    }

    public static function instance()
    {
        if (self::$_instance === NULL) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    public function connection()
    {
        $this->_conn = new mysqli($this->_ip, $this->_user, $this->_password, $this->_db, $this->_port);

        /* check connection */
        if ($this->_conn->connect_errno) {
            $message = sprintf("Connect failed: %s", $this->_conn->connect_error);
            throw new Exception($message);
        }

        /* change character set to utf8 */
        if (!($this->_conn->set_charset("utf8"))) {
            $message = sprintf("Error loading character set utf8: %s", $this->_conn->error);
            throw new Exception($message);
        }
        return $this;
    }

    /**
     * 查询
     * @param string $sql SQL语句
     * @return array 返回二维数组
     */
    public function select($sql)
    {
        $resultArr = array();
        $sqlResult = $this->_query($sql);
        if ($sqlResult) {
            while ($row = $sqlResult->fetch_assoc()) {
                $resultArr[] = $row;
            }
        }
        return $resultArr;
    }

    /**
     * 修改
     * @param string $sql SQL语句
     * @return int 返回影响行数
     */
    public function update($sql)
    {
        $this->_query($sql);
        return $this->_conn->affected_rows;
    }

    /**
     * 添加
     * @param string $sql SQL语句
     * @return int 返回自增ID
     */
    public function insert($sql)
    {
        $this->_query($sql);
        return $this->_conn->insert_id;
    }

    /**
     * 删除
     * @param string $sql SQL语句
     * @return int 返回影响行数
     */
    public function delete($sql)
    {
        $this->_query($sql);
        return $this->_conn->affected_rows;
    }

    /**
     * 执行
     * @param string $sql SQL语句
     * @return 返回执行结果
     */
    private function _query($sql)
    {
        $result = $this->_conn->query($sql);
        if (!$result) {
            $message = 'Mysql Error Code：' . $this->_conn->errno . ', Error Message：' . $this->_conn->error;
            throw new Exception($message);
        }
        return $result;
    }
}