Update
更新資料
2019/10/29 (更新內容)
2019/11/16 (補充內容)
更新資料 with Associative Array
一般而言,更新資料,要先取得原本的資料,再將原本的資料呈現給使用者,讓使用者修改。
第一步,先利用$_GET取得postid
$postid = isset($_GET["postid"])? $_GET["postid"] : "";
如果postid不存在,就顯示錯誤訊息並停止
if ($postid==""){
ECHO "ERROR: 未指定postid!";
die();
}
當postid存在時,利用postid從資料庫取得資料,程式碼跟Search相似,唯一不一樣的是,利用fetch讀取一筆資料。
$sql="select * from job where postid = :postid";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':postid', $postid);
$stmt->execute();
$job = $stmt->fetch(PDO::FETCH_ASSOC);
讀取到資料後,將資料放到各個欄位中 (注意,不同的欄位的處理方式不同):
<input type="text" name="company" value="<?=$job["company"]?>">
<textarea name="content" cols="40" rows="10"><?=$job["content"]?></textarea>
<input type="date" name="pdate" value="<?=$job["pdate"]?>">
另外,利用hidden把$postid也一併傳給update_process.php。
<input type="hidden" name="postid" value="<?$postid?>">
完整的程式碼update.php
<?php
require 'db.php';
$postid = isset($_GET["postid"])? $_GET["postid"] : "";
if ($postid==""){
ECHO "ERROR: 未指定postid!";
die();
}
else
{
$sql="select * from job where postid = :postid";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':postid', $postid);
$stmt->execute();
$job = $stmt->fetch(PDO::FETCH_ASSOC);
}
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Insert</title>
</head>
<body>
<form method="post" action="update_process.php">
<table width="40%">
<caption>新增 求才公告</caption>
<tr>
<td>求才廠商</td>
<td><input type="text" name="company" value="<?=$job["company"]?>"></td>
</tr>
<tr>
<td>求才內容</td>
<td>
<textarea name="content" cols="40" rows="10"><?=$job["content"]?></textarea>
</td>
</tr>
<tr>
<td>求才日期</td>
<td><input type="date" name="pdate" value="<?=$job["pdate"]?>"></td>
</tr>
<tr>
<td colspan=2><input type="submit" ></td>
</tr>
</table>
<input type="hidden" name="postid" value="<?=$postid?>">
</form>
</body>
</html>
更新資料的sql
update job set company='Apple', content='PHP Programmer', pdate='2019-08-29' where postid=1;
將內容以變數取代
$sql="update job set company=:company, content=:content, pdate=:pdate where postid=:postid";
其他跟Create類似。
完整的update_process.php
<?php
require 'db.php';
if ( !empty($_POST)) {
// insert data
$sql="update job set company=:company, content=:content, pdate=:pdate where postid=:postid";
$stmt = $conn->prepare($sql);
$result = $stmt->execute($_POST);
$conn = null;
if ($result) {
header('location:query.php');
}
else {
echo "ERROR in UPDATE";
}
}
else {
echo "ERROR";
}
?>
修改一下query.php,讓使用者可以選擇想更新的內容
<?php
require 'db.php';
$searchtxt = isset($_POST["searchtxt"])? $_POST["searchtxt"] : "";
if ($searchtxt==""){
$sql="select * from job";
$stmt = $conn->prepare($sql);
}
else
{
$sql="select * from job where company like :searchtxt";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':searchtxt', "%".$searchtxt."%");
}
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:center'>
<tr style='text-align:center'>
<td>求才廠商</td>
<td>求才內容</td>
<td>功能</td>
</tr>
<?php
foreach($rows as $job){ ?>
<tr style='text-align:center'>
<td><?=$job["company"]?></td>
<td><?=$job["content"]?></td>
<td><a href="update.php?postid=<?=$job["postid"]?>">[修改]</a></td>
</tr>
</tr>
<?php
}
$conn = null;
?>
</table>
更新資料 with Class
一般而言,更新資料,要先取得原本的資料,再將原本的資料呈現給使用者,讓使用者修改。
第一步,先利用$_GET取得postid
$postid = isset($_GET["postid"])? $_GET["postid"] : "";
如果postid不存在,就顯示錯誤訊息並停止
if ($postid==""){
ECHO "ERROR: 未指定postid!";
die();
}
當postid存在時,利用postid從資料庫取得資料,程式碼跟Search相似,唯一不一樣的是,利用fetchObject讀取一筆資料。
$sql="select * from job where postid = :postid";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':postid', $postid);
$stmt->execute();
$job = $stmt->fetchObject('Job');
另一種寫法:
$sql="select * from job where postid = :postid";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':postid', $postid);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_CLASS, 'Job');
$job = $stmt->fetch(PDO::FETCH_CLASS);
讀取到資料後,將資料放到各個欄位中 (注意,不同的欄位的處理方式不同):
<input type=text name="company" value="<?=$job->company?>">
<textarea name="content" cols=40 rows=10><?=$job->content?></textarea>
<input type="date" name="pdate" value="<?=$job->pdate?>">
另外,利用hidden把$postid也一併傳給update_process.php。
<input type="hidden" name="postid" value="<?$postid?>">
完整的程式碼update.php
<?php
require 'job.php';
require 'db.php';
$postid = isset($_GET["postid"])? $_GET["postid"] : "";
if ($postid==""){
ECHO "ERROR: 未指定postid!";
die();
}
else
{
$sql="select * from job where postid = :postid";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':postid', $postid);
$stmt->execute();
$job = $stmt->fetchObject('Job');
}
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Insert</title>
</head>
<body>
<form method="post" action="update_process.php">
<table width="40%">
<caption>新增 求才公告</caption>
<tr>
<td>求才廠商</td>
<td><input type="text" name="company" value="<?=$job->company?>"></td>
</tr>
<tr>
<td>求才內容</td>
<td>
<textarea name="content" cols="40" rows="10"><?=$job->content?></textarea>
</td>
</tr>
<tr>
<td>求才日期</td>
<td><input type="date" name="pdate" value="<?=$job->pdate?>"></td>
</tr>
<tr>
<td colspan=2><input type="submit" ></td>
</tr>
</table>
<input type="hidden" name="postid" value="<?=$postid?>">
</form>
</body>
</html>
更新資料的sql
update job set company='Apple', content='PHP Programmer', pdate='2019-08-29' where postid=1;
將內容以變數取代
$sql="update job set company=:company, content=:content, pdate=:pdate where postid=:postid";
其他跟Create類似。
完整的update_process.php
<?php
require 'job.php';
require 'db.php';
if ( !empty($_POST)) {
// insert data
$sql="update job set company=:company, content=:content, pdate=:pdate where postid=:postid";
$stmt = $conn->prepare($sql);
$job = new Job($_POST);
$result = $stmt->execute($job->toArray());
$conn = null;
if ($result) {
header('location:query.php');
}
else {
echo "ERROR in UPDATE";
}
}
else {
echo "ERROR";
}
?>
不過,因為這裡的Job傳了四個參數(多了postid),所以job.php要修改一下:
<?php
class Job {
/* Member variables */
/* make them private for encapsulation */
private $postid;
private $company;
private $content;
private $pdate;
//https://phpdelusions.net/pdo/fetch_modes#FETCH_CLASS
//only private variables were set
function __set($variable, $value){}
//https://culttt.com/2014/04/16/php-magic-methods/
//https://www.tutorialdocs.com/article/16-php-magic-methods.html
function __get($variable){
return $this->$variable;
}
/* constructor */
function __construct(){
//__set is called before __construt
//this should be modified to prevent PDO reset variables
$arguments = func_get_args();
if (sizeof(func_get_args())==4){
$this->postid = $arguments[0];
$this->company = $arguments[1];
$this->content = $arguments[2];
$this->pdate = $arguments[3];
}
if (sizeof(func_get_args())==1){
//create a Job from $_POST
$this->postid = isset($arguments[0]["postid"])? $arguments[0]["postid"] : 0;
$this->company = $arguments[0]["company"];
$this->content = $arguments[0]["content"];
$this->pdate = $arguments[0]["pdate"];
}
}
}
?>
修改一下query.php,讓使用者可以選擇想更新的內容
<?php
require 'job.php';
require 'db.php';
$searchtxt = isset($_POST["searchtxt"])? $_POST["searchtxt"] : "";
if ($searchtxt==""){
$sql="select * from job";
$stmt = $conn->prepare($sql);
}
else
{
$sql="select * from job where company like :searchtxt or content like :searchtxt";
$stmt = $conn->prepare($sql);
//$searchtxt = $searchtxt;
$stmt->bindParam(':searchtxt', "%".$searchtxt."%");
}
$stmt->execute();
//$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$rows = $stmt->fetchAll(PDO::FETCH_CLASS, 'Job');
?>
<a href="insert.html">新增求才資訊</a>
<form action="query.php" method="post">
<p style='text-align:center'>
<input type="text" name="searchtxt" value="<?=$searchtxt?>">
<input type="submit" value="搜尋廠商與內容"></p>
</form>
<table width='85%' style='float:center'>
<tr style='text-align:center'><td>求才廠商</td><td>求才內容</td><td>功能</td></tr>
<?php
foreach($rows as $job){
echo "<tr style='text-align:center'><td>".$job->company."</td><td>".$job->content."</td>";
echo "<td><a href=update.php?postid=".$job->postid.">[修改]</a></td></tr>";
}
$conn = null;
?>
</table>
** 作業 **
請利用上週的作業,讓登入成功的消費者可以更改自己的註冊資料
想一想,有沒有辦法讓新增及修改用同一個form? (挑戰題)
管理者可以刪除任何消費者的註冊資料 (挑戰題)
套用style ,美化介面 (挑戰題)
參考資料
Read
Create & Read
Update & Delete