---
# System prepended metadata

title: '[南應大資管]網頁程式設計：資料庫互動'
tags: [HTML, MariaDB, PHP, 台南應用科技大學資訊管理系, MySQL]

---

# [南應大資管]網頁程式設計：資料庫互動

###### 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>
```

