mysql数据文件存储_在MySQL中存储用户提交的数据和文件

朴灿烈づ我的快乐病毒、 2022-12-07 12:09 184阅读 0赞

mysql数据文件存储

## 建立表格 **(** Creating a Form **)** ##

Sometimes it is useful to collect data from your website users and store this information in a [MySQL ][MySQL]database. We have already seen you can populate a database using [PHP][], now we will add the practicality of allowing the data to be added through a user-friendly web form.

有时它是有用的，从你的网站用户收集数据并存储在该信息[MySQL][]数据库。 我们已经看到您可以使用[PHP][]填充数据库，现在我们将增加实用性，允许通过用户友好的Web表单添加数据。

The first thing we will do is create a page with a form. For our demonstration we will make a very simple one:

我们要做的第一件事是创建一个带有表单的页面。 对于我们的演示，我们将做一个非常简单的示例：

`Your Name:E-mail:Location:`

## 插入-从表单添加数据 **(** Insert Into - Adding Data from a Form **)** ##

Next, you need to make process.php, the page that our form sends its data to. Here is an example of how to collect this data to post to the MySQL database:

接下来，您需要制作process.php，这是我们表单将数据发送到的页面。 这是一个如何收集这些数据以发布到MySQL数据库的示例：

As you can see the first thing we do is assign variables to the data from the previous page. We then just query the database to add this new information.

如您所见，我们要做的第一件事是为上一页中的数据分配变量。 然后，我们只查询数据库以添加此新信息。

Of course, before we try it we need to make sure the table actually exists. Executing this code should create a table that can be used with our sample files:

当然，在尝试之前，我们需要确保该表确实存在。 执行此代码应创建一个可与我们的示例文件一起使用的表：

CREATE TABLE data (name VARCHAR(30), email VARCHAR(30), location VARCHAR(30));

## 添加文件上传 **(** Add File Uploads **)** ##

Now you know how to store user data in MySQL, so let's take it one step further and learn how to upload a file for storage. First, let's make our sample database:

现在您知道了如何在MySQL中存储用户数据，因此让我们更进一步，学习如何上传文件进行存储。 首先，让我们创建示例数据库：

CREATE TABLE uploads (id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY, description CHAR(50), data LONGBLOB, filename CHAR(50), filesize CHAR(50), filetype CHAR(50) );

The first thing you should notice is a field called *id* that is set to **AUTO\_INCREMENT**. What this [data type][] means is that it will count up to assign each file a unique file ID starting at 1 and going to 9999 (since we specified 4 digits). You will also probably notice that our data field is called **LONGBLOB.** There are many types of BLOB as we have mentioned before. TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB are your options, but we set ours to LONGBLOB to allow for the largest possible files.

您应该注意的第一件事是一个名为*id*的字段，该字段设置为**AUTO\_INCREMENT** 。 此[数据类型的][data type]意思是它将累加每个文件一个唯一的文件ID(从1开始到9999)(因为我们指定了4位数字)。 您可能还会注意到，我们的数据字段称为**LONGBLOB。** 如前所述，BLOB有很多类型。 TINYBLOB，BLOB，MEDIUMBLOB和LONGBLOB是您的选择，但我们将LONGBLOB设置为LONGBLOB以允许最大的文件。

Next, we will create a form to allow the user to upload her file. This is just a simple form, obviously, you could dress it up if you wanted:

接下来，我们将创建一个表单，以允许用户上传她的文件。 这只是一种简单的形式，显然，您可以根据需要进行修饰：

`Description:File to upload:`

**Be sure to take notice of the enctype, it is very important!**

**请务必注意enctype，这非常重要！**

## 将文件上传添加到MySQL **(** Adding File Uploads to MySQL **)** ##

Next, we need to actually create upload.php, which will take our users file and store it in our database. Below is sample coding for upload.php.

接下来，我们需要实际创建upload.php，它将获取我们的用户文件并将其存储在我们的数据库中。 以下是upload.php的示例代码。

File ID: $id "; print "

`File Name: $form_data_name"; print "`

`File Name: $form_data_name "; print "`

`File Size: $form_data_size"; print "`

`File Size: $form_data_size "; print "`

`File Type: $form_data_type`

`"; print "To upload another file Click Here"; ?>`

Learn more about what this actually does on the next page.

在下一页上了解更多有关此功能的信息。

## 添加上传说明 **(** Adding Uploads Explained **)** ##

The first thing this code actually does is connect to the database (you need to replace this with your actual database information.)

该代码实际要做的第一件事是连接到数据库(您需要用实际的数据库信息替换它。)

