Insert Excel File into MySQL using PHP
Hi! Let’s see how to insert excel file into mysql database using php. In PHP importing CSV data to MySQL is a breeze but not the same case when it comes to Excel file. Excel format is somewhat tricky to parse with php but you don’t have to worry. I’m going to show you a much simpler method to achieve it. For that you’ll need an excel parser library PHPExcel. This is an excellent lib and supports both ‘XLS’ and ‘XLSX’ format.
For those who don’t like using third-party library here’s a way to do it with core php alone. Just save your excel file as csv format, then read it with native fgetcsv() function and insert into db. Simple! But I’m not going into greater detail about the process here. As for this tutorial I’m going to focus on dealing with excel file. Come, let me show you how to read excel file and store data into mysql db with php.
How to Insert Excel File into MySQL using PHP?
The process is supposed to be like this. You have a mysql table and an excel file with some data to be stored in db. Here I’m going to assume the excel column headers matches the table fields. Now establish mysql connection, read through excel rows one by one and insert into database. Here goes the step by step process for inserting excel to mysql.
Step 1) First download PHPExcel library, unzip and move it to your root folder.
Step 2) Create necessary database and table in mysql. This is the one I’m going to use for the demo.
CREATE DATABASE IF NOT EXISTS `db_employee`; USE `db_employee`; CREATE TABLE IF NOT EXISTS `Employee` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(30) NOT NULL, `Designation` varchar(30) NOT NULL, `Salary` varchar(10) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Step 3) Create an excel file ’empdetails.xlsx’ and add some data to it.
| Id | Name | Designation | Salary |
| 1 | Suki Burks | Developer | 114500 |
| 2 | Fred Zupers | Technical Author | 145000 |
| 3 | Gavin Cortez | Team Leader | 235500 |
Step 4) Create index.php file and add the below code to it. This is the core of the entire process. The php script loads PHPExcel library, establish connection to mysql database, read the entire excel file, store the rows in associative arrays, loop through it and insert into the database.
setActiveSheetIndex(0); $data = $obj->getActiveSheet()->toArray(null, true, true, true); $columns = implode(",", array_values($data[1])); for ($i=2; $i echo "Successfully imported excel file to mysql!"; > catch(Exception $err) < die('Error ' . $err->getMessage()); > mysqli_close($con); ?> The PHPExcel parser returns excel data as array of arrays. That is, one array per row. The first row is the column headers and the subsequent ones are the actual data themselves.
Now run the file and if everything goes right, the data from excel file will be imported to mysql db and will show you some success message.
Now this is what I got — the mysql table after inserting excel data.
I hope now you have got idea about inserting excel file into mysql using php. Feel free to tweak the code to fit your logic. And don’t forget to share the script in social media.
How to Insert Files into a MySQL Database Using 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.
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.
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.
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.