<?
// 方法
// Exce(sql); 直接执行SQL语句
// GetOne(id); 根据ID行的值
// GetContent(where,fields,order,page,limit);
// save(arr); 保存数据
// Insert(arr); 数组形式存入
// Update(arr); 数组形式存入带ID
// Deleteid(id); 根据ID 数组或者单个值 删除
// Delete(wherr); 根据条件删除
// Count(wherr); 根据条件获得记录数
// Insert_id(); 插入数据后的值
define("DBroot",dirname(__FILE__)."文件目录
define("DBname","DB/china"); //数据库名
define("DB_T","M_"); //表前缀
define("T_T","S_"); //字段前缀
$db=sqlite_open(DBname.".s3db") ;
sqlite_busy_timeout($db,300);
echo DBname.".s3db"."<br>";
class DB{
private $Conn; //数据库连接
public $Table; //数据库名
private $Mod_key; //增加数据用字段
private $Mod_value; //增加数据用的值
private $Updates; //更新数据用的字符串
private $Updateid; //更新数据用的ID
public $TID = "id"; //表主键
public $Bug = 1; //是否打印SQL语句
//构造函数
public function DB($tablename){
global $db;
$this->Conn=$db;
$this->Table=$tablename;
$this->Istable($tablename);
$this->Iskey($tablename);
}
//兼容PHP5
function __construct($tablename){
$this->DB($tablename);
}
//获取记录
public function GetContent($where="1=1",$fields="*",$order="desc",$page=0,$limit=10){
if (is_array($where)){
foreach($where as $key=>$value)
{$arr_where[]=T_T.$key."=".$value;}
$where=implode(" and ",$arr_where);
}
$order=$this->TID." ".$order;
$sql='select '.$fields.' from '.DB_T.$this->Table.' where '.$where.' order by '.$order.' limit '.$page.','.$limit;
$result=sqlite_query($this->Conn,$sql);
while($rs=sqlite_fetch_array($result)){
$arr[]=$rs;
}
return $arr;
}
//获取单条记录根据主键
public function GetOne($id){
$sql='select * from '.DB_T.$this->Table.' where '.$this->TID.'='.$id;
$result=sqlite_query($this->Conn,$sql);
return sqlite_fetch_array($result);
}
//保存数据
public function save($Item){
if(!is_array($Item)) $this->Halt("参数必须是一个数组！");
if (array_key_exists("id",$Item)){
if ($Item['id']=="")
{$this->Insert($Item);}
else
{$this->Update($Item);}
}
else
{$this->Insert($Item);}
}
//根据数组增加数据
public function Insert($Item){
if(!is_array($Item)) $this->Halt("参数必须是一个数组！");
$this->Insert_mod($Item);
$sql="insert into ".DB_T.$this->Table." (".$this->Mod_key.") values ('".$this->Mod_value."');";
$this->Exec($sql);
}
//根据数组更新
public function Update($Item){
if(!is_array($Item)) $this->Halt("参数必须是一个数组！");
$this->Update_mod($Item);
$sql="update ".DB_T.$this->Table." set ".$this->Updates." where ".$this->TID."=".$this->Updateid.";";
$this->Exec($sql);
}
//根据条件删除
public function Delete($where){
$sql="delete from ".DB_T.$this->Table." where ".$where.";";
$this->Exec($sql);
}
//根据ID删除
public function Deleteid($id){
$sql="delete from ".DB_T.$this->Table." where ".$this->TID." in(".$this->Deleteid_mod($id).");";
$this->Exec($sql);
}
private function Deleteid_mod($Input_Item){
if (is_array($Input_Item)){
foreach ($Input_Item as $key=>$value)
{$Array_value[] = $value;}
}
else
{$Array_value[]=$Input_Item;}
return implode(",",$Array_value);
}
private function Update_mod($Input_Item){
foreach ($Input_Item as $key=>$value){
if ($key=="id")
{$this->Updateid=$value;}
else
{$Array_value[] = T_T.$key."='".$value."'";}
}
$this->Updates=@implode(",",$Array_value);
}
private function Insert_mod($Input_Item){
foreach ($Input_Item as $key => $value)
{
if(empty($value))
{
$value=0;
}
$Array_key[] = T_T.$key;
$Array_value[] = $value;
}
$this->Mod_key = @implode(",",$Array_key);
$this->Mod_value = @implode("','",$Array_value);
}
//获取表主键
private function Iskey($table){
$sql = "PRAGMA table_info(".DB_T.$table.")";
$res=sqlite_query($this->Conn,$sql);
while($row=sqlite_fetch_array($res)){
if($row['pk']==1)
{$this->TID=$row['name'];}
else{
break;} // echo $row['name']."不是这个表的主键。";
}
}
//查询表是否存在
private function Istable($tablename)
{
$sql = "SELECT name FROM sqlite_master where type='table'";
$st = sqlite_query($this->Conn,$sql);
$tables = array();
while($row = sqlite_fetch_array($st)){
//while($row = $this->fetch($st)){
$tables[] = $row[0];
}
if(!in_array(DB_T.$tablename,$tables))
{$this->Halt("没有".$tablename."这个表！");}
}
private function fetch($result){
echo $result;
//return sqlite_fetch_array($result)；
}
//总数统计
public function Count($where){
$sql="select ".$this->TID." from ".DB_T.$this->Table." where ".$where;
$result=sqlite_query($this->Conn,$sql);
return sqlite_num_rows($result);
}
//错误提示
private function Halt($str){
exit("错误：".$str );
}
//执行单条语句
public function Exec($sql){
if ($this->Bug){echo "<br>".$sql;}
sqlite_query($this->Conn,$sql);
}
//获取插入后的值
public function Insert_id(){
return sqlite_last_insert_rowid($this->Conn);
}
}
$b=new DB("admin");
$a['username']="name";
$a['password']="";
//$a['count']=100;
$aaa['id']=2;
$aaa['username']=4;
$aaa['password']=6;
$aaa['count']=8;
$aa['id']=20;
$aa['username']="222444422222";
$aa['password']="444";
$aa['count']=222222;
$b->Insert($a);
echo $b->Insert_id();
//exit();
$b->Update($aa);
$b->Deleteid(20);
$b->Deleteid($aaa);
$c=array();
$c=$b->GetContent("id>10","*","asc",1,20);
$d=array();
$d=$b->GetOne(50);
echo "<br>";
echo "ID:".$d['id'];
echo "帐号:".$d['S_username'];
echo "密码:".$d['S_password'];
echo "统计:".$d['S_count'];
echo "<br>";
echo $b->save($a);
echo $b->save($aa);
echo $b->Count("id>40");
foreach($c as $list)
{
echo "ID:".$list['id'];
echo "帐号:".$list['S_username'];
echo "密码:".$list['S_password'];
echo "统计:".$list['S_count'];
echo "<br>";
}
?>