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);