PHP mysql数据库面向对象简单操作类(增删改查)、分页类和图片验证码类的封装

PHP mysql数据库面向对象简单操作类(增删改查)、分页类和图片验证码类的封装

这篇文章中已经介绍了PHP连接数据库的方法以及最基本的对数据库的操作,但并没有实现模块化,所有的代码全都集中在表现页面中,造成了代码冗余,不仅不利于维护也不利于代码的重用性,那么在这篇文章中将用面向对象的知识对数据库的连接和基本操作方法进行封装,也简单地对地分页功能进行了封装,这样就大大避免了代码的重复。

以下文件放置于space文件夹中

一、封装简单的数据库的增删改查等函数

libs/Db文件夹

1、先写一个db.config.php文件来使用里面是你数据库的信息

1
2
3
4
5
6
7
8
9
<?php
return array(
'host' => 'localhost',
'user' => 'root',
'password' => 'root',
'port' => '3306',
'dbname' => 'test',
'charset' => 'utf8'
);

2、通过Idb.class.php文件来定义接口

1
2
3
4
5
6
7
8
9
10
<?php
namespace libs\Db; //引入命名空间
interface Idb {
public function add();
public function getOne();
public function getList();
public function update();
public function delete();
public function count();
}

什么是命名空间

3、通过Mysql.class.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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
<?php
namespace libs\Db;
class Mysql implements Idb {
private $_link = NULL;
private $_debug = false;
private $_where = NULL;
private $_table = NULL;
private $_data = NULL;
private $_fields = NULL;
private $_limit=NULL;
private $_order = NULL;
private static $_instance = NULL;

private function __construct()
{
if ( $this->_link == NULL )
{
$this->connect();
}
}

public static function init()
{
if ( self::$_instance == NULL )
{
self::$_instance = new self();
}
return self::$_instance;
}

private function excute($sql) //执行sql语句
{
if ( $this->_debug == true )
{
echo "EXCUTE SQL: {$sql} <br/>";
}
$ret = $this->_link->query($sql);
return $ret;
}

public function setDebug( $debug = true ) //调试函数
{
$this->_debug = $debug;
}

private function connect() //连接数据库
{
$conf = require_once 'db.config.php';
$this->_link = new \mysqli($conf['host'], $conf['user'], $conf['password'], $conf['dbname'],$conf['port']);
if ($this->_link->connect_error) {
die('Connect Error (' . $this->_link->connect_errno . ') '
. $this->_link->connect_error);
}
date_default_timezone_set('PRC'); //设时区
$this->_link->query("SET NAME {$conf['charset']}");
}

public function table( $table ) //获取表
{
$this->_table = $table;
return $this;
}

public function where($where= NULL)
{
$this->_where = $where;
return $this;
}

public function data($data)
{
$this->_data = $data;
return $this;
}

public function field($fields){
$this->_fields = $fields;
return $this;
}

public function order($order){
$this->_order = $order;
return $this;
}

public function limit($limit){
$this->_limit = $limit;
return $this;
}

public function add() //添加数据
{
$sql = "INSERT INTO {$this->_table} (";
$fields = "";
$values = "";
foreach( $this->_data as $key => $val )
{
if ( $fields == "" ) $fields = $key;
else $fields .= ",".$key;
if ( $values == "" ) $values = "'{$val}'";
else $values .= ","."'{$val}'";
}
$sql .= "{$fields} )VALUES({$values})";
$ret = $this->excute($sql);
return $ret;
}

public function getOne() //获取一组数据
{
//select id,name from table where id = 10
if ( is_array($this->_fields) ) $fields = implode(',', $fields);
$sql = "SELECT {$this->_fields} FROM {$this->_table}";
if ( $this->_where != NULL ) $sql .= " WHERE {$this->_where}";
$ret = $this->excute($sql); //select 操作query 返回的是结果集
if ( $ret == false ) return false;
$row = $ret->fetch_assoc();
if ( empty($row) ) return false;
return $row;
}

public function getList() //获取多组数据
{
//select id,name from table where id > 10
//若查询字段为一维数组,以,拼接为目标字符串
if ( is_array($this->_fields) ) $this->_fields = implode(',', $this->_fields);
$sql = "SELECT {$this->_fields} FROM {$this->_table}";
if ( $this->_where != NULL ) $sql .= " WHERE {$this->_where}";
if ($this->_order !=NULL) $sql .= " ORDER BY {$this->_order}";
if ( $this->_limit != NULL ) $sql .= " LIMIT {$this->_limit}";
$ret = $this->excute($sql);
if ( $ret == false ) return false;
$result = [];
while ( ($row = $ret->fetch_assoc()) != false) {
$result[] = $row;
}
return $result;
}

public function update() //更新数据
{
//update table set name = "wein" where id = 1
$sql = "UPDATE {$this->_table} SET ";
$fields = "";
foreach( $this->_data as $key => $val )
{
if ( $fields == "" ) $fields = "{$key}='{$val}'";
else $fields .= ","."{$key}='{$val}'";

}
if ( $this->_where != NULL ) $sql .= "{$fields} WHERE {$this->_where}";
else $sql .= "{$fields}";
$ret = $this->excute($sql);
if ( $ret == false ) return false;
$row = $this->_link->affected_rows;
if ( $row == 0 ) return false;
return true;
}

public function delete()//删除数据
{
//拼接sql语句
//delete from table where id =1
$sql = "DELETE FROM {$this->_table} WHERE {$this->_where}";
$ret = $this->excute($sql);
return $ret;
}

public function count() //统计数据
{
$sql = "SELECT COUNT({$this->_fields}) AS num FROM {$this->_table}";
if ( $this->_where != NULL ) $sql .= " WHERE {$this->_where}";
$ret = $this->excute($sql); //select 操作query 返回的是结果集
if ( $ret == false ) return false;
$row = $ret->fetch_assoc();
if ( empty($row) ) return false;
return $row['num'];
}

public function __destruct() //断开数据库连接
{
$this->_link->close();
$this->_link = NULL;
}

}

