数据库封装 sql server mysql_mysql数据库封装

/**

* 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 "连接失败
";

};

$sql = "CREATE DATABASE ".$sql_DB;

if($conn->query($sql) == TRUE){

return true;

}else{

echo $conn->error."
";

};

$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."
";

};

$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"

// )

// )

//));

///******************************************************************************//

};