PHP零基础教程-第六课-MySQL数据库与登录注册

一言准备中...

一、MySQL数据库基础

1. MySQL简介

关系型数据库管理系统
使用SQL语言操作
典型应用场景：用户数据、商品信息、订单记录等结构化数据存储

2. 安装配置（XAMPP集成环境）

启动XAMPP控制面板
开启Apache和MySQL服务
访问 http://localhost/phpmyadmin 进入管理界面

3. 基础SQL命令

(1) 数据库操作

CREATE DATABASE mydb;    -- 创建数据库
USE mydb;                -- 选择数据库
DROP DATABASE mydb;      -- 删除数据库

(2) 数据表操作

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 删除表
DROP TABLE users;

-- 查看表结构
DESC users;

(3) 数据操作

-- 插入数据
INSERT INTO users (username, password, email)
VALUES ('admin', '123456', 'admin@example.com');

-- 查询数据
SELECT * FROM users;                     -- 查询所有
SELECT username, email FROM users WHERE id = 1;

-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 2;

二、PHP操作MySQL

1. 连接数据库（mysqli扩展）

<?php
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'mydb';

// 创建连接
$conn = new mysqli($host, $user, $password, $database);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
echo "数据库连接成功";
?>

2. 执行查询

<?php
// 执行查询
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. " - 用户名: " . $row["username"]. "<br>";
    }
} else {
    echo "没有查询到结果";
}
?>

3. 预处理语句（防止SQL注入）

<?php
// 插入数据示例
$stmt = $conn->prepare("INSERT INTO users (username, password, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $username, $hashedPassword, $email);

$username = "testuser";
$hashedPassword = password_hash("123456", PASSWORD_DEFAULT);
$email = "test@example.com";

$stmt->execute();
echo "新记录插入成功";
$stmt->close();
?>

三、Cookie与Session

1. Cookie使用

<?php
// 设置Cookie（有效期1小时）
setcookie("username", "john", time()+3600, "/");

// 读取Cookie
if(isset($_COOKIE["username"])) {
    echo "欢迎回来，" . $_COOKIE["username"];
}

// 删除Cookie
setcookie("username", "", time()-3600, "/");
?>

2. Session管理

<?php
// 启动Session
session_start();

// 设置Session数据
$_SESSION["user_id"] = 1;
$_SESSION["username"] = "admin";

// 读取Session
if(isset($_SESSION["username"])) {
    echo "当前登录用户：" . $_SESSION["username"];
}

// 销毁Session
session_unset();
session_destroy();
?>

四、登录注册功能实现

1. 数据库准备

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

2. 注册功能实现

(1) 注册表单（register.html）

<form action="register.php" method="post">
    用户名：<input type="text" name="username" required><br>
    密码：<input type="password" name="password" required><br>
    邮箱：<input type="email" name="email" required><br>
    <input type="submit" value="注册">
</form>

(2) 注册处理（register.php）

<?php
if($_SERVER["REQUEST_METHOD"] == "POST") {
    $conn = new mysqli("localhost", "root", "", "mydb");

    $username = $conn->real_escape_string($_POST['username']);
    $password = password_hash($_POST['password'], PASSWORD_DEFAULT);
    $email = $conn->real_escape_string($_POST['email']);

    $stmt = $conn->prepare("INSERT INTO users (username, password, email) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $username, $password, $email);

    if($stmt->execute()) {
        echo "注册成功！";
    } else {
        echo "错误：" . $conn->error;
    }

    $stmt->close();
    $conn->close();
}
?>

3. 登录功能实现

(1) 登录表单（login.html）

<form action="login.php" method="post">
    用户名：<input type="text" name="username" required><br>
    密码：<input type="password" name="password" required><br>
    <input type="submit" value="登录">
</form>

(2) 登录处理（login.php）

<?php
session_start();

if($_SERVER["REQUEST_METHOD"] == "POST") {
    $conn = new mysqli("localhost", "root", "", "mydb");

    $username = $conn->real_escape_string($_POST['username']);
    $password = $_POST['password'];

    $stmt = $conn->prepare("SELECT id, password FROM users WHERE username = ?");
    $stmt->bind_param("s", $username);
    $stmt->execute();
    $result = $stmt->get_result();

    if($result->num_rows == 1) {
        $user = $result->fetch_assoc();
        if(password_verify($password, $user['password'])) {
            $_SESSION["user_id"] = $user['id'];
            $_SESSION["username"] = $username;
            header("Location: dashboard.php");
        } else {
            echo "密码错误";
        }
    } else {
        echo "用户不存在";
    }

    $stmt->close();
    $conn->close();
}
?>