How to Insert Files into a MySQL Database Using PHP

PHP



In this tutorial, we will show how to insert files into a MySQL database using PHP.

In fact, we really are not inserting files into the database.

This article actually doesn't take the route of inserting the actual files into the MySQL database.

Instead, we simply upload files to the server. And we place the filename into the database. We then specify the complete path to the file so that we can access it.

In my view, this is much simpler. However, if you are looking to actually insert the file into the database using blob, then we haven't created a tutorial on this. And you'll probably exit this page.

However, if we want to display a table with stored files based on a MySQL database, this tutorial is really good to show you how to do so.

So, again, with this method, all we have to do is place the filename in the MySQL database. We then can, using PHP code, show the file by specifying the full path to that file.

This is a much, much easier, and simple way of going about it then to actually upload files into the database, which is more complex.

We used to allow users to upload files on to the site but have stopped due to concerns of malware.

In order to insert files into a MySQL database, as explained before, we don't actually put the files in the database. We put the files in a regular folder on our website. I created a files folder (or directory) on my website which the files will upload to. In the MySQL database, all you do is place the file name. We also place a description of the file in the database. However, to display the file, all we need is the file name. Once we have this, all we have to do in the PHP code is specify the full path to this file, so that it can be displayed.

So, again, we upload the file to a directory on our website. We don't put the file directly in the MySQL database. We upload it to a directory on our site. We simply get the file name and place it in the MySQL database. We then have the complete pathway to the file in our code so that we can show and display it.

So if we're saving a file named, mortgage.pdf, we save the full file name, mortgage.pdf.

We will go through this below.

In this demonstration, I'm going to use phpMyAdmin. When creating a table in MySQL, we create 3 columns.

One column is the ID column, which should be present in really any MySQL table. This column is unique and should be set to autoincrement. It counts sequentially 1 up from any new row of data inserted. So it starts at 1. If you insert 5 rows of data, the fifth row will have an ID of 5. The purpose of it is that it's unique and it gives you a way to order the new uploads in order of when they were uploaded. You could also do this in other ways. But this is probably the easiest way of showing the files in order from when they were uploaded.

The second column that I created was a description column. This holds a description of the file being entered. Once the user puts a description of the file in the text box and uploads the file along with the description, the description of the file entered will be inserted into the descripton column of the table.

The third and final column that I created is the filename column. This holds the full file name of the file. So if you're uploading a file named records.txt, the full file name entered into the filename column is records.txt

Below you can see the structure of the table created that serves as the files uploads table.

Since it holds files information, I named the table, 'Files'. You can see the image of the structure of the table below.

Files uploads table structure

So you can see the structure of the table above.

The ID column is of type int (11).

The description column is set to VARCHAR(100) to allow it to hold a good deal of characters. Sometimes the user may want to put in a somewhat good description of the file, so we allow up to 100 characters.

The filename column is set to VARCHAR(50) to allow for a somewhat long filename.

The reason we use VARCHAR for all these fields is because it allows a variable amount of characters. CHAR is a fixed amount. You would use CHAR when every upload is the same amount of characters. This would be the case for a few things such as state abbreviations, since they are all 2 characters. In this case, CHAR wouldn't work for any of the fields.

Of course, if you need to, modify the amount parameters to satisfy your needs.

HTML Code

So now let's go over the HTML code needed to create the uploader seen above, which allows for files to be uploaded.

The HTML code is shown below.



The HTML code above creates an upload form. Since we want this page to keep all information obtained from the form, we set action equal to "". If we wanted to send the information entered into this uploader to another page, we would specify that PHP page. But since we want the information for later PHP code on this page, we set action equal to nothing. The method is POST. And for file uploading, the statement, enctype="multipart/form-data" must be input. If not, file uploading will not work.

The line underneath creates a text box, which holds the description of the file. If no description is entered and the user clicks the Upload button, the statement, "A description must be entered" is output.

We then create a line allowing for the file upload. If no file is entered, the statement, "Please choose a file" is output.

We then create a submit button, as pretty much every form needs. We give it the value, "Upload", so that Upload appears on the button. If not, it would just have submit by default.

We then close the form.

This concludes the HTML needed for this page.

PHP Code


First Block of PHP Code

There are 3 blocks of PHP codes needed.

The first block is shown below.

This block is for uploading the file entered to the directory, where we will store the file, in this case this is the Uploads/files/ directory.





So this code takes the file that the user entered.

We also take the description that the user entered of the file and place it in the $description variable.

We specify the directory that we want the file uploaded to. In this case, it is the Upload/files/ directory. If the user has clicked the Upload button without specifying a file, the statement, "Please choose a file" is output. If the user enters a file, the file is uploaded to the directory we specified through the move_uploaded_file() function. This function creates a temporary name for the file upload and then permanently transfers the file to the directory we specify. The statement, "Uploaded" is then output.

This concludes this block of PHP code.


Second Block of PHP Code

The next block of PHP code inserts the data into the MySQL table for storage. These include the description of the file and its filename.

This block of code is shown below.





This block of code establishes connection to the MySQL database being used. This requires a user, password, host, database, and table name.

As stated above, a MySQL table was created with phpMyAdmin. This table has 3 columns: ID, description, filename.

If the description text box is not empty, then we insert into the Files MySQL table the description and filename of the file.

We then close the database.


Third Block of PHP Code

The next block of PHP code displays the table with the file description and the link to the actual file.





So again, we establish connection to the MySQL database which contains the Files table.

We query the table containing the files information.

We then display the table with the html <table> tag.

The statement, while ($row = mysql_fetch_array($result)) gives a loop so that every row in the accounted for. As long as there are rows, the statement, mysql_fetch_array($result) will be true, so the loop won't be exited until all rows are output.

Using the $row['value'] feature, we are able to get all of the information from each of the columns for each row. So we obtain the description and filename for each row, which represents a file.

And this concludes all the code which is necessary for building a file uploader which works in combination with MySQL to store tables of files.

And this is how files can be used with MySQL to hold a table containing files.

Note that the file you uploaded is public, which is how you were able to access the page. If you didn't think it would work and you uploaded an embarrassing file or one you just simply don't want on the site, just email me and I'll take it down. I'll be checking regularly anyway.


Related Resources

How to Insert Images into a MySQL Database Using PHP

How to Insert Videos into a MySQL Database Using PHP

How to Select a Table from a MySQL Database

How to Create a Confirmation Page for an HTML Web Form Using PHP

How to Redirect to Another URL with PHP

How to Create a Search Engine Using PHP

How to Upload Images to a Website Using PHP

How to Upload Files to a Website Using PHP

How to Create a Searchable Database Using MySQL and PHP

How to Create Your Own File Transfer Protocol (FTP) For Your Website Using PHP

How to Create a Register and Login Page Using PHP



HTML Comment Box is loading comments...