# [南應大資管]網頁程式設計：資料庫互動
###### tags: `PHP` `HTML` `台南應用科技大學資訊管理系` `MySQL` `MariaDB`
----
## SQL 指令
- 連接資料庫：UI/CLI
- 建立資料庫
- 建立使用者
- 建立資料表
----
### 建立資料庫
編碼/字元集
```sql
CREATE DATABASE 資料表名稱;
或
CREATE DATABASE 資料表名稱 CHARACTER SET 字元集 COLLATE 字元定序;
```
```sql
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```
```sql
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
```sql
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_general_ci;
```
----
### 使用資料庫
```sql
USE 資料庫;
```
```sql
USE mydb;
```
----
### 建立使用者
```sql
CREATE USER 帳號@來源 IDENTIFIED BY '密碼';
```
```sql
CREATE USER apple@localhost IDENTIFIED BY '1234';
```
```sql
CREATE USER apple@'localhost' IDENTIFIED BY '1234';
```
```sql
CREATE USER apple@'%' IDENTIFIED BY '1234';
```
----
### 賦予使用者權限
```sql
GRANT 權限 ON 資料庫.資料表 TO 帳號@來源;
```
```sql
GRANT ALL PRIVILEGES ON mydb.* TO apple@localhost;
```
```sql
GRANT select,insert ON mydb.xxxx TO apple@'%';
```
----
### 建立資料表
```sql
CREATE TABLE 資料表名稱 (
欄位名稱 欄位格式,
欄位名稱 欄位格式,
欄位名稱 欄位格式
) 其他定義;
```
```sql
CREATE TABLE 資料表名稱 (
欄位名稱 欄位格式,
欄位名稱 欄位格式,
欄位名稱 欄位格式
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
----
### 資料表欄位
```sql
欄位名稱 格式 其他定義
```
- 常用格式
- 整數 INTEGER, INT, BIGINT
- 浮點數 DECIMAL, NUMERIC, FLOAT, DOUBLE
- 字串 CHAR, VARCHAR, BLOB, TEXT
- 日期 DATE, DATETIME, TIMESTAMP
- 常用定義
- DEFAULT 預設值
- NOT NULL, NULL 允許空值
- AUTO_INCREMENT 自動遞增
- UNIQUE [KEY] 唯一
- [PRIMARY] KEY (主)鍵
```sql
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
```
```sql
usr VARCHAR(36)
```
```sql
msg VARCHAR(288)
```
----
### PUT IT ALL TOGETHER
```sql
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mydb;
CREATE USER apple@localhost IDENTIFIED BY '1234';
GRANT ALL PRIVILEGES ON mydb.* TO apple@localhost;
CREATE TABLE board (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
usr VARCHAR(36),
msg VARCHAR(288)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
----
## PHP
- mysql / mysqli / PDO
- 建立資料庫連線
----
### 建立資料庫連線
```php
new PDO('連線字串','帳號','密碼',參數)
```
- mysql 連線的資料庫系統
- host= 資料庫主機位置或領域名稱
- dbname = 資料庫名字
- charset= 字元編碼!!
- 帳號, 密碼 對照 當初的SQL指令 ```CREATE USER apple@localhost IDENTIFIED BY '1234'```
```php
try {
$pdo = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8mb4',
'apple','1234',
[
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
] );
} catch (Exception $e) {
// echo $e->getMessage(); // 顯示錯誤訊息
exit('發生錯誤');
}
```
參數
- PDO::ATTR_EMULATE_PREPARES 模擬prepare指令
- false 取消使用真實prepare加快速度
- PDO::ATTR_DEFAULT_FETCH_MODE 預設取得資料模式
- PDO::FETCH_ASSOC 使用關係陣列
- PDO::ATTR_ERRMODE 發生錯誤時的反應方式
- PDO::ERRMODE_EXCEPTION 例外
----
### 執行指令
準備 SQL 指令，然後執行
```php
$stmt = $pdo->prepare('SQL指令');
$stmt->execute();
```
準備帶有嵌入參數位置的 SQL 指令，然後執行時帶入參數
```php
$stmt = $pdo->prepare('帶 ? 的SQL指令');
$stmt->execute([值,值,值]);
```
例如：
```php
$stmt = $pdo->prepare('DELETE FROM board');
$stmt->execute();
```
例如：
```php
$stmt = $pdo->prepare('INSERT INTO board (usr,msg) VALUES (?,?)');
$stmt->execute(['cat','this is a book']);
// 可以執行多次
$stmt->execute(['lion','i wanna know']);
$stmt->execute(['elephant','somewhere over the ranbower']);
```
----
### CRUD
Create 新增
Read 讀取
Update 修改
Delete 刪除
----
### Create
顯示畫面
```php
<form method="POST" action="XXXX.php">
<input type="text" name="user">
<textarea name="message"></textarea>
<button type="submit">張貼新文章</button>
</form>
```
XXXX.php
```php
$pdo = 略
$user = $_POST['user'];
$message = $_POST['message'];
$stmt = $pdo->prepare('INSERT INTO board (usr,msg) VALUES (?,?)');
$stmt->execute([$user,$message]);
```
----
### execute()
```php
$pdo = 略
$user = $_POST['user'];
$message = $_POST['message'];
$stmt = $pdo->prepare('INSERT INTO board (usr,msg) VALUES (?,?)');
$stmt->execute([$user,$message]);
```
v.s.
```php
$pdo = 略
$user = $_POST['user'];
$message = $_POST['message'];
$stmt = $pdo->prepare('INSERT INTO board (usr,msg) VALUES (?,?)');
$stmt->bindParam(1, $user, PDO::PARAM_STR, 36);
$stmt->bindParam(2, $message, PDO::PARAM_STR, 288);
$stmt->execute();
```
----
### Read
```php
$pdo = 略
$stmt = $pdo->prepare('SELECT * FROM board order by id desc');
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
foreach ($data as $r) {
echo( $r->id );
echo( $r->usr );
echo( $r->msg );
}
```
----
### Update (1/2)
先取得資料，用來顯示編輯界面
```php
$pdo = 略
$id = 略
$stmt = $pdo->prepare('SELECT usr,msg FROM board where id = ?');
$stmt->execute();
if ($r = $stmt->fetch(PDO::FETCH_OBJ) ) {
echo('<form method="POST" action="XXXX.php">');
echo(' <input type="hidden" name="id" value="' . $id . '">');
echo(' <input type="text" name="user" value="' . $r->usr . '">');
echo(' <textarea name="message">' . $r->msg . '</textarea>');
echo(' <button type="submit">修改文章</button>');
echo('</form>');
}
```
預計將長出
```php
<form method="POST" action="XXXX.php">
<input type="hidden" name="id" value="XXXXX">
<input type="text" name="user" value="XXXXX">
<textarea name="message">XXXXX</textarea>
<button type="submit">修改文章</button>
</form>
```
----
### Update (2/2)
將修改後的資料填入資料庫中
```php
$pdo = 略
$id = $_POST['id'];
$user = $_POST['user'];
$message = $_POST['message'];
$stmt = $pdo->prepare('UPDATE board set usr=?,msg=? where id = ?');
$stmt->execute([$user,$message,$id]);
```
或
```php
$stmt = $pdo->prepare('UPDATE board set usr=?,msg=? where id = ?');
$stmt->bindParam(1, $user, PDO::PARAM_STR, 36);
$stmt->bindParam(2, $message, PDO::PARAM_STR, 288);
$stmt->bindParam(3, $id, PDO::PARAM_INT);
$stmt->execute();
```
----
### Delete
```php
$pdo = 略
$id = 略
$stmt = $pdo->prepare('DELETE FROM board where id = ?');
$stmt->bindParam(1, $id, PDO::PARAM_INT);
$stmt->execute();
```
或
```php
$pdo = 略
$id = 略
$stmt = $pdo->prepare('DELETE FROM board where id = ?');
$stmt->execute([$id]);
```
-----
### 換行
HTML 使用 `<br>`
`<textarea>` 中使用 `\n`
`\n` 轉 `<br>`
```php
$s = str_replace(array("\r\n", "\n", "\r"), '<br>', $s);
```
----
### 補充 mysql dump 傾印匯入
#### 匯出單一資料庫
`mysqldump -u帳號 -p 資料庫名稱 > 匯出的檔名`
`mysqldump -uaaaa -p sampledb > backup.sql`
#### 匯入單一資料庫
進資料庫系統
`myql -uroot -p`
建資料庫
`CREATE DATABASE sampledb CHARSET SET utf8mb4 COLLATE utf8mb4_unicode_ci;`
建使用者
`CREATE USER aaaa@localhost IDENTIFIED BY 'bbbb';`
設定使用者權限
`GRANT ALL PRIVILEGES ON sampledb.* TO aaaa@localhost;`
離開資料庫系統
`exit`
匯入
`mysql -u帳號 -p 資料庫名稱 < 匯入的檔名`
`mysql -uaaaa -p samepledb < backup.sql`
#### 匯入單一資料庫 (其他做法)
準備文字檔 `init.sql`, 內容如：
```
CREATE DATABASE sampledb CHARSET SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER aaaa@localhost IDENTIFIED BY 'bbbb';
GRANT ALL PRIVILEGES ON sampledb.* TO aaaa@localhost;
```
匯入
```
mysql -uroot -p < init.sql
mysql -uaaaa -p samepledb < backup.sql
```
#### 匯出全部的資料庫
(用root, 包括帳號資料表..設定資料表...)
`mysqldump -uroot -p --all-databases > backup.sql`
#### 匯入全部的資料庫
匯入
`mysql -uroot -p < backup.sql`
進資料庫系統
`myql -uroot -p`
補設定權限的指令
`flush privileges`
----
## DEMO (2020/06/03)
### SQL
```sql
CREATE DATABASE sampledb CHARSET SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER aaaa@localhost IDENTIFIED BY 'bbbb';
GRANT ALL PRIVILEGES ON sampledb.* TO aaaa@localhost;
CREATE TABLE boards (
bid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
bname VARCHAR(200)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE posts (
bid INT NOT NULL,
pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(144),
message VARCHAR(288)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE comments (
pid INT NOT NULL,
cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message VARCHAR(288)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
insert into boards(bname) values ('測試'),('資管系'),('亂七八糟');
insert into posts(bid,title,message) values (1,'aaa','aaa123141231212'),
(1,'bbb','bbbdasfsfsadfas'),(2,'ccc','cccdsfsafafdfasfda'),(3,'ddd','ddddfadfaf');
```
----
### config.php
```php
<?php
const DB_USER = 'aaaa';
const DB_PASSWORD = 'bbbb';
```
----
### db.php
```php
<?php
require_once('config.php');
// 建立資料庫連線
try{
$pdo = new PDO('mysql:host=localhost;dbname=sampledb;charset=utf8mb4',
DB_USER, # 帳號
DB_PASSWORD, # 密碼
[
PDO::ATTR_EMULATE_PREPARES => false, # 不要模擬 prepare
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, # 錯誤時拋出例外
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
]);
} catch (Exception $e) {
exit('資料庫連線有問題');
}
```
----
### index.php
```php
<?php
require('db.php');
?>
<!DOCTYPE html>
<html lang="zh-Hant">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>討論區</title>
</head>
<body>
<h1>討論區</h1>
<table>
<?php
// 查詢所有的討論板名稱
$stmt = $pdo->prepare('select * from boards');
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
foreach ($data as $r) {
echo('<tr><td>');
echo("<a href=\"board.php?bid=$r->bid\">$r->bname</a>");
echo('</td></tr>');
}
?>
</table>
</body>
</html>
```
----
### board.php
```php
<?php
$bid = $_GET['bid'];
if (!$bid) {
header('Location: /', true, 302);
exit();
}
require('db.php');
?>
<!DOCTYPE html>
<html lang="zh-Hant">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>討論區</title>
</head>
<body>
<h1>討論版</h1>
<table>
<?php
// 查詢所有的討論板名稱
$stmt = $pdo->prepare('select pid,title from posts where bid=?');
$stmt->bindParam(1, $bid, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
foreach ($data as $r) {
echo('<tr><td>');
echo("<a href=\"post.php?pid=$r->pid\">$r->title</a>");
echo('</td></tr>');
}
?>
</table>
</body>
</html>
```
----
### post.php
```php
<?php
$pid = $_GET['pid'];
if (!$pid) {
header('Location: /', true, 302);
exit();
}
require('db.php');
?>
<!DOCTYPE html>
<html lang="zh-Hant">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>討論區</title>
</head>
<body>
<h1>討論版</h1>
<table>
<?php
// 查詢所有的討論板名稱
$stmt = $pdo->prepare('select title,message from posts where pid=?');
$stmt->bindParam(1, $pid, PDO::PARAM_INT);
$stmt->execute();
// TODO: 只有一筆，不用 foreach
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
foreach ($data as $r) {
echo($r->title);
echo($r->message);
}
?>
</table>
</body>
</html>
```
Sign in with Wallet
Connect another wallet