此篇我们来看下文章简单操作，怎么连接数据库？怎么实现文章的增删改查？
这里没有使用模板引擎，主要使用ajax来获取数据渲染页面。
文章表结构
创建表的sql语句
~~~
CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author` varchar(16) NOT NULL COMMENT '作者',
`title` varchar(64) NOT NULL COMMENT '标题',
`content` tinytext NOT NULL COMMENT '内容',
`create_time` int(11) NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `author` (`author`,`title`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章表' AUTO_INCREMENT=1 ;
~~~
数据
目录结构
article.php （控制器类）
~~~
include 'model.php';
class Article {
private $articleModel;
function __construct() {
$this->articleModel = new ArticleModel();
}
// 添加文章
function add($data) {
if($this->articleModel->add($data)) {
echo '<script>alert("添加成功！");location.href="articleList.html";</script>';
} else {
echo '<script>alert("添加失败！");history.back();</script>';
}
}
// 删除文章
function del($id) {
if($this->articleModel->del($id)) {
header('Location: articleList.html');
} else {
echo '<script>alert("删除失败！");history.back();</script>';
}
}
// 修改文章
function modify($id, $data) {
if($this->articleModel->modify($id, $data)) {
echo '<script>alert("修改成功！");location.href="articleList.html";</script>';
} else {
echo '<script>alert("修改失败！");history.back();</script>';
}
}
// 获取所有文章
function getAll() {
$result = $this->articleModel->getAll();
echo json_encode($result);
}
// 获取一篇文章
function getOne($id) {
$result = $this->articleModel->getOne($id);
echo json_encode($result);
}
}
$article = new Article();
if(isset($_GET['action'])) {
switch($_GET['action']) {
case 'add': // 添加
$article->add($_POST);
break;
case 'edit': // 编辑
$article->modify($_POST['id'], $_POST);
break;
case 'del': // 删除
$article->del($_GET['id']);
break;
case 'getAll': // 获取全部
$article->getAll();
break;
case 'getOne': // 获取一条
$article->getOne($_GET['id']);
break;
}
}
~~~
model.php （模型类）
~~~
class ArticleModel {
private $db = [
'host'=>'localhost', // 主机
'port'=>'3306', // 端口
'username'=>'root', // 用户名
'password'=>'root', // 密码
'dbname'=>'test', // 库
'table'=>'article', // 表
'encoding'=>'utf8' // 编码
];
private $con;
function __construct() {
$this->con = mysqli_connect(
$this->db['host'].':'.$this->db['port'],
$this->db['username'],
$this->db['password']
);
if(!$this->con) {
die('连接失败！'.mysqli_error($this->con));
}
mysqli_query($this->con, 'set names '.$this->db['encoding']);
mysqli_select_db($this->con, $this->db['dbname']);
}
// 添加文章
function add($data) {
$sql = 'INSERT INTO '.$this->db['table'].' (author, title, content, create_time) VALUES("'.$data['author'].'","'.$data['title'].'","'.$data['content'].'",'.time().')';
return mysqli_query($this->con, $sql);
}
// 删除文章
function del($id) {
$sql = 'DELETE FROM '.$this->db['table'].' WHERE id='.$id;
return mysqli_query($this->con, $sql);
}
// 修改文章
function modify($id, $data) {
$fields = '';
foreach($data as $k=>$v) {
$fields .= $k.'="'.$v.'",';
}
$fields = rtrim($fields, ',');
$sql = 'UPDATE '.$this->db['table'].' SET '.$fields.' WHERE id='.$id;
return mysqli_query($this->con, $sql);
}
// 获取所有文章
function getAll() {
$sql = 'SELECT * FROM '.$this->db['table'];
$result = mysqli_query($this->con, $sql);
$data = [];
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
array_push($data, [
'id'=>$row['id'],
'author'=>$row['author'],
'title'=>$row['title'],
'content'=>$row['content'],
'create_time'=>date('Y-m-d H:i', $row['create_time'])
]);
}
}
return $data;
}
// 获取一篇文章
function getOne($id) {
$sql = 'SELECT id,author,title,content FROM '.$this->db['table'].' WHERE id='.$id;
$result = mysqli_query($this->con, $sql)->fetch_assoc();
return $result;
}
}
~~~
articleList.html
~~~
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="UTF-8">
<title>文章</title>
<style>
*{padding: 0;margin: 0;font-size: 14px;font-weight: normal;}
.table{border-collapse: collapse;border-left: 1px solid #ddd;border-top: 1px solid #ddd;}
.table th{background-color: #eee;}
.table th,.table td{padding: 6px 10px;border-right: 1px solid #ddd;border-bottom: 1px solid #ddd;}
</style>
<script src="common.js"></script>
</head>
<body>
<a href="addArticle.html">添加文章</a>
<script>
// 获取文章列表
ajax({
url: 'article.php?action=getAll',
success: function(res) {
var data = JSON.parse(res);
var table = document.createElement('table'),
thead = document.createElement('thead'),
tbody = document.createElement('tbody'),
row,
cell;
table.className = 'table';
thead.innerHTML = '<tr><th>id</th><th>作者</th><th>标题</th><th>内容</th><th>发布时间</th><th>编辑</th></tr>';
table.appendChild(thead);
data.forEach(function(item) {
row = tbody.insertRow();
for(var i in item) {
cell = row.insertCell();
cell.textContent = item[i];
}
cell = row.insertCell();
cell.innerHTML = '<a href="editArticle.html?id='+item.id+'">编辑</a> <a class="del" data-id="'+item.id+'" href="javascript:;">删除</a>';
// row.deleteCell(1); 删除列
});
// table.deleteRow(0); 删除行
table.appendChild(tbody);
document.body.appendChild(table);
// 点击删除
$('.del').forEach(function(ele) {
ele.addEventListener('click', function(e) {
if(confirm('确定要删除吗？')) {
ajax({
url: 'article.php?action=del&id='+e.target.dataset.id,
success: function() {
// 删除行
var tr = e.target.parentNode.parentNode;
tr.parentNode.removeChild(tr);
}
});
}
});
});
},
error: function() {
alert('删除失败！');
}
});
</script>
</body>
</html>
~~~
common.js
~~~
// 定义变量
var $ = document.querySelectorAll.bind(document);
var log = console.log.bind(console);
// 封装ajax
function ajax(options) {
var defaults = {
type: 'get',
url: '',
isAsync: true,
data: null,
success: function() {},
error: function() {},
complete: function() {}
};
var conf = Object.assign({}, defaults, options);
var xhr = new window.XMLHttpRequest();
xhr.open(conf.type, conf.url, conf.isAsync);
xhr.send(conf.data);
// 成功
xhr.onload = function() {
conf.success.call(null, this.response, this);
};
// 失败
xhr.onerror = function() {
conf.error.call(this);
};
}
~~~
addArticle.html
~~~
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="UTF-8">
<title>添加</title>
</head>
<body>
<a href="javascript:history.back();">返回</a>
<form action="article.php?action=add" method="post">
<input name="author" type="text" required placeholder="作者"/><br/>
<input name="title" type="text" required placeholder="标题"/><br/>
<textarea name="content" cols="30" rows="10" required placeholder="内容"></textarea><br/>
<button type="submit">提交</button>
<button type="reset">重置</button>
</form>
</body>
</html>
~~~
editArticle.html
~~~
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="UTF-8">
<title>编辑</title>
<script src="common.js"></script>
</head>
<body>
<a href="javascript:history.back();">返回</a>
<form action="article.php?action=edit" method="post">
<input type="hidden" name="id"/>
<input name="author" type="text" required placeholder="作者"/><br/>
<input name="title" type="text" required placeholder="标题"/><br/>
<textarea name="content" cols="30" rows="10" required placeholder="内容"></textarea><br/>
<button type="submit">提交</button>
<button type="reset">重置</button>
</form>
<script>
var id = location.search.match(/(id=\d+)/)[0].substr(3);
ajax({
url: 'article.php?action=getOne&id='+id,
success: function(res) {
var data = JSON.parse(res);
$('[name="id"]')[0].value = data.id;
$('[name="author"]')[0].value = data.author;
$('[name="title"]')[0].value = data.title;
$('[name="content"]')[0].value = data.content;
}
});
</script>
</body>
</html>
~~~
相当简单的操作，没有什么过滤和加密，很容易被gank，看下就行，后面文章会介绍过滤加密。。。
