# [BE101] 用 PHP 與 MySQL 學習後端基礎
###### tags:`Backend`
[TOC]
> [SQL語法集](https://www.1keydata.com/tw/sql/sql.html)
> 帳密不要放到網路上
## SQL !== 資料庫
資料庫 > MySQL 被收購之後，社群就自己做了一個類似的 MariaDB
==SQL, php > 程式語言==
>資料庫其實是一種特殊格式的檔案，這種檔案必需要透過特殊介面 (資料庫軟體) 來進行讀寫。由於這個特殊介面已經針對資料的查詢、寫入做過最佳化設計， 因此很適合多人同時寫入與查詢的工作。
>針對資料庫的語法有所謂的 SQL 標準語法，任何根據這種資料檢索語法發展出來的資料庫，就稱為 SQL 資料庫。 比較知名的自由軟體資料庫系統有 MySQL 及 PostgreSQL ，其中 MySQL 的使用率又比較高一些。
>BY *vbird*
die($connection->error);
$err_code = $connection->errno;
// 發生其他錯誤的處理
if($err_code) {
echo '伺服器連線錯誤';
echo $err_code;
die($connection->error);
}
header('Location: update_comment.php?errCode=0' . $_POST['ID']);
## PHP 基礎
==request > apache(server) > php > html > apache > response==
> server 也是一個程式
> php 的輸出入一般會先給 server 做**預處理**，所以不會直接輸出 .php 的原始程式碼
* 網址跟實際檔案的對稱關係可以自己設定
## 資料庫基礎
server > 程式，專門處理 request / response
資料庫 > 程式，專門處理資料
* 資料庫有兩種：
relational database，關聯性資料庫>相同類型的資料放在一起，例如 MySQL, PostgreSQL，靠著彼此間有相關的部份把 TABLE 連在一起
NoSQL(not only SQL)，例如 MongoDB
* phpmyadmin : 寫在 php 上的圖形化資料庫管理程式==(GUI)==
* schema : 資料庫的結構
* ==比較 primary key, foreign key==
The main purpose of primary key is to identify the uniqueness of a row, where as unique key is to prevent the duplicates
https://stackoverflow.com/questions/54527999/why-we-need-a-primary-key/54528079
https://stackoverflow.com/questions/655446/what-exactly-is-a-foreign-key
* index，方便查詢特定欄位做定位用，要花時間，一個索引可以對應多個欄位
* [distinct](https://www.1keydata.com/tw/sql/sqldistinct.html)
* 記得操作網頁前要把 browser 給 disable cache，免得一直讀取快取而不讀取原始檔案
### 基礎語法
跟資料庫連線的語法有兩種，`$link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');` 和 `$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db')`，後者比較新而且錯誤處理比較容易，[參考](https://stackoverflow.com/questions/15707696/new-mysqli-vs-mysqli-connect)，[BE101] 裡面有提到可以使用 `->` 來調用函數，查詢之後發現 `mysqli` 是個 Class（所以查詢[官方文件](https://www.php.net/manual/en/class.mysqli.php)的時候才會看到 `::` 這種奇妙的符號？），底下有很多函式可以調用（類似 prototype 的概念...?）
```php=
$sql = "SELECT * FROM table WHERE 1";
$sql = "INSERT INTO table (id, role) VALUES (value1, value2)";
$sql = "UPDATE table SET id = value1, role = value2 WHERE 1";
$sql = "DELETE FROM table WHERE 1";
$result = $connection->query($sql); // 考慮到資安因素，建議使用 stmt
$row = $result->fecth_assoc();
```
`WHERE = ` 設定額外篩選條件 (SELECT, UPDATE 會用到)
使用 `is_deleted = 1` ˊ這個屬性，來代表資料是被刪除的，而不是直接從資料庫刪掉(沒救)
### 再次複習 PHP 執行流程
動態網頁會在 送出 response 檔案給 SERVER 之前，先執行 PHP ，把檔案裏面 php 的部分先跑過一次，再給 server，是否要先執行某個檔案這件事是可以被 server 設定的
> 這次使用的 XAMPP 會預執行 .php 檔
### 實作
> 筆記在實作的 xampp/htdocs/Test 資料夾裡面
* php 是用來操作 DB 的程式語言，一個 DB 可以對稱很多個 php
* 通常會把 php 連線到 DB 的 php程式碼獨立出來寫，然後再用 `require_once('conn.php');` 這段程式碼來引入
### Cookie
相同 domain 的 cookie 可以互相讀取
cookie 由瀏覽器來做設定和傳送，存放在客戶端
```php=
setcookie ( string $name , string $value = "" , array $options = [] );
// 另一種讓 COOKIE 過期的方法
setcookie('name', 'content', 1);
```
## 修正問題
永遠不要相信 client 的資料(server以外)
* 要做 token 驗證
### PHP 內建 session 機制
> COOKIE 和 session 都相當於識別證的概念
跟 cookie 不一樣，sessionID 存放在 SERVER，存放內容如下
```php=
// 開啟內建的 session
$session_start();
// 1.產生 PHPSESSID(token)
// 2.把 $username 寫入檔案
// 3.最後設定 cookie "PHPSESSID: ###"
$_SESSION['username'] = $username;
// 1.從 cookie 裡面讀取 PHPSESSID(token)
// 2.再從檔案裡面讀取 PHPSESSID(token) 的內容
// 3.最後放到 $username
$username = $_SESSION['username'];
// 清除 PHPSESSID, PHPSESSID 還在瀏覽器但是內容已經清空了
$seccion_destroy();
```
PHPSESSID 是存在 xampp/tmp 裡面，我用不同帳號去登入和登出做測試，後來發現同個瀏覽器的 PHPSESSID 是不變的，但檔案裏面寫入的內容會根據登入的帳號不同而改變
### hash 存放密碼
hash 雜湊函數，例如: MD5, SHA265
它是一個單向的函數，輸入一樣，輸出的結果保證一樣，不同輸入可能會有相同輸出(碰撞)，因此沒辦法從輸出反推出輸入
所以如果使用者忘記密碼了，是沒辦法從 server 給出原本的密碼，只能重設密碼XD(除非對方是存明碼)
加鹽 salting，自動幫使用者的輸入加上一段亂數，降低被破解的可能
> [補充](https://medium.com/starbugs/what-are-encoding-encrypt-and-hashing-4b03d40e7b0c)
### XSS
cross-site-scripting，使用==htmlspecialchars==來解決
但資料庫存的通常還是使用者輸入的原始資料，在顯示的時候才做處理
### SQL injection
加入`'#` 讓#後面的語法被註解掉，方法五花八門...，使用==prepared statement==來解決
```php=
$sql = 'insert into `message-board`(`nickname`, `content`) values (?, ?)';
$stmt = $connection->prepare($sql);
$stmt->bind_param('ss', $nickname, $content);
$result = $stmt->execute();
// 正確新增的話會回傳 1
if(!$result) {
die($connection->error);
}
$result = $stmt->get_reults(); // 之後在做 fetch_assco()
```
### 加上分頁功能
limit 和 offset
limit: 限制取出的資料量
offset: 設定 limit 的偏移量
### 加上權限檢查
## 留言板 - API 篇
之前W9和11的作業的作法是: 把 DB 資料拿出來 > 把資料跟 HTML 結合 > 回傳 HTML
現在的 API的作法則是: 把 DB 資料拿出來 > 把資料變成某種格式 > 回傳特定格式的資料 > HTML 再做動態新增
### API 實作
## 實作小細節
* row 有相同名稱，會以後面出現的為主:
* 把相同的HTML 模板獨立出來:`require_once('./nav.php');`
* 後台、`handle_*`系列也要做 SESSION 或其他驗證，避免使用者套過修改網址、query string、直接對表單送出資料(不透過瀏覽器)而進入`require('check_permission.php');`
* 只輸出資料的一部份:`substr(); // limit string`
* redirect 可以透過 php 或 DOM 來達成:
```javascript=
goBack(-2)
function goBack(page) {
window.history.go(page)
}
```
* css 的文章部分記得要加上 `white-space: pre-line;`，才會正常斷行R
### JSON String ? decode ? encode ? parse ? stringfy ?
為了處理正確地在 JS 與 PHP 之間傳送和讀取 JSON，我卡關好久之後(整整兩天)，在血與淚的研究和發問之後，終於比較懂這三者之間的差異了，寫個筆記紀錄一下，因為感覺未來很可能再踩進同一個坑QQ
先建立個觀念：
所謂的 JSON 就只是一個 string type 的資料，它**不是**物件、陣列，所以我通常都稱它為 JSON String，以避免自己又搞混。
既然 JSON String 是個 string，那個 JS、PHP 要怎麼讀懂它所要表達的內容呢？這時候就要靠 `json_decode()` (PHP) 或是 `JSON.parse()` (JS)啦，
再延伸一題，如果我們想要自己產生一個 JSON String 要怎麼做呢？一般會用陣列或物件型式的變數來蒐集資料，然後再用`json_encode()` (PHP) 或是 `JSON.stringfy()` (JS) 來轉換成 JSON String。
總結：文章標題提到的的概念差不多是這樣，PHP 的 json_decode() 和 JavaScript 的 JSON.parse() 差不多，目的是得到陣列或物件，以方便操作他們。PHP 的 json_encode() 和 JavaScript 的 JSON.stringfy() 差不多，目的是得到 JSON String
#### JSON: 從 PHP 傳到 JS
傳送端(PHP)
這裡就要考慮要如何產生 JSON String 了，
作法之一是自己把需要的資料取出來，建立 array 來存放資料，這個作法所產生的資料型式實際上是一個**陣列**，需要使用 `json_encode()` 來轉換成 JSON String
作法之二是直接在後端的資料庫建立一個 JSON 型式的欄位來做存取，例如下圖，從該欄位取出的資料實際上就是 JSON String 了，所以不用特地再做`json_encode()` 來轉換
接收端(JS)
要記得做 `JSON.parse()` 來轉換成物件或陣列
#### JSON: 從 JS 傳到 PHP
傳送端(JS)
一樣是要自己先產生 JSON String，一般會使用陣列或物件來蒐集資料，蒐集完之後記得要做`JSON.stringfy()` 來轉換成 JSON String 再做傳送，我在這邊滿順利的，就不介紹了XD
傳送的方式以 POST 為例，會遇到 CORS 和 preflight 的問題，參考[這篇文章](https://shubo.io/what-is-cors/#preflight-request-%E9%A0%90%E6%AA%A2%E8%AB%8B%E6%B1%82)設置對應的 header 之後就可以解決
接收端(PHP)
這邊就神奇了，因為我是直接傳送 JSON String，例如下面程式碼，PHP 裡面寫成這樣才可以正確拿到資料(`$obj = json_decode(file_get_contents('php://input'), true);`)，有查到[W3SCHOOL](https://www.w3schools.com/js/js_json_php.asp) 提到的做法，但是在我這邊無效，原因還不知道XD
> 21/07/16更新: 原因是 jQuery 的 request 忘記加上 content-type
```javascript=
const url = 'http://localhost/dashboard/Todo/api_handle_add_todo.php'
const request = new XMLHttpRequest
request.open('POST', url, true)
request.setRequestHeader("Content-Type", "application/json;charset=UTF-8")
request.addEventListener('load', (e) => {
if (!(200 <= request.status < 400)) {
alert('123Oops, something is going wrong:', request.status)
}
let json = ''
try {
json = JSON.parse(request.response)
} catch (err) {
alert('Oops, something is going wrong:' + err)
}
if(!json.success) {
alert(json.message)
return
}
alert(json.message)
})
request.send(data) // data 已經用 JSON.stringfy() 做轉換
```
## 程式導師實驗計畫：Lesson 8-2 之資料庫
NoSQL, Not only SQL
* 沒有 schema
* 用 key-value 存（JSON）
* 不支援 JOIN
* 通常用來存結構不固定的資料，ex log
## Transaction 交易
資料庫做一次 query 不能完成的操作，例如轉帳。為了保證資料庫操作的正確，需要符合 ==ACID== 這四個特性：
原子性 atomicity: 確保操作全部成功或全部失敗
一致性 consistency: 操作前和操作後，錢的總數要相同
隔離性 isolation: 不能同時改一個值
持久性 durability，動作完成之後，資料還在
例如↓，在`commit()`之後才會執行 query，所以也可以在一次`commit()`裡面塞很多 query，然後依次執行，效率會比較好。
```php=
$conn->autocommit(FALSE);
$conn->begin_transaction();
$conn->query("UPDATE FROM money SET amount = 20");
$conn->query("UPDATE FROM money SET sum = 10");
$conn->commit();
```
(MYISAM 不支援、innoDB 支援)
## Lock 鎖
為了避免多筆 request 對資料庫操作可能產生的錯誤，需要==在每一次的 trancaction 裡面加上 LOCK==，==確保第一次動作完成才進行下一次動作==。
例如↓，第 3 行把 table 'products' 的 id = 1 row 鎖起來，等到`UPDATE`完之後才可以做下一次的操作。``
```php=
$conn->autocommit(FALSE);
$conn->begin_transaction();
$conn->query("UPDATE amount FROM products WHERE id = 1 for update");
$conn->query("UPDATE FROM products SET sum = 10");
$conn->commit();
```
做交易的期間DB 會被鎖定，有很多種[鎖定模式](http://caryhsu.blogspot.com/2011/09/sql-server.html)
## View ==虛擬的表格==
把不同表格的部分資料取出來，產生一個"檢視表"
* 跟一般的資料表不同， view 是==唯讀的==
* 限制使用者可以看到的資料
```sql=
CREATE VIEW my_viwe AS
SELECT * FROM my_table
```
[補充](https://www.fooish.com/sql/view.html)
## stored procedure
[mariaDB Doc](https://mariadb.com/kb/en/stored-procedure-overview/)
[補充](https://ryanchen34057.github.io/2019/09/25/storedProcedure/)
一般的 function
```sql=
SELECT SUM(prive) FROM my_table WHERE 1
```
stored procedure，==自己寫的 function，會存在 db 預存程序那邊==
```sql=
DELIMITER //
CREATE PROCEDURE(id INT)
BEGIN
SELECT id FROM my_table WHERE id = id;
END //
DELIMITER ;
```
```sql=
CALL getorder(1)
```
## Triggers 事情發生前/後要做的事情
[maria DB](https://mariadb.com/kb/en/trigger-overview/)
==類似 JS 的監聽器==，ex 自動化的 log 紀錄?
```sql=
DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
INERT INTO my_table(id, name, price, action)
VALUE(OLD.id, OLD.name, OLD.price, 'UPDATE');
<!-- OLD, 更動之前的值 NOW更動之後的值 -->
<!-- my_table 會存放 OLD(update前)的值 -->
END //
DELIMITER ;
```
做更新?當下的價格
## CS75 (Summer 2012) Lecture 9 Scalability Harvard Web Development David Malan
Sign in with Wallet
Connect another wallet