web备忘录——php数据库操作的一个例子

最近学习了php操作数据库的一些知识,这里做了一个小小的例子,权当练习。因为时间比较仓促,所以代码写的比较简单,希望大家多多批评指正。

主要代码如下

html:

<!DOCTYPE html>
<html>
<head>
<meta charset=”UTF-8″>
<title>Memoe Online</title>
<link rel=”stylesheet” href=”styles/group.css”>
<link rel=”stylesheet” href=”styles/index.css”>
<script src=”js/jquery-3.4.1.min.js” type=”text/javascript”></script>
<script src=”js/memo.js”></script>
<script src=”js/My97DatePicker/WdatePicker.js”></script>
</head>
<body>
<!–main box–>
<div class=”main-box”>
<!–left part–>
<div class=”main-box-left left”>
<img class=”u-login-icon” src=”images/timg.jpg” alt=”smile”>
</div>
<!–right part–>
<div class=”main-box-right right”>
<!–right 0 memo列表展示区域–>
<div class=”memo-list left”>
<div class=”memo-list-head”>
Memo
</div>
<div class=”memo-list-content” id=”memo-list-box”>
<div class=”memo-list-content-item” data-id=””>
<p class=”u-list-title”>随手笔记</p>
<p class=”u-list-time”>16/10/22</p>
<p class=”u-list-desc”>普兰明 麻黄素假死糖可以查毒 </p>
</div>
</div>
</div>
<!–right 1 memo 编辑和显示区域–>
<div class=”memo-edit right”>
<!–头部功能操作区域–>
<div class=”g-blk10″></div>
<div class=”m-operation”>
<div class=”i-delete” onclick=”deleteData()”></div>
<div class=”i-save” onclick=”updateData()”></div>
<div class=”i-new” onclick=”newMemo()”></div>
</div>
<div class=”g-blk10″></div>
<div class=”g-blk30″></div>
<div class=”memo-edit-box”>
<form action=”#”>
<input type=”text” class=”u-edit-title” maxlength=”240″ id=”m_title”>
<div class=”g-blk10″></div>
<input type=”text” class=”u-edit-time Wdate input” id=”m_time” style=”width: 160px;border: 1px solid #A5ACB2;” onfocus=”WdatePicker({isShowClear:true,readOnly:true,dateFmt:’yyyy-MM-dd HH:mm:ss’})” readonly=”” placeholder=”年/月/日”>
<div class=”g-blk10″></div>
<input type=”text” class=”u-edit-desc” maxlength=”240″ id=”m_desc”>
<div class=”g-blk10″></div>
<textarea name=”content” id=”m_content” cols=”30″ rows=”10″ class=”u-edit-content”></textarea>
</form>
</div>

</div>
</div>

</div>
</body>
</html>

css

