Open Lu-yeom opened 3 years ago
日期:110年6月24日20:30~22:30
今日進度:[BE101] 用 PHP 與 MySQL 學習後端基礎:真正的實戰:留言板 - 新增功能篇#6-#7
課程筆記:
一、編輯留言功能
<span class="card__time">
<?php echo escape($row['created_at']); ?>
</span>
<?php if ($row['username'] === $username) { ?>
<a href="update_comment.php?id=<?php echo $row['id'] ?>">編輯</a>
<? } ?>
</div>
<p class="card__content"><?php echo escape($row['content']); ?></p>
</div>
在顯示時間後加上編輯功能,且只能編輯自己的留言
利用update_comment.php實現編輯留言功能
<?php
session_start();
require_once("conn.php");
require_once("utils.php");
$id = $_GET['id'];
$username = NULL;
$user = NULL;
if(!empty($_SESSION['username'])) {
$username = $_SESSION['username'];
$user = getUserFromUsername($username);
}
$stmt = $conn->prepare(
'select * from comments where id = ?'
);
$stmt->bind_param("i", $id);
$result = $stmt->execute();
if (!$result) {
die('Error:' . $conn->error);
}
$result = $stmt->get_result();
$row = $result->fetch_assoc();
?>
<!DOCTYPE html>
* 利用update_user.php建立handle_update_comment.php
<?php session_start(); require_once('conn.php'); require_once('utils.php');
if ( empty($_POST['content']) ) { header('Location: update_comment.php?errCode=1&id='.$_POST['id']); die('資料不齊全'); }
$username = $_SESSION['username']; $id = $_POST['id']; $content = $_POST['content'];
$sql = "update comments set content=? where id=?"; $stmt = $conn->prepare($sql); $stmt->bind_param('si', $content, $id); $result = $stmt->execute(); if (!$result) { die($conn->error); }
header("Location: index.php"); ?>
二、刪除留言功能
<?php if ($row['username'] === $username) { ?> 編輯 刪除 <?php } ?>
* 利用handle_update_comment.php製作delete_comment.php
<?php session_start(); require_once('conn.php'); require_once('utils.php');
if ( empty($_GET['id']) ) { header('Location: index.php?errCode=1'); die('資料不齊全'); }
$id = $_GET['id'];
$sql = "update comments set is_deleted=1 where id=?"; $stmt = $conn->prepare($sql); $stmt->bind_param('i', $id); $result = $stmt->execute(); if (!$result) { die($conn->error); }
header("Location: index.php"); ?>
* delete分為hard delete和soft delete,差別在於hard delete會將資料庫的留言刪除,soft delete則是會在資料庫中新增一筆is_deleted資料,以防誤刪後想復原資料。
* comments => 結構 => 於content之後 => 新增is_deleted => 類型TINYINT、預設值NULL => 儲存
* ```$sql = "update comments set is_deleted=1 where id=?";``` 設定刪除留言的狀態為1
* 但是按刪除鍵時留言不會同步消失,所以再修正如下:
$stmt = $conn->prepare( 'select '. 'C.id as id, C.content as content, '. 'C.created_at as created_at, U.nickname as nickname, U.username as username '. 'from comments as C ' . 'left join users as U on C.username = U.username '. 'where C.is_deleted IS NULL '. 'order by C.id desc' ); $result = $stmt->execute();
日期:110年6月26日20:30~22:30
今日進度:[BE101] 用 PHP 與 MySQL 學習後端基礎:真正的實戰:留言板 - 新增功能篇#8 - 真正的實戰:留言板 - 再次修正問題篇
課程筆記:
一、實作分頁功能,介紹 offset 與 limit
設定一頁可顯示幾個留言,並分頁(以5個留言一頁為例)
$page = 1;
if (!empty($_GET['page'])) {
$page = intval($_GET['page']);
}
$items_per_page = 5;
$offset = ($page - 1) * $items_per_page;
$stmt = $conn->prepare(
'select '.
'C.id as id, C.content as content, '.
'C.created_at as created_at, U.nickname as nickname, U.username as username '.
'from comments as C ' .
'left join users as U on C.username = U.username '.
'where C.is_deleted IS NULL '.
'order by C.id desc '.
'limit ? offset ? '
);
$stmt->bind_param('ii', $items_per_page, $offset);
$result = $stmt->execute();
製作切換頁面的功能
</section>
<div class="board__hr"></div>
<?php
$stmt = $conn->prepare(
'select count(id) as count from comments where is_deleted IS NULL'
);
$result = $stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
$count = $row['count'];
$total_page = ceil($count / $items_per_page);
?>
<div class="page-info">
<span>總共有 <?php echo $count ?> 筆留言,頁數:</span>
<span><?php echo $page ?> / <?php echo $total_page ?></span>
</div>
<div class="paginator">
<a href="index.php?page=1">首頁</a>
<?php if ($page != 1) {?>
<a href="index.php?page=<?php echo $page - 1?>">上一頁</a>
<?php } ?>
<?php if ($page != $total_page) {?>
<a href="index.php?page=<?php echo $page + 1?>">下一頁</a>
<a href="index.php?page=<?php echo $total_page?>">最後一頁</a>
<?php } ?>
</div>
二、發現問題:權限管理問題
雖然頁面設定為只能編輯自己的留言,但其實只要透過網址列更改編號,就可以修改其他用戶的留言,所以必須確定使用者的權限(*超級重要)
三、修正問題:確認權限
修正delete_comment.php
<?php
session_start();
require_once('conn.php');
require_once('utils.php');
if (
empty($_GET['id'])
) {
header('Location: index.php?errCode=1');
die('資料不齊全');
}
$id = $_GET['id'];
$username = $_SESSION['username'];
$sql = "update comments set is_deleted=1 where
id=? and username=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('is', $id, $username);
$result = $stmt->execute();
if (!$result) {
die($conn->error);
}
header("Location: index.php");
?>
修正handle_update_comment.php
<?php
session_start();
require_once('conn.php');
require_once('utils.php');
if (
empty($_POST['content'])
) {
header('Location: update_comment.php?errCode=1&id='.$_POST['id']);
die('資料不齊全');
}
$username = $_SESSION['username'];
$id = $_POST['id'];
$content = $_POST['content'];
$sql = "update comments set content=? where id=?
and username=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('sis', $content, $id, $username);
$result = $stmt->execute();
if (!$result) {
die($conn->error);
}
header("Location: index.php");
?>
日期:110年6月21日~23日
今日進度:[BE101] 用 PHP 與 MySQL 學習後端基礎:真正的實戰:留言板 - 新增功能篇#1-#5
課程筆記:
一、真正的實戰:留言板 - 新增功能篇
二、編輯暱稱功能
update_user.php
?>
<?php session_start(); require_once('conn.php'); require_once('utils.php');
if ( empty($_POST['content']) ) { header('Location: index.php?errCode=1'); die('資料不齊全'); }
$username = $_SESSION['username']; $content = $_POST['content'];
$sql = "insert into comments(username, content) values(?, ?)"; $stmt = $conn->prepare($sql); $stmt->bind_param('ss', $username, $content); $result = $stmt->execute(); if (!$result) { die($conn->error); } header("Location: index.php");
?>
SELECT * FROM comments LEFT JOIN users ON comments.username = users.username
$stmt = $conn->prepare('select C.id as id, C.content as content, '. 'C.create_at as create_at, U.nickname as nickname ', 'from comments as C '. 'left join users as U on C.username = U.username order by C.id desc'); $result = $stmt->execute();