Relations

2019/12/29

當資料表之間建立關聯之後，新增或修改資料就必須要注意資料表之間的關係。

假如purchase資料表中，包含user_id、sticker_id、created_at，user_id是外鍵(foreign key)連接到users的主鍵id及stickers的主鍵id。也就是說，在新增一筆purchase時必須確定user_id是存在於users，sticker_id也必須存在於stickers。

ALTER TABLE `purchases`

ADD CONSTRAINT `purchase_sticker` FOREIGN KEY (`sticker_id`) REFERENCES `stickers` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,

ADD CONSTRAINT `purchase_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

完整資料表的sql如下:

-- phpMyAdmin SQL Dump

-- version 4.9.0.1

-- https://www.phpmyadmin.net/

-- 主機： localhost

-- 產生時間：

-- 伺服器版本： 8.0.17

-- PHP 版本： 7.3.8

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET AUTOCOMMIT = 0;

START TRANSACTION;

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

-- 資料庫： `web_mid_exam`

-- --------------------------------------------------------

-- 資料表結構 `purchases`

CREATE TABLE `purchases` (

`id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

`sticker_id` int(11) NOT NULL,

`created_at` datetime NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 傾印資料表的資料 `purchases`

INSERT INTO `purchases` (`id`, `user_id`, `sticker_id`, `created_at`) VALUES

(1, 1, 1, '2019-12-29 10:28:46'),

(2, 1, 2, '2019-12-29 10:33:32'),

(3, 1, 3, '2019-12-29 11:43:19'),

(4, 74, 1, '2019-12-29 17:52:45'),

(5, 74, 2, '2019-12-29 17:52:49'),

(6, 74, 3, '2019-12-29 17:52:52');

-- --------------------------------------------------------

-- 資料表結構 `stickers`

CREATE TABLE `stickers` (

`id` int(11) NOT NULL,

`title` varchar(30) NOT NULL,

`author` varchar(10) NOT NULL,

`description` text NOT NULL,

`price` tinyint(4) NOT NULL,

`created_at` date NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 傾印資料表的資料 `stickers`

INSERT INTO `stickers` (`id`, `title`, `author`, `description`, `price`, `created_at`) VALUES

(1, '自由之翼貼圖特輯第一彈', 'TT', '最可愛及搞笑的資管系童鞋們，讓我們看看他們在宿營有什麼有趣的模樣吧！', 60, '2019-10-28'),

(2, '自由之翼貼圖特輯第二彈', 'TINY', '大家最愛的自由之翼小夥伴又來啦！這群認真又不失搞笑的士官長們這次又會帶來什麼可愛的表現呢？', 60, '2019-11-11'),

(3, '資管小棒槌貼圖特輯', '踢妮', '可愛的士官長第三彈！不用多說自己看ㄅ❤︎', 60, '2019-11-11'),

(4, '古人撩妹語錄', 'Dcard', '課本沒教的古人名言，撩妹篇。<br>\r\n古人除了寫詩、搞發明還會幹嘛？沒事當然還要來撩撩妹啦。快看哪一句GET到你的心❤︎\r\n', 30, '2019-11-25'),

(5, '汗語字典', 'IG', '課本沒教的單字。<br>\r\n蛤？原來是這個意思啊。看貼圖，長知識。', 30, '2019-11-25');

-- --------------------------------------------------------

-- 資料表結構 `users`

CREATE TABLE `users` (

`id` int(11) NOT NULL,

`role` char(1) NOT NULL,

`account` varchar(30) NOT NULL,

`name` varchar(20) DEFAULT NULL,

`password` varchar(30) NOT NULL,

`credit` int(11) NOT NULL DEFAULT '0',

`created_at` date NOT NULL,

`updated_at` datetime DEFAULT NULL,

`deleted_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 傾印資料表的資料 `users`

INSERT INTO `users` (`id`, `role`, `account`, `name`, `password`, `credit`, `created_at`, `updated_at`, `deleted_at`) VALUES

(1, 'M', '405401372', '伍庭儀', '123456', 500, '2019-10-27', '2019-12-29 16:30:13', NULL),

(2, 'M', '405401607', '高子軒', '1234567', 0, '2019-10-27', '2019-12-29 16:30:30', NULL),

(74, 'C', '756951', 'Ben Wu', '123456', 0, '2019-12-29', NULL, NULL);

-- --------------------------------------------------------

-- 資料表結構 `wishes`

CREATE TABLE `wishes` (

`id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

`sticker_id` int(11) NOT NULL,

`created_at` datetime NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 傾印資料表的資料 `wishes`

INSERT INTO `wishes` (`id`, `user_id`, `sticker_id`, `created_at`) VALUES

(2, 1, 3, '2019-12-29 11:43:39'),

(9, 74, 1, '2019-12-29 17:53:05');

-- 已傾印資料表的索引

-- 資料表索引 `purchases`

ALTER TABLE `purchases`

ADD PRIMARY KEY (`id`),

ADD KEY `purchase_sticker` (`sticker_id`),

ADD KEY `purchase_user` (`user_id`);

-- 資料表索引 `stickers`

ALTER TABLE `stickers`

ADD PRIMARY KEY (`id`);

-- 資料表索引 `users`

ALTER TABLE `users`

ADD PRIMARY KEY (`id`);

-- 資料表索引 `wishes`

ALTER TABLE `wishes`

ADD PRIMARY KEY (`id`),

ADD KEY `wish_user` (`user_id`),

ADD KEY `wish_sticker` (`sticker_id`);

-- 在傾印的資料表使用自動遞增(AUTO_INCREMENT)

-- 使用資料表自動遞增(AUTO_INCREMENT) `purchases`

ALTER TABLE `purchases`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

-- 使用資料表自動遞增(AUTO_INCREMENT) `stickers`

ALTER TABLE `stickers`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

-- 使用資料表自動遞增(AUTO_INCREMENT) `users`

ALTER TABLE `users`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=75;

-- 使用資料表自動遞增(AUTO_INCREMENT) `wishes`

ALTER TABLE `wishes`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

-- 已傾印資料表的限制式

-- 資料表的限制式 `purchases`

ALTER TABLE `purchases`

ADD CONSTRAINT `purchase_sticker` FOREIGN KEY (`sticker_id`) REFERENCES `stickers` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,

ADD CONSTRAINT `purchase_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

-- 資料表的限制式 `wishes`

ALTER TABLE `wishes`

ADD CONSTRAINT `wish_sticker` FOREIGN KEY (`sticker_id`) REFERENCES `stickers` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,

ADD CONSTRAINT `wish_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

那如何確保這件事呢? 我們可以利用登錄時，將user_id記錄下來，另外，購買的按鈕也放在貼圖中，利用隱藏欄位自動的代入這兩個欄位，這樣就可以減少資料表資料不一致的問題。

stickerPage.php

</form>

controllers/purchase_process.php

<?php

require __DIR__ . '/../etc/bootstrap.php';

//確認是否有修改表單資料

if (!empty($_POST)) {

// =============================================================================

// = 處理送來的表單資料

// =============================================================================

$userId = $_POST["userId"] ?? "";

$stickerId = $_POST["stickerId"] ?? "";

//沒有登入資訊跳轉登入頁

if(!$userId){

header("Location:../login.php");

die;

}

/* =============================================================================

* = 新增購買記錄

* =============================================================================

**/

$purchaseResult = addPurchase($conn, [

"user_id" => $userId,

"sticker_id" => $stickerId,

]);

header("Location:../stickerPage.php?sticker=$stickerId&purchase=$purchaseResult");

die();

}

header("Location:../stickerPage.php");

sql.php裡的addPurchase():

/**

* 新增購買紀錄

* @param PDO $conn PDO實體

* @param array $data 要新增的購買記錄資料

* @return boolean 執行結果

function addPurchase($conn, $data = [])

{

$sql = "insert into `purchases` (`user_id`, `sticker_id`, `created_at`) values (:user_id, :sticker_id, :created_at)";

$stmt = $conn->prepare($sql);

$purchaseData = [

'user_id' => $data['user_id'], /**完成資料綁定 */

'sticker_id' => $data['sticker_id'], /**完成資料綁定 */

'created_at' => $data['created_at'] ?? date('Y-m-d H:i:s'),

];

$result = $stmt->execute($purchaseData);

//回傳執行結果

return $result;

}

