PHP 对MySQLI预处理的包装

mysql 类

<?php

class Mysql {

    private static $instance;
    private $link;
    private $query;
    private $stmt;
    private $param;

    // 初始化
    private function __construct() {
        $this->link = @new mysqli('localhost', 'root', 'chenshuo90909', 'temp');
        if(mysqli_connect_errno()) {
            echo "MySQL connect error!"; exit();
        }
        return $this->link;
    }

    // 单例模式
    public static function instance() {
        if(isset(self::$instance)){
            return self::$instance;
        } else {
            self::$instance = new self();
            return self::$instance;
        }
    }

    // 预处理SQL
    private function prepare($query) {
        $this->query = $query;
        $this->stmt = $this->link->prepare($this->query);
        if($this->stmt) {
            return $this->stmt;
        } else {
            echo "Stmt error!"; exit;
        }
    }

    // 值绑定
    private function bind_value($array) {
        $data = array();
        foreach ($array as $key => $value) {
            $data[$key] = &$array[$key];
        }
        return $data;
    }

    // 执行
    public function execute($query, $param) {
        $this->query = $query;
        $this->stmt = $this->link->prepare($this->query);
        $this->param = $param;
        call_user_func_array(array($this->stmt, 'bind_param'), $this->bind_value($this->param));    //绑定参数
        $result = $this->stmt->execute();
        var_dump($result);
    }

    // 返回单挑数据
    public function find($query, $param) {
        $this->query = $query;
        $this->param = $param;
        $this->stmt = $this->link->prepare($this->query);
        //绑定参数
        call_user_func_array(array($this->stmt, 'bind_param'), $this->bind_value($this->param));    
        $this->stmt->execute();
        
        $meta = $this->stmt->result_metadata();
        // 将结果绑定数组元素设置为引用状态
        while ($field = $meta->fetch_field()) {
            $parameters[] = &$row[$field->name];
        }
        //绑定结果
        call_user_func_array(array($this->stmt, 'bind_result'), $this->bind_value($parameters));
        
        while ($this->stmt->fetch()) {
            $result = $row;
        }
        
        return $result;
    }

    // 返回多条数据
    public function fetch($query, $param) {
        $this->query = $query;
        $this->param = $param;
        $this->stmt = $this->link->prepare($this->query);

        //绑定参数
        call_user_func_array(array($this->stmt, 'bind_param'), $this->bind_value($this->param));    
        $this->stmt->execute();
        
        $meta = $this->stmt->result_metadata();

        // 将结果绑定数组元素设置为引用状态
        while ($field = $meta->fetch_field()) {
            $parameters[] = &$row[$field->name];
        }

        //绑定结果
        call_user_func_array(array($this->stmt, 'bind_result'), $this->bind_value($parameters));

        // 有多行记录时将多行记录存入$results数组中.
        while ($this->stmt->fetch()) {
            $data = array();
            foreach ($row as $key => $value) {
                $data[$key] = $value;
            }
            $result[] = $data;
        }
        
        return $result;
    }

    // SQL语句调试
    public function debug() {
        return $this->query;
    }

    // 释放资源
    public function __destruct() {
        $this->stmt->close();
        $this->link->close();
    }


}

?>

应用：

<?php
include 'mysql.php';
class Data {

    const INSERT = "INSERT INTO user (username, password)VALUES(?, ?)";
    const UPDATE = "UPDATE user SET username = ? WHERE uid = ?";
    const SELECT = "SELECT username, password FROM user WHERE username = ? AND password = ?";
    const DELETE = "DELETE FROM user WHERE uid = ?";

    private $db;

    public function __construct() {
        $this->db = Mysql::instance();
    }

    public function insert($username, $password) {
        $query = self::INSERT;
        $param = array('ss',$username, $password);
        $result = $this->db->execute($query, $param);
        return $result;
    }

    public function delete($uid) {
        $query = self::DELETE;
        $param = array('i',$uid);
        $result = $this->db->execute($query, $param);
        return $result;
    }

    public function update($username, $uid) {
        $query = self::UPDATE;
        $param = array('si',$username, $uid);
        $result = $this->db->execute($query, $param);
        return $result;
    }

    public function select($username, $password) {
        $query = self::SELECT;
        $param = array('ss',$username, $password);
        $result = $this->db->find($query, $param);
        return $result;
    }

    public function find($username, $password) {
        $query = self::SELECT;
        $param = array('ss',$username, $password);
        $result = $this->db->find($query, $param);
        return $result;
    }

}

$data = new Data();

//$data->insert('chenshuox', 'chenshuo123');
//$data->delete(2);
//$data->update('tinys123', 1);

$result = $data->find('chenshuox', 'chenshuo123');
echo $result['username'];
echo $result['password'];

?>