PHP零基础教程-第七课--数据库进阶与RESTful API

一言准备中...

一、MySQL数据库进阶

1. 关联查询

(1) 创建关联表

-- 文章表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    content TEXT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

(2) 内连接查询

<?php
// 查询文章及其作者信息
$sql = "SELECT articles.*, users.username 
        FROM articles 
        INNER JOIN users ON articles.user_id = users.id";
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    echo "标题: ".$row['title']." (作者: ".$row['username'].")<br>";
    echo "内容: ".substr($row['content'], 0, 50)."...<br><br>";
}
?>

(3) 左连接与右连接

<?php
// 左连接：即使没有文章也显示用户
$sql = "SELECT users.username, COUNT(articles.id) as article_count
        FROM users 
        LEFT JOIN articles ON users.id = articles.user_id
        GROUP BY users.id";
?>

2. 事务处理

<?php
// 开启事务
$conn->begin_transaction();

try {
    // 插入用户
    $stmt = $conn->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
    $stmt->bind_param("ss", $username, $password);
    $stmt->execute();
    $user_id = $conn->insert_id;

    // 插入文章
    $stmt = $conn->prepare("INSERT INTO articles (title, content, user_id) VALUES (?, ?, ?)");
    $stmt->bind_param("ssi", $title, $content, $user_id);
    $stmt->execute();

    // 提交事务
    $conn->commit();
    echo "操作成功！";
} catch (Exception $e) {
    // 回滚事务
    $conn->rollback();
    echo "操作失败: " . $e->getMessage();
}
?>

二、RESTful API开发基础

1. RESTful API设计原则

使用HTTP方法对应CRUD操作：
- GET：读取资源
- POST：创建资源
- PUT/PATCH：更新资源
- DELETE：删除资源
使用JSON格式传输数据
无状态通信

2. 基础API实现

(1) 文章API路由（api.php）

<?php
header("Content-Type: application/json");
require 'db_connect.php'; // 数据库连接文件

$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'],'/'));
$table = preg_replace('/[^a-z0-9_]+/i','',array_shift($request));
$key = array_shift($request);

switch ($method) {
    case 'GET':
        if(empty($key)) {
            getArticles();
        } else {
            getArticle($key);
        }
        break;
    case 'POST':
        createArticle();
        break;
    case 'PUT':
        updateArticle($key);
        break;
    case 'DELETE':
        deleteArticle($key);
        break;
}

function getArticles() {
    global $conn;
    $result = $conn->query("SELECT * FROM articles");
    echo json_encode($result->fetch_all(MYSQLI_ASSOC));
}

function getArticle($id) {
    global $conn;
    $stmt = $conn->prepare("SELECT * FROM articles WHERE id=?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    echo json_encode($stmt->get_result()->fetch_assoc());
}
?>

(2) 创建文章API

function createArticle() {
    global $conn;
    $data = json_decode(file_get_contents('php://input'), true);

    $stmt = $conn->prepare("INSERT INTO articles (title, content, user_id) VALUES (?, ?, ?)");
    $stmt->bind_param("ssi", $data['title'], $data['content'], $data['user_id']);
    $stmt->execute();

    echo json_encode(['id' => $conn->insert_id]);
}

3. API测试（使用Postman）

获取所有文章：GET /api/articles
获取单篇文章：GET /api/articles/1
创建新文章：POST /api/articles (Body: JSON格式数据)
更新文章：PUT /api/articles/1 (Body: JSON格式数据)
删除文章：DELETE /api/articles/1

三、博客系统实现

1. 系统功能设计

用户认证（注册/登录/注销）
文章管理（创建/编辑/删除）
文章列表与分页
文章分类功能

2. 核心代码实现

(1) 数据库设计

-- 分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 文章分类关联表
CREATE TABLE article_category (
    article_id INT,
    category_id INT,
    PRIMARY KEY (article_id, category_id),
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

(2) 分页查询

<?php
function getArticles($page = 1, $perPage = 10) {
    global $conn;
    $offset = ($page - 1) * $perPage;

    $stmt = $conn->prepare("SELECT * FROM articles LIMIT ?, ?");
    $stmt->bind_param("ii", $offset, $perPage);
    $stmt->execute();

    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}
?>

(3) 前端调用API示例

// 使用Fetch API获取文章列表
fetch('/api/articles')
    .then(response => response.json())
    .then(data => {
        data.forEach(article => {
            // 渲染文章到页面
        });
    });

四、安全加固

1. API认证（JWT实现）

(1) 生成JWT Token

<?php
use Firebase\JWT\JWT;

function generateToken($user_id) {
    $secret_key = "your_secret_key";
    $payload = [
        'user_id' => $user_id,
        'exp' => time() + 3600 // 1小时后过期
    ];
    return JWT::encode($payload, $secret_key);
}
?>

(2) 验证中间件

<?php
function authenticate() {
    $headers = getallheaders();
    if(!isset($headers['Authorization'])) {
        http_response_code(401);
        die(json_encode(['error' => '未提供Token']));
    }

    try {
        $token = str_replace('Bearer ', '', $headers['Authorization']);
        $decoded = JWT::decode($token, "your_secret_key", ['HS256']);
        return $decoded->user_id;
    } catch (Exception $e) {
        http_response_code(401);
        die(json_encode(['error' => '无效Token']));
    }
}
?>