如果無法自動代入，最好也不要讓使用者直接輸入，比較好的方式是使用下拉式選單來選。

<?php

require __DIR__ . '/etc/bootstrap.php';

$users = fetchAllUser($conn); //function in sql.php

$user_options="";

foreach($users as $user){

$user_options = $user_options."<option value='".$user->id."'>".$user->name."</option>";

}

$stickers = fetchAllStickers($conn); //function in sql.php

$sticker_options="";

foreach($stickers as $sticker){

$sticker_options = $sticker_options."<option value='".$sticker->id."'>".$sticker->title."</option>";

}

user:

<?=$user_options?>

</select>

sticker:

<?=$sticker_options?>

</select>

</form>

另外，除了在程式的控制之外，資料庫也會根據建立好的限制式控制資料不一致的問題。例如:

ALTER TABLE `purchases`

ADD CONSTRAINT `purchase_sticker` FOREIGN KEY (`sticker_id`) REFERENCES `stickers` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,

ADD CONSTRAINT `purchase_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

在一般的資料庫中可利用restrict或cascade來控制，以purchase跟users之間的關聯而言，restrict就是當users的id要更新或刪掉一個使用者時，會先檢查是否在purchases裡有對應資料，如果有，就不准使用者的id被更新或整筆被刪除 (詳參: mysql的foreign key介紹、13.1.20.6 FOREIGN KEY Constraints)。cascade則是當users的id要更新時，一併更新purchase裡的user_id，如果users裡的資料被刪除，會一併刪除purchase裡的資料。

目前purchases的設計是相當簡單的，一般而言，會是有個訂單(對使用者是purchase order，對商家是sales order)的概念，每張訂單會有多個產品的。這時候就要需要兩張資料表order_master及order_detail，如果訂單的編號(order_master)是由資料庫自動產的話，那在產生訂單(order_master)之後，要先取得上一筆新增的序號，才能根據這個序號將對應的訂單編號也放到order_detail裡 (詳參:PDO 取得上一筆新增的序號 lastInsertId() 和取得變動的行數數量 rowCount())。