Create
新增資料
2019/10/29 (調整內容)
2019/11/28 (補充內容)
新增資料 with Associative Array
先利用form讓使用者填寫資料
insert.html
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Insert</title>
</head>
<body>
<form method="post" action="insert.php">
<table width="40%">
<caption>新增 求才公告</caption>
<tr>
<td>求才廠商</td>
<td><input type="text" name="company"></td>
</tr>
<tr>
<td>求才內容</td>
<td>
<textarea name="content" cols="40" rows="10"></textarea>
</td>
</tr>
<tr>
<td>求才日期</td>
<td><input type="date" name="pdate"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" ></td>
</tr>
</table>
</form>
</body>
</html>
新增一筆資料的SQL是:
insert into job (company, content, pdate) values ('Apple', 'Programmer', '2019-08-29');
一些網路上的範例都是直接將$_POST的內容取代值,然後執行sql,但是為了避免sql injection,最好使用prepare (詳參: PHP Prepared Statements)
為了使用prepare,我們需要將內容以變數取代,php的語法就是:
$sql="insert into job (company, content, pdate) values (:company, :content, :pdate)";
利用prepare去設定sql statement:
$stmt = $conn->prepare($sql);
利用prepare有很多好處,在這裡的最大功能是避免sql injection。(詳參: 【網頁安全】給網頁後端新人的 SQL Injection 介紹與防範 (PHP))
再利用bindValue或bindParam去綁定變數內容以及sql中的變數,在這個情況下,可以利用bindValue或bindParam,但兩者是有所不同的。 (詳參: 比較 PDO bindParam 和 bindValue 的不同 & PDO – bindParam 和 bindValue 的使用時機)
$stmt->bindValue(':company', $_POST["company"]);
執行execute()後會新增資料到資料庫,並且會回傳true或false,如果回傳false,表示新增不成功。
insert.php
<?php
require 'db.php';
if ( !empty($_POST)) {
// insert data
$sql="insert into job (company, content, pdate) values (:company, :content, :pdate)";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':company', $_POST["company"]);
$stmt->bindValue(':content', $_POST["content"]);
$stmt->bindValue(':pdate', $_POST["pdate"]);
$result = $stmt->execute();
$conn = null;
if ($result) {
header('location:query.php');
}
else {
echo "ERROR in INSERT";
}
header('location:query.php');
}
else {
echo "ERROR";
}
?>
PDO提供另外一種更簡短的寫法,如果POST的欄位跟資料表的欄位是一致的話,可以直接利用execute,因為execute會接受一個陣列,並把陣列裡的內容跟參數做對應 (詳參: INSERT query using PDO)。
<?php
require 'db.php';
if ( !empty($_POST)) {
// insert data
$sql="insert into job (company, content, pdate) values (:company, :content, :pdate)";
$stmt = $conn->prepare($sql);
$result = $stmt->execute($_POST);
$conn = null;
if ($result){
header('location:query.php');
}
else {
echo "ERROR in INSERT";
}
}
else {
echo "ERROR";
}
?>
query.php
<?php
require 'db.php';
$sql="select * from job";
$stmt = $conn->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<a href="insert.html">新增求才資訊</a>
<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><?=$job["pdate"]?></td>
</tr>
<?php
}
$conn = null;
?>
</table>
新增資料 with Class
先利用form讓使用者填寫資料
insert.html
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Insert</title>
</head>
<body>
<form method="post" action="insert.php">
<table width="40%">
<caption>新增 求才公告</caption>
<tr>
<td>求才廠商</td>
<td><input type="text" name="company"></td>
</tr>
<tr>
<td>求才內容</td>
<td>
<textarea name="content" cols="40" rows="10"></textarea>
</td>
</tr>
<tr>
<td>求才日期</td>
<td><input type="date" name="pdate"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" ></td>
</tr>
</table>
</form>
</body>
</html>
新增一筆資料的SQL是:
insert into job (company, content, pdate) values ('Apple', 'Programmer', '2019-08-29');
一些網路上的範例都是直接將$_POST的內容取代值,然後執行sql,但是為了避免sql injection,最好使用prepare (詳參: PHP Prepared Statements)
為了使用prepare,我們需要將內容以變數取代,php的語法就是:
$sql="insert into job (company, content, pdate) values (:company, :content, :pdate)";
利用prepare去設定sql statement:
$stmt = $conn->prepare($sql);
利用prepare有很多好處,在這裡的最大功能是避免sql injection。(詳參: 【網頁安全】給網頁後端新人的 SQL Injection 介紹與防範 (PHP))
先利用$_POST產生一個Job的物件
$job = new Job($_POST);
再利用bindValue或bindParam去綁定變數內容以及sql中的變數,在這個情況下,可以利用bindValue或bindParam,但兩者是有所不同的。 (詳參: 比較 PDO bindParam 和 bindValue 的不同 & PDO – bindParam 和 bindValue 的使用時機)
$stmt->bindValue(':company', $job->company);
insert.php
<?php
require 'job.php';
require 'db.php';
if ( !empty($_POST)) {
// insert data
$sql="insert into job (company, content, pdate) values (:company, :content, :pdate)";
$stmt = $conn->prepare($sql);
$job = new Job($_POST);
$stmt->bindValue(':company', $job->company);
$stmt->bindValue(':content', $job->content);
$stmt->bindValue(':pdate', $job->pdate);
$result = $stmt->execute();
$conn = null;
if ($result){
header('location:query.php');
}
else {
echo "ERROR in INSERT";
}
}
else {
echo "ERROR";
}
?>
因為希望能利用$_POST直接產生job,修改一下__construct()
完整的程式碼: 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["postid"];
$this->company = $arguments["company"];
$this->content = $arguments["content"];
$this->pdate = $arguments["pdate"];
}
//create a Job from $_POST
if (sizeof(func_get_args())==1){
//$this->postid = $arguments[0]["postid"];
$this->company = $arguments[0]["company"];
$this->content = $arguments[0]["content"];
$this->pdate = $arguments[0]["pdate"];
}
}
}
?>
PDO提供另外一種更簡短的寫法,如果POST的欄位跟資料表的欄位是一致的話,可以直接利用execute,因為execute會接受一個陣列,並把陣列裡的內容跟參數做對應 (詳參: INSERT query using PDO)。
<?php
require 'job.php';
require 'db.php';
if ( !empty($_POST)) {
// insert data
$sql="insert into job (company, content, pdate) values (:company, :content, :pdate)";
$stmt = $conn->prepare($sql);
$ result = $stmt->execute($_POST);
$conn = null;
if ($result) {
header('location:query.php');
}
else {
echo "ERROR in INSERT";
}
}
else {
echo "ERROR";
}
?>
但是,因為execute不接受物件,這樣就不能透過Job類別來處理資料了。如果需要透過Job,我們先利用$_POST產生Job物件,再把內容利用Job裡新增的一個function(toArrayInsert)將內容轉為陣列,修改如下:
<?php
require 'job.php';
require 'db.php';
if ( !empty($_POST)) {
// insert data
$sql="insert into job (company, content, pdate) values (:company, :content, :pdate)";
$stmt = $conn->prepare($sql);
$job = new Job($_POST);
$jobArray= $job->toArrayInsert();
$result = $stmt->execute($jobArray);
$conn = null;
if ($result) {
header('location:query.php');
}
else {
echo "ERROR in INSERT";
}
}
else {
echo "ERROR";
}
?>
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 */
/*method 1*/
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["postid"];
$this->company = $arguments["company"];
$this->content = $arguments["content"];
$this->pdate = $arguments["pdate"];
}
// this is converting _POST to Job
if (sizeof(func_get_args())==1){
$this->postid = isset($arguments[0]["postid"])? $arguments[0]["postid"] : 0;
//create a Job from $_POST
$this->company = $arguments[0]["company"];
$this->content = $arguments[0]["content"];
$this->pdate = $arguments[0]["pdate"];
}
}
function toArrayInsert(){
//this will return all variables without postid
return array(
"company"=>$this->company,
"content"=>$this->content,
"pdate"=>$this->pdate);
}
function toArray(){
//this will return all variables includes postid
return get_object_vars($this);
}
}
?>
query.php
<?php
require 'job.php';
require 'db.php';
$sql="select * from job";
$stmt = $conn->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_CLASS, 'Job');
?>
<a href="insert.html">新增求才資訊</a>
<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><?=$job->pdate?></td>
</tr>
<?php
}
$conn = null;
?>
</table>
** 作業 **
利用SQL產生以下的資料表:
CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT ,
`account` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`name` varchar(10) NOT NULL,
`created_at` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
匯入範例檔案的 practice.sql,生成貼圖清單資料表。
請利用上週的登入介面,在登入介面中增加一個連結讓使用者可以註冊
請參考以上的範例及users資料表,寫一個註冊程式,讓使用者可以自行註冊
管理者登入成功後可以看到所有的註冊資料,未登入者不可以看到所有人的註冊資料
請利用上次的style sheet
參考資料
Read
Create & Read
Update & Delete