基于PHP+MySQL的校离线服务器：如何实现视频存入账号专属下载文件夹

Hey there! Let's break down how to build this "virtual download" feature—since you don't want videos saved to students' local devices, we're essentially creating a personalized access list that shows up as "MY DOWNLOADS" in their account. All video files stay on your school's offline server; the "download" action just adds the video to the student's personal list. Here's how to pull this off with PHP and MySQL:

1. Update Your Database Schema #

First, you'll need a junction table to link students to the videos they've "downloaded". Let's call it user_video_downloads:

CREATE TABLE user_video_downloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    video_id INT NOT NULL,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_video (user_id, video_id) -- Prevents duplicate "downloads"
);

The UNIQUE constraint ensures a student can't "download" the same video multiple times accidentally.

2. Handle the "Download" Button Action #

Instead of triggering a file download when the student clicks "Download", send an AJAX request to your PHP backend to add the video to their list.

Frontend (Vanilla JavaScript) #

Add this to your video page (adaptable for jQuery too):

// Attach to your "Download" button click event
document.querySelector('.download-video-btn').addEventListener('click', function(e) {
    e.preventDefault();
    const videoId = this.dataset.videoId; // Store video ID in a data attribute
    const userId = <?php echo $_SESSION['user_id']; ?>; // Get logged-in user ID from session

    fetch('add_download.php', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/x-www-form-urlencoded',
        },
        body: `user_id=${userId}&video_id=${videoId}`
    })
    .then(response => response.json())
    .then(data => {
        if (data.success) {
            alert('Video added to your MY DOWNLOADS!');
            // Optional: Update button state to avoid re-clicks
            this.textContent = 'Added to Downloads';
            this.disabled = true;
        } else {
            alert('Error: ' + data.message);
        }
    });
});

Backend (add_download.php) #

This script handles the request and inserts the record into the database:

<?php
session_start();
// Validate user is logged in
if (!isset($_SESSION['user_id'])) {
    echo json_encode(['success' => false, 'message' => 'Please log in first']);
    exit;
}

// Connect to your MySQL database (use your existing connection logic)
$conn = mysqli_connect('localhost', 'your_db_user', 'your_db_pass', 'your_db_name');
if (!$conn) {
    echo json_encode(['success' => false, 'message' => 'Database connection failed']);
    exit;
}

$userId = $_POST['user_id'];
$videoId = $_POST['video_id'];

// Check if the video is already in the user's downloads
$checkQuery = "SELECT id FROM user_video_downloads WHERE user_id = ? AND video_id = ?";
$stmt = mysqli_prepare($conn, $checkQuery);
mysqli_stmt_bind_param($stmt, 'ii', $userId, $videoId);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);

if (mysqli_stmt_num_rows($stmt) > 0) {
    echo json_encode(['success' => false, 'message' => 'Video already in your downloads']);
    mysqli_stmt_close($stmt);
    mysqli_close($conn);
    exit;
}

// Insert the new download record
$insertQuery = "INSERT INTO user_video_downloads (user_id, video_id) VALUES (?, ?)";
$stmt = mysqli_prepare($conn, $insertQuery);
mysqli_stmt_bind_param($stmt, 'ii', $userId, $videoId);

if (mysqli_stmt_execute($stmt)) {
    echo json_encode(['success' => true, 'message' => 'Video added successfully']);
} else {
    echo json_encode(['success' => false, 'message' => 'Failed to add video: ' . mysqli_error($conn)]);
}

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

3. Display the "MY DOWNLOADS" Folder #

Create a page (e.g., my_downloads.php) that fetches and displays all videos the student has "downloaded":

<?php
session_start();
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit;
}

$conn = mysqli_connect('localhost', 'your_db_user', 'your_db_pass', 'your_db_name');
$userId = $_SESSION['user_id'];

// Fetch user's downloaded videos (join with videos table for details)
$query = "SELECT v.id, v.title, v.video_path, v.thumbnail_path 
          FROM user_video_downloads ud
          JOIN videos v ON ud.video_id = v.id
          WHERE ud.user_id = ?
          ORDER BY ud.downloaded_at DESC";
$stmt = mysqli_prepare($conn, $query);
mysqli_stmt_bind_param($stmt, 'i', $userId);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$downloadedVideos = mysqli_fetch_all($result, MYSQLI_ASSOC);
?>

<!DOCTYPE html>
<html>
<head>
    <title>MY DOWNLOADS</title>
</head>
<body>
    <h1>MY DOWNLOADS</h1>
    <div class="video-grid">
        <?php if (empty($downloadedVideos)): ?>
            <p>No videos downloaded yet.</p>
        <?php else: ?>
            <?php foreach ($downloadedVideos as $video): ?>
                <div class="video-card">
                    <img src="<?php echo $video['thumbnail_path']; ?>" alt="<?php echo $video['title']; ?>">
                    <h3><?php echo $video['title']; ?></h3>
                    <!-- Link to play the video via secure proxy -->
                    <a href="play_video.php?id=<?php echo $video['id']; ?>" class="play-btn">Play Video</a>
                </div>
            <?php endforeach; ?>
        <?php endif; ?>
    </div>
</body>
</html>

4. Secure Video Access (Critical!) #

Don't let students access video files directly via URLs. Create a play_video.php script that validates their access before serving the video:

<?php
session_start();
if (!isset($_SESSION['user_id'])) {
    header('HTTP/1.1 403 Forbidden');
    echo 'Access denied';
    exit;
}

$conn = mysqli_connect('localhost', 'your_db_user', 'your_db_pass', 'your_db_name');
$videoId = $_GET['id'];
$userId = $_SESSION['user_id'];

// Verify the video is in the user's downloads
$query = "SELECT v.video_path FROM user_video_downloads ud
          JOIN videos v ON ud.video_id = v.id
          WHERE ud.user_id = ? AND v.id = ?";
$stmt = mysqli_prepare($conn, $query);
mysqli_stmt_bind_param($stmt, 'ii', $userId, $videoId);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$video = mysqli_fetch_assoc($result);

if (!$video) {
    header('HTTP/1.1 404 Not Found');
    echo 'Video not found or you don\'t have access';
    exit;
}

// Serve the video file (use your server's local path)
$filePath = $video['video_path']; // e.g., '/var/www/offline_videos/algebra1.mp4'
if (file_exists($filePath)) {
    $mime = mime_content_type($filePath);
    header('Content-Type: ' . $mime);
    header('Content-Length: ' . filesize($filePath));
    readfile($filePath);
} else {
    header('HTTP/1.1 404 Not Found');
    echo 'Video file missing';
}
?>

Key Notes #

• No Local Storage: Videos never touch the student's device—they only access them through your system's interface.
• Offline Compatibility: Everything runs on your school's offline server, so no internet is needed.
• Duplicate Prevention: The database's UNIQUE constraint and backend check stop accidental duplicate "downloads".

内容的提问来源于stack exchange，提问作者Blessing Dongo