二、分页类的封装

libs/Page文件夹

1、通过Page.class.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
<?php
namespace libs\Page;
class Page {
private $_totals = 0;
private $_pagesize = 0;

public function __construct( $pagesize, $total )
{
$this->_totals = $total;
$this->_pagesize = $pagesize;
}

public function getoffset() //获取展示下一页开始的id
{
$page = isset($_GET['page'])?$_GET['page']:1;
$offset = ($page -1) * $this->_pagesize;
return $offset;
}

public function show($left = 2)
{
$page = isset($_GET['page'])?$_GET['page']:1;
$totalPages = ceil($this->_totals / $this->_pagesize);
$html = '<ul class="page-ui-box">';
if ( $page > 1 ){
$html .= '<li class="able"><a href="?page=1">首页</a></li>';
$html .= '<li class="able"><a href="?page='.($page-1).'">上一页</a></li>';
}
else
{
$html .= '<li class="unable"><a>首页</a></li>';
$html .= '<li class="unable"><a>上一页</a></li>';
}
//当前页的前面显示两个数字页面

$start = ($page - $left);
if ( $start <= 0 ) $start = 1;
for( $i = $start; $i < $page; $i++ )
{
$html .= '<li class="able"><a href="?page='.$i.'">'.$i.'</a></li>';
}

$html .= '<li class="unable"><a>'.$page.'</a></li>';

//当前页的后面显示两个数字页面
$end = $page + 2;
if ( $end > $totalPages ) $end = $totalPages;
for( $i = $page + 1; $i <= $end; $i++ )
{
$html .= '<li class="able"><a href="?page='.$i.'">'.$i.'</a></li>';
}

if ( $page < $totalPages )
{
$html .= '<li class="able"><a href="?page='.($page+1).'">下一页</a></li>';
$html .= '<li class="able"><a href="?page='.$totalPages.'">尾页</a></li>';
}
else
{
$html .= '<li class="unable"><a>下一页</a></li>';
$html .= '<li class="unable"><a>尾页</a></li>';
}
$html .= '</ul>';
return $html;
}
}

?>

2、样式文件Page-ui.css