@charset “utf-8”;
html, body{height: 100%;}
.main-box{height: 100%; min-height: 500px; margin: 20px auto; min-width: 1200px; width: 96%;}
/*left part*/
.main-box-left{width: 5%; background: #f8f8f8; outline: 1px solid #ececec; height: 100%; min-height: 500px;}
.u-login-icon{display: block; margin: 20px auto; width: 50px; height: 50px; -moz-border-radius: 50%; border-radius: 50%;}
/*right part*/
.main-box-right{background: #FFFFFF; width: 95%; height: 100%; min-height: 500px; outline: 1px solid #ececec;}
.memo-list{outline: 1px solid #ececec; width: 18%; background: #fff; height: 100%; overflow: hidden;}
.memo-list-head{font-size: 20px; text-align: center; line-height: 30px; color: #878787;}
.memo-list-content{max-height: 97%;overflow-x: hidden;}
.memo-list-content-item{box-sizing: border-box; padding: 10px 20px;}
.memo-list-content-item:hover{background: #3fbc6c; transition: background-color 0.1s ease-in-out, width 0s ease-in-out 0.1s;}
.memo-list-content-item:hover *{color: #FFFFFF; transition: color 0.1s ease-in-out, width 0s ease-in-out 0.1s;}
.u-list-title{transition: color 0.1s ease-in-out, width 0s ease-in-out 0.1s; font-size: 16px; font-weight: 400; color: #4a4a4a; margin-bottom: 4px; max-height: 40px; overflow: hidden; overflow-wrap: break-word; text-overflow: ellipsis; white-space: nowrap; word-wrap: break-word; line-height: 20px; width: 302px;}
.u-list-time{transition: color 0.1s ease-in-out, width 0s ease-in-out 0.1s; font-size: 11px; font-weight: 400; text-transform: uppercase; letter-spacing: 1px; margin-bottom: 8px;}
.u-list-desc{transition: color 0.1s ease-in-out, width 0s ease-in-out 0.1s; font-size: 12px; font-weight: 400; line-height: 17px;}
/*编辑区域*/
.memo-edit{width: 80%;}
.m-operation{width: 100%; text-indent: 10px; height: 30px; background: #fff; box-sizing: border-box; padding-left: 152px;}
.m-operation .i-delete{float: left; width: 24px; height: 24px; background: url(“../images/delete.png”);}
.m-operation .i-save{float: left; width: 24px; height: 24px; background: url(“../images/save.png”);margin-left:20px;}
.m-operation .i-new{float: left; width: 24px; height: 24px; background: url(“../images/new.png”);background-size:24px 24px;margin-left:20px;}
.memo-edit-box{width: 1100px; margin: 0 auto;}
.memo-edit-box .u-edit-title{color: #2dbe60; width: 80%; height: 35px; line-height: 35px; outline: none; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; font-weight: 300; margin-top: -1px; margin-left: 10px;}
.memo-edit-box .u-edit-time{margin-left: 10px; color: #2dbe60; line-height: 35px; outline: none; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; font-weight: 300; height: 35px; margin-top: -1px;}
.memo-edit-box .u-edit-desc{margin-left: 10px; color: #2dbe60; line-height: 35px; outline: none; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; font-weight: 300; height: 35px; margin-top: -1px;}
.memo-edit-box .u-edit-content{color: #2dbe60; width: 80%; min-height: 500px; line-height: 35px; margin-left: 10px; outline: none; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; font-weight: 300; margin-top: -1px;}

js

var currentId;
$(function () {
getData();

});


function getData() {
$.ajax({
url: ‘https://bcc.hotfeel.me/memo.php’,
data: {
action_type: ‘select’,
},
dataType: “json”,
method: ‘get’,
success: function (data) {
console.log(“查询返回的数据是”, data);
if (data.list) {
$(“#memo-list-box”).empty();
for (var i = 0; i < data.list.length; i++) {
var _html = “<div class=\”memo-list-content-item\” data-id=\”” + data.list[i].id + “\”>” +
“<p class=\”u-list-title\”>” + data.list[i].title + “</p>” +
“<p class=\”u-list-time\”>” + data.list[i].time + “</p>” +
“<p class=\”u-list-desc\”>” + data.list[i].description + “</p>” +
“</div>”;
$(“#memo-list-box”).append(_html);
}
$(“div.memo-list-content-item”).on(‘click’, function () {
currentId = $(this).data(‘id’);
$.ajax({
url: ‘https://bcc.hotfeel.me/memo.php’,
data: {
action_type: ‘select’,
id: currentId
},
dataType: “json”,
method: ‘get’,
success: function (data) {
console.log(“查询单个的数据是”, data);
if (data.list) {
$(“#m_title”).val(data.list[0].title);
$(“#m_time”).val(data.list[0].time);
$(“#m_desc”).val(data.list[0].description);
$(“#m_content”).val(data.list[0].content);
}
else {
getData();
alert(“该条数据已经被删除了!”);
}
},
error: function () {
console.log(“获取失败!”);
}
})
})
}
else {
$(“#memo-list-box”).empty();
console.log(“未获取到事项”);
}
},
error: function () {
console.log(“获取失败!”);
}
})
}


function newMemo() {
$(“#m_title”).val(“”);
$(“#m_time”).val(“”);
$(“#m_desc”).val(“”);
$(“#m_content”).val(“”);
currentId = null;
}

function updateData() {
let update_type = ”;
if (currentId) {
update_type = “update”;
}
else {
update_type = “insert”;
}
if ($(“#m_title”).val() != “”) {
$.ajax({
url: ‘https://bcc.hotfeel.me/memo.php’,
data: {
action_type: update_type,
id: currentId,
title: $(“#m_title”).val(),
time: $(“#m_time”).val(),
keywords: “”,
description: $(“#m_desc”).val(),
content: $(“#m_content”).val()
},
dataType: “json”,
method: ‘get’,
success: function (data) {
console.log(“返回的数据是”, data);
if (data[“affected rows”] > 0) {
getData();
update_type == “insert” ? alert(“新增成功!”) : alert(“修改成功!”);
} else {
update_type == “insert” ? alert(“新增失败!”) : alert(“修改失败!”);
}
},
error: function () {
console.log(“获取失败!”);
}
})
}
else {
alert(“请至少写个标题吧!”);
}
}

function deleteData() {
if (currentId) {
$.ajax({
url: ‘https://bcc.hotfeel.me/memo.php’,
data: {
action_type: ‘delete’,
id: currentId,
title: $(“#m_title”).val(),
time: $(“#m_time”).val(),
keywords: “”,
description: $(“#m_desc”).val(),
content: $(“#m_content”).val()
},
dataType: “json”,
method: ‘get’,
success: function (data) {
console.log(“返回的数据是”, data);
$(“#m_title”).val(“”);
$(“#m_time”).val(“”);
$(“#m_desc”).val(“”);
$(“#m_content”).val(“”);
getData();
alert(“删除成功!”);
currentId = null;
},
error: function () {
console.log(“获取失败!”);
}
})
} else {

alert(“未选择需要删除的事项!”)

}
}


function clearInput() {
$(“#m_title”).val(“”);
$(“#m_time”).val(“”);
$(“#m_desc”).val(“”);
$(“#m_content”).val(“”);
}

php接口

<?php
header(“Content-type:application/json;charset=utf-8”);
header(“Access-Control-Allow-Origin:*”);
header(‘Access-Control-Allow-Methods:POST.GET’);
header(‘Access-Control-Allow-Headers:x-requested-with, content-type’);
$username = ‘mem’;
$userpass = ‘pass’;
$dbhost = ‘****’;
$dbdatabase = ‘memo’;
$action_type = $_GET[“action_type”]; // 获取操作类型
$id = $_GET[“id”];
$title = $_GET[“title”];
$time = $_GET[“time”];
date_default_timezone_set(‘PRC’);//设置默认的时区
$keywords = $_GET[“keywords”];
$desc = $_GET[“desc”];//cut content
$content = $_GET[“content”];

if (!$desc) {
$desc = substr($content, 0, 10);//描述截取10个内容
}

if(!$time){
$time= date(‘Y-m-d h:i:s’, time());//时间为空时,时间设置为当前的时间
}

$db = new mysqli($dbhost, $username, $userpass, $dbdatabase);
if (mysqli_connect_error()) {//返回一个描述错误的字符串。如果没有错误发生则返回 NULL
echo ‘Could not connect to database.’;
exit;
} else {

if ($action_type == ‘insert’) {
$insert_arr = array();
$insert_sql = “INSERT INTO m_main (title,time,keywords,description,content) VALUES(‘” . $title . “‘,'” . $time . “‘,'” . $keywords . “‘,'” . $desc . “‘,'” . $content . “‘);”;
$insert_result = $db->query($insert_sql);
if (mysqli_affected_rows($db)) {//检查是否成功
$insert_arr[“insert_succeed”] = “1”;
$insert_arr[“affected rows”] = mysqli_affected_rows($db);
} else {
$insert_arr[“insert succeed”] = “-1”;
$insert_arr[“affected rows”] = mysqli_affected_rows($db);
}

echo(json_encode($insert_arr, JSON_UNESCAPED_UNICODE));
} else if ($action_type == ‘update’) {
$update_arr = array();
if ($id) { //判断id是否存在
$update_sql = “UPDATE m_main SET title='” . $title . “‘, time='” . $time . “‘,keywords='” . $keywords . “‘,description='” . $desc . “‘,content='” . $content . “‘ WHERE id=” . $id . “;”;
$update_result = $db->query($update_sql);
if (mysqli_affected_rows($db)) { //检查是否成功
$update_arr[“update succeed”] = “1”;
$update_arr[“affected rows”] = mysqli_affected_rows($db);
} else {//更新失败
$update_arr[“update succeed”] = “-1”;
$update_arr[“affected rows”] = mysqli_affected_rows($db);
}
} else {
$update_arr[“update error”] = “id不存在”;
}
echo json_encode($update_arr, JSON_UNESCAPED_UNICODE);
} else if ($action_type == ‘delete’) {
$delete_arr = array();
if ($id) { //判断id是否存在
$delete_sql = “DELETE FROM m_main WHERE id='” . $id . “‘;”;
$delete_result = $db->query($delete_sql);
if (mysqli_affected_rows($db)) { //检查是否成功
$delete_arr[“delete succeed”] = “1”;
$delete_arr[“delete rows”] = mysqli_affected_rows($db);
} else {//删除失败
$delete_arr[“delete succeed”] = “-1”;
$delete_arr[“affected rows”] = mysqli_affected_rows($db);
}
} else {
$delete_arr[“delete error”] = “需要删除的id不存在”;
}
echo json_encode($delete_arr, JSON_UNESCAPED_UNICODE);
} else if ($action_type == ‘select’) {
$select_arr = array();
$select_sql = “SELECT * from m_main ORDER BY id DESC;”;
if($id){
$select_sql = “SELECT * from m_main where id='”.$id.”‘;”;
}
$select_result = $db->query($select_sql);
if (mysqli_affected_rows($db)) { //检查是否成功
$select_arr[“select succeed”] = “1”;
while($row = mysqli_fetch_assoc($select_result)) {
$select_arr[“list”][]=$row;
}
$select_arr[“select rows”] = mysqli_affected_rows($db);
} else {//查询失败
$select_arr[“select succeed”] = “-1”;
$select_arr[“select rows”] = mysqli_affected_rows($db);
}
echo json_encode($select_arr, JSON_UNESCAPED_UNICODE);
} else {
$op_arr = array();
$op_arr[“error”] = “非法的操作”;
echo json_encode($op_arr, JSON_UNESCAPED_UNICODE);
}

mysqli_close($db);
}

比较关键的地方都有注释,很容易看懂。这样就完成了一个简单的网络备忘录。

此条目发表在php分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。