Next, it uses the **ADDSLASHES** function. What this does is add backslashes if needed into the file name so that we won't get an error when we query the database. For example, if we have Billy'sFile.gif, it will convert this to Billy'sFile.gif. **[FOPEN][]** opens the file and **FREAD** is a binary safe file read so that the **ADDSLASHES** is applied to data within the file if needed.

接下来，它使用**ADDSLASHES**函数。 这样做是在文件名中添加反斜杠(如果需要)，以便在查询数据库时不会出现错误。 例如，如果我们有Billy'sFile.gif，它将把它转换为Billy'sFile.gif。 **[FOPEN][]**打开文件，而**FREAD**是读取的二进制安全文件，以便在需要时将**ADDSLASHES**应用于文件中的数据。

Next, we add all of the information our form collected into our database. You will notice we listed the fields first, and the values second so we don't accidentally try to insert data into our first field (the auto assigning ID field.)

接下来，我们将表单收集的所有信息添加到数据库中。 您会注意到我们首先列出了字段，然后列出了值，所以我们不会意外地将数据插入我们的第一个字段(自动分配ID字段)。

Finally, we print out the data for the user to review.

最后，我们打印出数据供用户查看。

## 检索文件 **(** Retrieving Files **)** ##

We already learned how to retrieve[ plain data][plain data] from our MySQL database. Likewise, storing your files in a MySQL database wouldn't be very practical if there wasn't a way to retrieve them. The way we are going to learn to do this is by assigning each file a URL based on their ID number. If you will recall when we uploaded the files we automatically assigned each of the files an ID number. We will use that here when we call the files back. Save this code as download.php

我们已经学习了如何从MySQL数据库检索[纯数据][plain data] 。 同样，如果没有一种方法可以将文件存储在MySQL数据库中也不是很实用。 我们将学习如何做到这一点的方法是根据每个文件的ID号为其分配一个URL。 如果您还记得在上载文件时会自动为每个文件分配一个ID号。 当我们回叫文件时，将在这里使用它。 将此代码另存为download.php

Now to retrieve our file, we point our browser to: http://www.yoursite.com/download.php?id=2 (replace the 2 with whatever file ID you want to download/display)

现在要检索文件，我们将浏览器指向：http://www.yoursite.com/download.php?id=2(将2替换为要下载/显示的任何文件ID)

This code is the base for doing a lot of things. With this as a base, you can add in a database query that would list files, and put them in a drop down menu for people to choose. Or you could set ID to be a randomly created number so that a different graphic from your database is randomly displayed each time a person visits. The possibilities are endless.

这段代码是做很多事情的基础。 以此为基础，您可以添加一个数据库查询，该查询将列出文件，并将其放在下拉菜单中供人们选择。 或者，您可以将ID设置为随机创建的数字，以便每次有人访问时都随机显示与数据库不同的图形。 可能性是无止境。

## 删除文件 **(** Removing Files **)** ##

Here is a **very simple** way of removing files from the database. You want to **be careful** with this one!! Save this code as remove.php

这是从数据库中删除文件的**非常简单的**方法。 您要**小心**这一点！！ 将此代码另存为remove.php

Like our previous code that downloaded files, this script allows files to be removed just by typing in their URL: http://yoursite.com/remove.php?id=2 (replace 2 with the ID you want to remove.) For obvious reasons, you want to **be careful with this code**. This is of course for demonstration, when we actually build applications we will want to put in safeguards that ask the user if they are sure they want to delete, or perhaps only allow people with a password to remove files. This simple code is the base we will build on to do all of those things.

就像我们之前下载文件的代码一样，此脚本允许通过输入URL来删除文件：http://yoursite.com/remove.php?id=2(用您要删除的ID替换2)。显而易见的原因，您需要**谨慎使用此代码** 。 当然，这是出于演示目的，当我们实际构建应用程序时，我们将希望采用防护措施，询问用户是否确定要删除，或者仅允许具有密码的人删除文件。 这个简单的代码是我们做所有这些事情的基础。

> 翻译自: [https://www.thoughtco.com/storing-data-and-files-in-mysql-2694013][https_www.thoughtco.com_storing-data-and-files-in-mysql-2694013]

mysql数据文件存储

[MySQL]: https://www.thoughtco.com/mysql-commands-4133482
[PHP]: https://www.thoughtco.com/php-4133485
[data type]: https://www.thoughtco.com/understanding-how-sql-databases-work-2693878
[FOPEN]: https://www.thoughtco.com/write-to-a-file-from-php-2693790
[plain data]: https://www.thoughtco.com/limit-mysql-command-2693970
[https_www.thoughtco.com_storing-data-and-files-in-mysql-2694013]: https://www.thoughtco.com/storing-data-and-files-in-mysql-2694013