Mysql Tutorial – Upload files to MYSQL database
Posted on | March 17, 2009 | 12 Comments
Hello friends ,
All we know that how to upload file(e.g. image , pdf) in folder using PHP. But in some web application if you need to upload files into MySQL database than here is your solution.
First let’s create the table for the upload files.The table will consist of 5 fields.
id : Unique id for each file (primary key)
name : File name
type : File content type
size : File size
content : The file itself
For content field we will use BLOB data type. BLOB is a binary large object that can hold a variable amount of data. MySQL have four BLOB data types as listed below :
1 . TINYBLOB
2 . BLOB
3 . MEDIUMBLOB
4 . LONGBLOB
Since BLOB has limited capacity to store data up to 64 kilobytes so we will use MEDIUMBLOB which can store larger files up to 16 megabytes.
CREATE TABLE tbl_upload (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
type VARCHAR(30) NOT NULL,
size INT NOT NULL,
content MEDIUMBLOB NOT NULL,
PRIMARY KEY(id)
);
To upload a file to MYSQL , First you need to upload file to the server and then read the uploaded file and insert it to MYSQL.
For uploading file to server , you need a simple form which give option to user to browse computer and select file. Look at below code.
<form method=”post” enctype=”multipart/form-data”>
<table width=”50%” align=”center” border=”0″ cellpadding=”1″ cellspacing=”1″>
<tr>
<td width=”59%” align=”left”>
<input type=”hidden” name=”MAX_SIZE” value=”2000000″>
<input name=”file_name” type=”file” id=”file_name”>
</td>
<td width=”41%” align=”left”><input name=”upload_file” type=”submit” id=”upload_file” value=”Upload”></td>
</tr>
</table></form>
Form must have encytype=”multipart/form-data” and Hidden input MAX_SIZE is used to restrict the size of files.
Below are some value we can get after form is submitted using $_FILES varaible.
$_FILES['file_name']['name']
The original name of the file on the user’s computer.
$_FILES['file_name']['type']
The mime type of the file, if the browser provided this information. An example would be “image/jpeg”.
$_FILES['file_name']['size']
The size, in bytes, of the uploaded file.
$_FILES['file_name']['tmp_name']
The temporary filename of the file in which the uploaded file was stored on the server.
<?php
if(isset($_POST['upload_file']) && $_FILES['file_name']['size'] > 0)
{
$originalName = $_FILES['file_name']['name'];
$tmpName = $_FILES['file_name']['tmp_name'];
$fileSize = $_FILES['file_name']['size'];
$fileType = $_FILES['file_name']['type'];$fp = fopen($tmpName, ‘r’);
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);if(!get_magic_quotes_gpc())
{
$originalName = addslashes($originalName);
}$dbhostname = ‘localhost’;
$dbusername = ‘root’;
$dbpassword = ”;
$connection = mysql_connect($dbhostname, $dbusername, $dbpassword)mysql_select_db(“testing”);
$query = “INSERT INTO tbl_upload (name, size, type, content ) VALUES (‘$originalName’, ‘$fileSize’, ‘$fileType’, ‘$content’)”;
mysql_query($query) or die(‘Error, query failed’);
mysql_close($connection);
echo “File $originalName uploaded”;
}
?>
Uploaded file was saved as temporary name ( $_FILES['file_name']['tmp_name'] ) by PHP . So Our job is to read the content of this saved file and insert the content to MYSQL database. Always use addslashes() to escape the content.
That’s it now you can upload your files to MySQL. Now your job is to download the files back from MYSQL database. to view how to download that files back click here.
Cheers !!
Comments
12 Responses to “Mysql Tutorial – Upload files to MYSQL database”
Leave a Reply
About Author
I am Rakshit Patel working as PHP Freelancer. I am here to share all my knowledge related to internet and also real time internet updates.
Follow on twitter: @raxit4u2
Get Free Softwares:
March 17th, 2009 @ 10:52 am
[...] PHP, Programming Easy AdSenser by UnrealOnce we upload file in Mysql database , we need to download file from Mysql database . When we upload a file to MYSQL database we also [...]
March 19th, 2009 @ 12:45 am
How about getting them back out again?
March 18th, 2009 @ 8:45 pm
How about getting them back out again?
March 19th, 2009 @ 5:09 am
Hello Someone ,
Here is the way to get them back from MYSQL database.
http://www.onlinehungama.com/2009/03/17/download-files-from-mysql-database/
March 19th, 2009 @ 1:09 am
Hello Someone ,
Here is the way to get them back from MYSQL database.
http://www.onlinehungama.com/2009/03/17/download-files-from-mysql-database/
March 19th, 2009 @ 1:28 pm
This technical exercise is interesting, however… I wonder about it’s use.
Having the binary data in the database is useful, but not necessarily for binary files. This adds a bit of latency to files, as well as unnecessary calls to the database.
I would agree that storing the name, size, and date, even the file type, are useful in the database. However, the binary itself should be left to the file system to handle, and not the database.
IMHO, you’re adding another layer that is not required. Adding latency to fetch the object itself.
Again, this is an interesting technical exercise.
Regards,
March 19th, 2009 @ 9:28 am
This technical exercise is interesting, however… I wonder about it’s use.
Having the binary data in the database is useful, but not necessarily for binary files. This adds a bit of latency to files, as well as unnecessary calls to the database.
I would agree that storing the name, size, and date, even the file type, are useful in the database. However, the binary itself should be left to the file system to handle, and not the database.
IMHO, you’re adding another layer that is not required. Adding latency to fetch the object itself.
Again, this is an interesting technical exercise.
Regards,
March 19th, 2009 @ 1:40 pm
Thanks Lloyd for your compliment. I am working as a PHP developer since last 2 and half years. So if i came against something new stuff related to PHP and MYSQL than i would like to share it with my readers. Thanks a lot.
March 19th, 2009 @ 9:40 am
Thanks Lloyd for your compliment. I am working as a PHP developer since last 2 and half years. So if i came against something new stuff related to PHP and MYSQL than i would like to share it with my readers. Thanks a lot.
April 15th, 2009 @ 3:09 pm
Hey, nice tips. Perhaps I’ll buy a bottle of beer to the man from that forum who told me to go to your blog
April 15th, 2009 @ 11:09 am
Hey, nice tips. Perhaps I’ll buy a bottle of beer to the man from that forum who told me to go to your blog
July 12th, 2011 @ 2:12 pm
how to upload files into orac le database using jsp and servlet
please reply to me
and how getting them back