How to design a reliable MySQL table structure to implement file download function?

How to design a reliable MySQL table structure to implement the file download function?

In many applications, the file download function is a very common and important function. In order to implement the file download function, we need to design the database table so that it can store and manage file-related information. This article will introduce how to design a reliable MySQL table structure to implement the file download function, and provide code examples for easy understanding.

1. Create a file table

First, we need to create a file table to store file-related information. The file table needs to contain the following fields:

• id: file ID, as the primary key
• name: file name
• path: file path, where the file is stored on the server Path information
• size: file size
• create_time: file creation time
• update_time: file update time

The following is the SQL to create the file table Code example:

CREATE TABLE `file` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `path` varchar(512) NOT NULL,
  `size` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Upload files

In the file upload function, we need to save the uploaded file to the server and store the relevant information of the file in the file table. The following is a simple PHP code example:

<?php
// 获取上传文件信息
$fileName = $_FILES['file']['name'];
$fileSize = $_FILES['file']['size'];
$fileTmpName = $_FILES['file']['tmp_name'];

// 生成文件路径
$uploadPath = '/var/www/html/uploads/';
$fileUrl = $uploadPath . $fileName;

// 保存文件到服务器
move_uploaded_file($fileTmpName, $fileUrl);

// 将文件信息插入到文件表中
$createTime = date('Y-m-d H:i:s');
$updateTime = $createTime;

$sql = "INSERT INTO `file` (`name`, `path`, `size`, `create_time`, `update_time`)
        VALUES ('$fileName', '$fileUrl', '$fileSize', '$createTime', '$updateTime')";

// 执行SQL语句
// ...
?>

3. Download files

To implement the file download function, you need to provide a download link to the user, and transfer the file that the user clicks to download to the user's device . The following is a simple PHP code example:

<?php
$fileId = $_GET['id'];

// 查询文件信息
$sql = "SELECT `path`, `name` FROM `file` WHERE `id` = $fileId";
// 执行SQL语句并获取结果
// ...

// 获取文件路径和文件名
$fileUrl = $result['path'];
$fileName = $result['name'];

// 设置HTTP Header为下载文件
header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename="' . $fileName . '"');

// 读取文件内容并输出到浏览器
readfile($fileUrl);
?>

Through the above steps, we can design a reliable MySQL table structure to store and manage file-related information and implement the file download function. Of course, the above code example is just a simple implementation, and the actual situation may need to be appropriately adjusted and optimized according to specific needs.

The above is the detailed content of How to design a reliable MySQL table structure to implement file download function?. For more information, please follow other related articles on the PHP Chinese website!