<?php
/**
* name: sql操作封装，可扩展 .
* User: 张云山
* Date: 2016/9/4
* Time: 22:02
*/
//php文件编码设置
header('Content-type: text/html; charset=utf-8');
//数据库类
class sqled {
public function addTabase($sql_url,$sql_name,$sql_pss,$sql_DB){//创建数据库
$conn = new mysqli($sql_url,$sql_name,$sql_pss);
if(!$conn){
echo "连接失败<br>";
};
$sql = "CREATE DATABASE ".$sql_DB;
if($conn->query($sql) == TRUE){
return true;
}else{
echo $conn->error."<br>";
};
$conn -> close();
}
//示例（创建数据库）************/// addTabase($sql_url,$sql_name,$sql_pss,$sql_DB);
///*************************************************************************///
public function addTable($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//创建表以及字段
$conn = new mysqli($sql_url,$sql_name,$sql_pss,$sql_DB);
// 检测连接
if (!$conn) {
echo "连接失败";
};
$sql = "CREATE TABLE {$data['name']} (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
{$data['data']}
)";
/*
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
*/
if($conn->query($sql) == TRUE){
return true;
}else{
echo $conn->error."<br>";
};
$conn -> close();
}
//示例（创建表以及字段）************/// addTable($sql_url,$sql_name,$sql_pss,$sql_DB,array("name"=>"ceshi_biao","data"=>"
// test VARCHAR(30) NOT NULL
// "));
//***************************************************************************************///
public function addDB($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//添加字段数据
$conn = new mysqli($sql_url,$sql_name,$sql_pss,$sql_DB);
if(!$conn){
echo "连接失败";
};
$sql = "INSERT INTO {$data['name']} (
{$data["key"]}
)
VALUES (
{$data['value']}
)";
if($conn->query($sql) == TRUE){
return true;
}else{
echo $conn->error;
};
$conn -> close();
}
//示例（添加字段数据）************/// addDB($sql_url,$sql_name,$sql_pss,$sql_DB,array(
// "name"=>"ceshi_biao",
// "key"=>"zanIndex",
// "value"=>"0",
//));
///***********************************************************************************///
public function queryDB($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//读取数据
// 创建连接
$conn = new mysqli($sql_url,$sql_name,$sql_pss,$sql_DB);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
};
$sql = "SELECT {$data['data']} FROM {$data['name']}";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// 输出每行数据
$data_data = explode(",",$data['data']);
$key = '{';
$fh = ',';
for($i = 0 ; $i < count($data_data) ; $i++){
if($i == count($data_data)-1){
$fh = '';
};
$key = $key.'"'.$data_data[$i].'":"$row[\''.$data_data[$i].'\']"'.$fh;
};
$key = $key.'}';
$returnData = array();
while($row = $result->fetch_assoc()) {
$key1 = json_decode($key,true);
foreach($key1 as $keyName=>$value){
$value = "{".$value."}";
eval("\$value = \"$value\";");
$key1[$keyName] = $value;
};
array_push($returnData,$key1);
};
// echo $returnData;//执行回调，并返回查询结果
} else {
echo "0 个结果";
};
return $returnData;
$conn->close();
}
//示例（数据读取或数据查询）************/// queryDB($sql_url,$sql_name,$sql_pss,$sql_DB,array(
// "name" => "ceshi_biao",
// "data" => "id,zanIndex",
// ));
//******************************************************************///
public function updateDB($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//数据更新
$con=mysqli_connect($sql_url,$sql_name,$sql_pss,$sql_DB);
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
};
$sqlUpdate_set = null;
$sqlUpdate_WHERE = null;
//无条件设置，全部更新
$index = 0;
$fh = ",";
foreach ($data['newdata'] as $key => $value){
$index += 1;
if($index == count($data['newdata'])){
$fh = null;
};
$sqlUpdate_set = $sqlUpdate_set.$key."='".$value."'".$fh;
};
//有条件限制s
if(count($data["data"]) > 0 ){
$sqlUpdate_WHERE = "WHERE";
$fh1 = "AND";
$index1 = 0;
foreach ($data["data"] as $key=>$value){
$index1 += 1;
if($index1 == count($data['data'])){
$fh1 = null;
};
$sqlUpdate_WHERE = $sqlUpdate_WHERE." ".$key."='".$value."'".$fh1;
};
};
//有条件限制e
mysqli_query($con,"UPDATE {$data['name']} SET ".$sqlUpdate_set.$sqlUpdate_WHERE);
mysqli_close($con);
return true;
}
//示例(数据更新)************/// updateDB($sql_url,$sql_name,$sql_pss,$sql_DB,array(
// "name"=>"ceshi_biao",
// "newdata" => array(
// "zanIndex"=>"豆腐干发给豆腐",
// "id"=>"1",
// ),
// "data"=> array()//条件
// ));
//*****************************************************************///
public function deleteDB($sql_url,$sql_name,$sql_pss,$sql_DB,$data){//数据删除
$con=mysqli_connect($sql_url,$sql_name,$sql_pss,$sql_DB);
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
};
$result = mysqli_query($con,"DELETE FROM {$data['name']} WHERE {$data['data']}");
echo $result;
mysqli_close($con);
return true;
}
//示例(数据删除)************/// deleteDB($sql_url,$sql_name,$sql_pss,$sql_DB,array(
// "name"=>"ceshi_biao",
// ));
//*********************************************************************************//
//数据初始值赋值
public function aab($a,$b){
if($a){
$a = $a;
}else{
$a = $b;
};
return $a;
}
//*********************************************************************************//
//数据库操作类型
public static $sql_type = array(
"addTabase"=>"addTabase",//创建数据库
"addTable"=>"addTable",//创建表以及字段
"addDB"=>"addDB",//添加字段数据
"queryDB"=>"queryDB",//读取数据
"updateDB"=>"updateDB",//数据更新
"deleteDB"=>"deleteDB"//数据删除
);
//*********************************************************************************//
//sql综合调用方法；
public function fn($sql){
$sql['type'] = $this->aab($sql['type'],sqled::$sql_type['queryDB']);//默认数据库操作设置为（数据查询）
$sql['url'] = $this->aab($sql['url'],"127.0.0.1");
$sql['name'] = $this->aab($sql['name'],"root");
$sql['pss'] = $this->aab($sql['pss'],"admin");
$sql['db'] = $this->aab($sql['db'],"");
$sql['table'] = $this->aab($sql['table'],"");
$sql['array'] = $this->aab($sql['array'],array());
switch ($sql['type']){
case "addTabase";//创建数据库
return $this->addTabase($sql['url'],$sql['name'],$sql['pss'],$sql['db']);
break;
case "addTable";//创建表以及字段
return $this->addTable($sql['url'],$sql['name'],$sql['pss'],$sql['db'],$sql['array']);
break;
case "addDB";//添加字段数据
return $this->addDB($sql['url'],$sql['name'],$sql['pss'],$sql['db'],$sql['array']);
break;
case "queryDB";//读取数据
return $this->queryDB($sql['url'],$sql['name'],$sql['pss'],$sql['db'],$sql['array']);
break;
case "updateDB";//数据更新
return $this->updateDB($sql['url'],$sql['name'],$sql['pss'],$sql['db'],$sql['array']);
break;
case "deleteDB";//数据删除
return $this->deleteDB($sql['url'],$sql['name'],$sql['pss'],$sql['db'],$sql['array']);
break;
}
}
//示例(sql操作)*****************///
//$sql = new sqled();
//$q = $sql->fn(array(
// "db"=>sql_db,
// 'type'=>sqled::$sql_type['deleteDB'],
// "array"=>array(
// "name"=>"user",
// "data"=>"id",
// "newdata"=>array(
// "id"=>"5",
// "user" => "new-d"
// )
// )
//));
///******************************************************************************//
};
