PHP完美封装Mysql的增删改查
usity发表于 2017-03-16 08:07:06
------第一步 config.php-------
|
1
2
3
4
5
6
|
<?php
$mysql_host = "127.0.0.1:3306";
$mysql_user="root";
$mysql_pwd="123456";
$mysql_db="b2c";
?>
|
------第二步 function.php-------
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
<?php
require_once 'config.php';
// 新增操作(字段必须齐全除自增id)
function insert($tableName, $columnArray) {
$con = mysql_connect($GLOBALS["mysql_host"], $GLOBALS["mysql_user"], $GLOBALS["mysql_pwd"]);
mysql_query("set names 'utf8' ");
mysql_query("set character_set_client=utf8");
mysql_query("set character_set_results=utf8");
if (!$con) {
return '连接错误';
} else {
mysql_select_db($GLOBALS["mysql_db"], $con);
$keys = array_keys($columnArray);
$values = array_values($columnArray);
$strk = implode(",", $keys);
$strv = implode("','", $values);
$sql = "INSERT INTO $tableName ($strk) VALUES ('$strv')";
echo $sql;
$flag = mysql_query($sql, $con);
mysql_close($con);
if ($flag) {
return true;
} else {
return false;
}
}
}
// 更新操作(字段可以不齐全)
function update($tableName, $columnArray, $id) {
$con = mysql_connect($GLOBALS["mysql_host"], $GLOBALS["mysql_user"], $GLOBALS["mysql_pwd"]);
mysql_query("set names 'utf8' ");
mysql_query("set character_set_client=utf8");
mysql_query("set character_set_results=utf8");
if (!$con) {
return '连接错误';
} else {
mysql_select_db($GLOBALS["mysql_db"], $con);
reset($columnArray);
$sets = array();
while (list($key, $value) = each($columnArray)) {
array_push($sets, "$key='$value'");
}
$str = implode(",", $sets);
$sql = "UPDATE $tableName SET $str WHERE id='" . mysql_real_escape_string($id) . "'";
echo $sql;
$flag = mysql_query($sql, $con);
mysql_close($con);
if ($flag) {
return true;
} else {
return false;
}
}
}
// 删除操作
function remove($tableName, $id) {
$con = mysql_connect($GLOBALS["mysql_host"], $GLOBALS["mysql_user"], $GLOBALS["mysql_pwd"]);
mysql_query("set names 'utf8' ");
mysql_query("set character_set_client=utf8");
mysql_query("set character_set_results=utf8");
if (!$con) {
return '连接错误';
} else {
mysql_select_db($GLOBALS["mysql_db"], $con);
$sql = "DELETE FROM $tableName WHERE id='" . mysql_real_escape_string($id) . "'";
echo $sql;
$flag = mysql_query($sql, $con);
mysql_close($con);
if ($flag) {
return true;
} else {
return false;
}
}
}
// 单条查询操作(字段可以不齐全)
function select($tableName, $columnArray, $id) {
$con = mysql_connect($GLOBALS["mysql_host"], $GLOBALS["mysql_user"], $GLOBALS["mysql_pwd"]);
mysql_query("set names 'utf8' ");
mysql_query("set character_set_client=utf8");
mysql_query("set character_set_results=utf8");
if (!$con) {
return '连接错误';
} else {
mysql_select_db($GLOBALS["mysql_db"], $con);
$keys = array_keys($columnArray);
$str = implode(",", $keys);
$sql = "SELECT $str FROM $tableName where id='" . mysql_real_escape_string($id) . "'";
$result = mysql_query($sql, $con);
$values = array();
$row = mysql_fetch_object($result);
mysql_close($con);
foreach ($keys as $key) {
array_push($values, $row -> $key);
}
return array_combine($keys, $values);
}
}
// 复杂查询操作(带分页,条件,排序,字段可以不齐全)#传$paramSql进来的时候记得先用mysql_real_escape_string过滤一下
function search($tableName, $currentPage, $pageSize, $order, $sort, $columnArray, $paramSql) {
$con = mysql_connect($GLOBALS["mysql_host"], $GLOBALS["mysql_user"], $GLOBALS["mysql_pwd"]);
mysql_query("set names 'utf8' ");
mysql_query("set character_set_client=utf8");
mysql_query("set character_set_results=utf8");
if (!$con) {
return '连接错误';
} else {
$orderSql = "";
if ($order && $sort) {
$orderSql = "order by $order $sort";
}
$limitSql="";
if($currentPage && $pageSize){
$limitSql="limit " . ($currentPage-1) * $pageSize . ",$pageSize";
}
mysql_select_db($GLOBALS["mysql_db"], $con);
$keys = array_keys($columnArray);
$str = implode(",", $keys);
$sql = "SELECT $str FROM $tableName $paramSql $orderSql $limitSql";
$result = mysql_query($sql, $con);
mysql_close($con);
$allresult = array();
while ($row = mysql_fetch_object($result)) {
$values = array();
foreach ($keys as $key) {
array_push($values, $row -> $key);
}
$obj = array_combine($keys, $values);
array_push($allresult, $obj);
}
return $allresult;
}
}
?>
|
------第三步 index.php-------
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
<!doctype html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="Generator" content="EditPlus®">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
<title>Document</title>
</head>
<body>
<style type="text/css">
table {
width: 95%;
padding: 0;
margin: 0;
border-collapse:collapse;
border-spacing:0;
}
th {
font: bold 11px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
color: #4f6b72;
border: 1px solid #C1DAD7;
letter-spacing: 2px;
text-transform: uppercase;
text-align: left;
padding: 5px 5px 5px 10px;
background: #CAE8EA url(images/bg_header.jpg) no-repeat;
}
td {
border: 1px solid #C1DAD7;
background: #fff;
font-size: 11px;
padding: 5px 5px 5px 10px;
color: #4f6b72;
word-wrap: break-word;
word-break: normal;
white-space: nowrap;
}
</style>
<?php
include 'function.php';
$tableName = "user";
$columnArray = array("id" => "", "name" => "", "age" => "", "sex" => "", "birth" => "");
$id = "5";
$currentPage = 1;
$pageSize = 10;
$order = "birth";
$sort = "desc";
$paramSql = null;
$list = search($tableName, $currentPage, $pageSize, $order, $sort, $columnArray, $paramSql);
?>
<table>
<tr><th>ID</th><th>姓名</th><th>年龄</th><th>性别</th><th>生日</th></tr>
<?php
foreach($list as $obj) {
echo "<tr><td>" . $obj['id'] . "</td><td>" . $obj['name'] . "</td><td>" . $obj['age'] . "</td><td>" . $obj['sex'] . "</td><td>" . $obj['birth'] . "</td></tr>";
}
?>
</table>
</body>
</html>
|
------第四步 效果图-------
转载于：http://www.tenfell.cn/post/8.html