Storing User Submitted Data and Files in MySQL

Creating a Form

Sometimes it is useful to collect data from your website users and store this information in a​ 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.

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:

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:

 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.

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!

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.

 File ID: $id
";
print "

File Name: $form_data_name
"; 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.

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.)

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

Retrieving Files

We already learned how to retrieve 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

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)

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.

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

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.