SQLite3数据库类

SQLite3数据库操作类


<?php

class db{

    public $db;

    // 初始化
    function __construct($dbname){
        $this->db = new SQLite3($dbname);
    }

    // 创建表
    function create_table($array){
        if(is_array($array)){
            $i = true;
            foreach($array as $v){
                $b = $this->db->exec($v);
                if(!$b){
                    $i = false;
                }
            }
            if($i){
                return true;
            }else{
                return false;
            }
        }
    }

    // 插入数据
    function insert($table, $data, $type = ''){
        $key = '';
        $val = '';
		$i = 0;
        foreach($data as $k=>$v){
			if(count($type) == count($data)){
				$v = $this->format($v, $type[$i]);
			}
            $key .= "'".$k."', ";
            $val .= "'".$v."', ";
			$i++;
        }
        $key = substr($key, 0, -2);
        $val = substr($val, 0, -2);
        $sql = "INSERT INTO ".$table."(".$key.") VALUES(".$val.")";
        $exec = $this->db->exec($sql);
        if($exec){
            return $this->db->lastInsertRowID();
        }else{
            return false;
        }
    }

	// 删除数据
	function delete($table, $data, $type = ''){
		$where = '';
		$i = 0;
		foreach($data as $k=>$v){
			if(count($type) == count($data)){
				$v = $this->format($v, $type[$i]);
			}
			$where .= $k.'='.$v.' and ';
			$i++;
		}
		$where = substr($where, 0, -5);
		$sql = "DELETE FROM ".$table." WHERE ".$where;
		if($this->db->exec($sql)){
			return true;
		}else{
			return false;
		}
	}

	// 更新数据
	function update($table, $data, $type = ''){

	}

    // 获取全部结果集
    function result($sql, $type = 0){
        $result = array();
		$types = array(SQLITE3_ASSOC, SQLITE3_NUM, SQLITE3_BOTH);
        $query = $this->db->query($sql);
        while($r = $query->fetchArray($types[$type])){
            $result[] = $r;
        }
        return $result;
    }

    // 获取第一个结果
    function first($sql){
        return $this->db->querySingle($sql);
    }

	// 格式化
	function format($data, $type){
		switch ($type){
			case '%d':
				$data = intval($data);
				break;
			case '%s':
				$data = htmlspecialchars($data);
				break;
		}
		return $data;
	}

    // 错误信息
    function error($show = false){
        if($show){
            return 'Error '.$this->db->lastErrorCode().' : '.$this->db->lastErrorMsg();
        }
    }
}

判断支持的SQLite版本:



if(extension_loaded('sqlite3')){
    // 支持SQLite3
    require('sqlite3.class.php');
}elseif(extension_loaded('sqlite')){
    // 支持SQLite
    require('sqlite.class.php');
}else{
    // 不支持SQLite
    exit('Not support SQLite!');
}

如何使用:


$db = new db('mogu.db');

// 创建表
$array = array(
    "CREATE TABLE IF NOT EXISTS users ( uid INTEGER NOT NULL CHECK (uid>= 0) DEFAULT 0, name VARCHAR(60) NOT NULL UNIQUE DEFAULT '', pass VARCHAR(128) NOT NULL DEFAULT '', mail VARCHAR(254) NOT NULL UNIQUE DEFAULT '');",
    "CREATE TABLE IF NOT EXISTS users_status ( uid INTEGER NOT NULL DEFAULT 0, status INTEGER NOT NULL DEFAULT 0);"
);

// $result = $db->create_table($array);

$data = array(
    'name'=>'mogu12122<script>',
    'pass'=>'123aaa456',
    'mail'=>'mogu1222<111>@mogu.cn',
);

$type = array('%s', '%d', '%s');

$result = $db->insert('users', $data, $type);

// Delete

$data = array(
	'uid'=>'0',
	'pass'=>'123aaa456',
);

$type = array('%s', '%d');

$result = $db->delete('users', $data, $type);



$sql = "select * from users";

$result = $db->result($sql);

echo '<pre>';
var_dump($result);

发表评论