1
2
3
4
5
6
7
8
9
10
* {margin: 0px;padding: 0px;}
ul, li {list-style: none;}
.page-ui-box {overflow: hidden;border-left: 1px solid #CCC;}
.page-ui-box li {float: left;padding: 5px 25px;height: 30px;line-height: 30px;border: 1px solid #CCC;border-left:none;cursor: pointer;}
.unable {background: #EEE;}
.page-ui-box a {text-decoration: none;}
.able a {color: blue;}
.able a:hover {text-decoration: underline;}
table{border-collapse:collapse; border-spacing:0; border-left:1px solid #aaa; border-top:1px solid #aaa; }
td{border-right:1px solid #aaa; border-bottom:1px solid #aaa; padding:3px 15px; text-align:left; color:#3C3C3C;}

三、图片验证码类的封装

libs/Verify文件夹

1、Verify.class.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
<?php 
namespace libs\Verify;
class Verify
{
private $width=100;
private $height=30;
private $content="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
private $num=4;
private $point=100;
private $line= 4;

public function __construct(){

}

public function show(){
/**
* 字母+数字的验证码生成
*/
// 开启session
session_start();
//1.创建黑色画布
$image = imagecreatetruecolor($this->width, $this->height);

//2.为画布定义(背景)颜色
$bgcolor = imagecolorallocate($image, 255, 255, 255);

//3.填充颜色
imagefill($image, 0, 0, $bgcolor);

// 4.设置验证码内容

//4.1 定义验证码的内容

//4.1 创建一个变量存储产生的验证码数据,便于用户提交核对
$captcha = "";
for ($i = 0; $i < $this->num; $i++) {
// 字体大小
$fontsize = 5;
// 字体颜色 随机生成字体颜色
$fontcolor = imagecolorallocate($image, mt_rand(0, 120), mt_rand(0, 120), mt_rand(0, 120));
// 设置字体内容
$fontcontent = substr($this->content, mt_rand(0, strlen($this->content)), 1);
$captcha .= $fontcontent;
// 显示的坐标
$x = ($i * $this->width / $this->num) + mt_rand(5, 10);
$y = mt_rand($this->height/3, $this->height/2);
// 填充内容到画布中
imagestring($image, $fontsize, $x, $y, $fontcontent, $fontcolor);
}
$_SESSION["captcha"] = $captcha;

//4.3 设置背景干扰元素
for ($i = 0; $i < $this->point; $i++) {
$pointcolor = imagecolorallocate($image, mt_rand(50, 200), mt_rand(50, 200), mt_rand(50, 200));
imagesetpixel($image, mt_rand(1, $this->width), mt_rand(1, $this->height), $pointcolor);
}

//4.4 设置干扰线
for ($i = 0; $i < $this->line; $i++) {
$linecolor = imagecolorallocate($image, mt_rand(50, 200), mt_rand(50, 200), mt_rand(50, 200));
imageline($image, mt_rand(1, $this->width), mt_rand(1, $this->height), mt_rand(1, $this->width), mt_rand(1, $this->height), $linecolor);
}

return $image;

//6.输出图片到浏览器
// imagepng($image);

//7.销毁图片
// imagedestroy($image);
}

public function width($width) //设置验证码图片的宽度
{
$this->width=$width;
return $this;
}

public function height($height) //设置验证码图片的高度
{
$this->height=$height;
return $this;
}

public function setLine($line) //设置验证码图片的干扰线数量
{
$this->line=$line;
return $this;
}


public function setNum($num) // 设置验证码的位数
{
$this->num=$num;
return $this;
}

public function setChars($content) //设置验证码出现的字符集, 比如设置 123456789,那么验证码中出现的字符只能是这个字符串中的某几个
{
$this->content=$content;
return $this;
}

public function setPoint($point) // 设置验证码图片中出现的杂点数量
{
$this->point=$point;
return $this;
}

}
?>

2、Verify类的实例verify.php:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
//5.向浏览器输出图片头信息
header('content-type:image/png');
spl_autoload_register(function($class){ //$class = libs\Db\Mysql
//libs\Db\Mysql -> libs/Db/Mysql
$class_path = str_replace('\\', '/', $class);
$class_path .= '.class.php';
require_once $class_path;
});
use libs\Verify\Verify;
$verify=new Verify();
$char="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
$verifyhtml=$verify->width(200)->height(80)->setLine(6)->setNum(6)->setChars($char)->setPoint(500)->show(); //链式操作

//6.输出图片到浏览器
imagepng($verifyhtml);
//7.销毁图片
imagedestroy($verifyhtml);
?>

四、运用实例

这里通过start.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
<?php
//namespace "上级文件夹名称"
// require_once 'libs/Db/Mysql.class.php';
//引入命名空间
spl_autoload_register(function($class){ //$class = libs\Db\Mysql
//libs\Db\Mysql -> libs/Db/Mysql
$class_path = str_replace('\\', '/', $class);
$class_path .= '.class.php';
require_once $class_path;
});

use libs\Db\Mysql;
use libs\Page\Page;

$db = Mysql::init(); //新建Mysql对象
$totals = $db->table("contact")->field("id")->count();
$pagesize = 3; //每一页显示的数据条数
$page = new Page($pagesize,$totals); //新建Page对象
$pagehtml = $page->show();
$left = $page->getoffset();
$db->setDebug(true);
$data=$db->table("contact")->where(NULL)->field("*")->order("id desc")->limit("{$left},{$pagesize}")->getList(); //链式操作
// $data = $db->getList('contact', NULL,"*", 'id desc', "{$left}, {$pagesize}");
// var_dump($data);
?>


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
<link rel="stylesheet" type="text/css" href="libs/Page/page-ui.css">
</head>
<body>
<table>
<tr>
<td>编号</td>
<td>姓名</td>
<td>工作</td>
<td>QQ</td>
<td>手机号码</td>
<td>留言内容</td>
</tr>
<?php foreach ($data as $key => $val) { ?>
<tr>
<td><?=$val['id']?></td>
<td><?=$val['userName']?></td>
<td><?=$val['job']?></td>
<td><?=$val['qqnum']?></td>
<td><?=$val['phone']?></td>
<td><?=$val['remark']?></td>
</tr>
<?php } ?>
</table>
<?=$pagehtml?> <!-- 显示分页样式 -->
<img src="http://localhost/space/verify.php" onclick="resetPic(this)"> <!-- 显示图片验证码 -->
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript">
function resetPic(obj)
{
$(obj).attr('src', "http://localhost/space/verify.php"); //点击二维码刷新
}
</script>
</body>
</html>

附:本人建立test数据库contact表的sql文件:

1
2
3
4
5
6
7
8
9
use test; 
create table contact(
id int primary key auto_increment,
userName char(20) not null unique,
job char(10) not null,
qqnum char(10) not null,
phone char(100),
remark text
);
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×