mysql
Sometimes in our web application we need the image upload feature. User can browse an image from his/her PC and clicks the upload/submit button to upload this image to the server. Basically this happens when we create web applications like photo gallery or user avatar selection etc.
There are two ways uploaded images can be stored in server- In server’s physical directory or in the database. Storing in physical directory is like copying the image from user’s machine to server directory. The advantage is that it only takes spaces from the server’s physical directory. But disadvantage is the extra headache of taking backup for website administrator. In database we can store the images easily and taking backup of database is sufficient. But here the disadvantage is that large volume of data might slow down the database operation.
In this tutorial we’ll discuss about image upload in mysql database and how to display it. In MySQL images or any files can be stored in a specific field called BLOB (Basic Large Object or Binary Large Object). BLOB fields are designed to store any binary files. There are 4 types of BLOB fields are available in MySQL: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. The descriptions are self explanatory.
In our sample PHP-MySQL code we’ll use only BLOB field. So open your phpmyadmin and execute this SQL to create the table.
|
CREATE TABLE `img_tbl` (
`id` int(4) NOT NULL auto_increment,
`img_name` varchar(255) collate latin1_general_ci NOT NULL,
`img_type` varchar(4) collate latin1_general_ci NOT NULL,
`img_size` int(8) NOT NULL,
`img_data` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
|
`id` is the unique id for the table row. This is the primary key. `img_name` field stores the image name. `img_type` stores the information about the type of images like JPG or GIF etc. `img_size` holds the size of the image and img_data stores the actual image file.
Now we need a HTML file which will allow the user to browse and select the image to upload. Elow is the code snippet for this:
|
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN”>
<HTML>
<HEAD>
<TITLE> Image Upload to BLOB field </TITLE>
</HEAD>
<BODY>
<FORM NAME=”f1″ METHOD=”POST” ACTION=”upload.php” ENCTYPE=”multipart/form-data”>
<table>
<tr><td> Image Upload Page </td></tr>
<tr><td> <input type=”file” name=”imgfile”/></td></tr>
<tr><td> <input type=”submit” name=”submit” value=”Save”/> </td></tr>
</table>
</FORM>
</BODY>
</HTML>
|
Remember always put the ENCTYPE attribute to be “multipart/form-data” to make it post files to the server. If you forget to add this attribute in the HTML <form /> tag, then no files will be posted to the server. The HTML element that displays the file select dialog box to the user is the tag <input type=”file” name=”imgfile” />. When user submits the selected image to upload to the server the control goes to the PHP script upload.php which handles the upload of the image file to the server. The code snippet of upload.php is given below:
upload.php
|
<?
include “dbconfig.php”;
$dbconn = mysql_connect($dbhost, $dbusr, $dbpass) or die(”Error Occurred-”.mysql_error());
mysql_select_db($dbname, $dbconn) or die(”Unable to select database”);
if(isset($_REQUEST[submit]) && $_FILES[imgfile][size] > 0)
{
$fileName = $_FILES[imgfile][name]; // image file name
$tmpName = $_FILES[imgfile][tmp_name]; // name of the temporary stored file name
$fileSize = $_FILES[imgfile][size]; // size of the uploaded file
$fileType = $_FILES[imgfile][type]; // file type
$fp = fopen($tmpName, ‘r’); // open a file handle of the temporary file
$imgContent = fread($fp, filesize($tmpName)); // read the temp file
fclose($fp); // close the file handle
$query = “INSERT INTO img_tbl (`img_name`, `img_type`, `img_size`, `img_data` )
VALUES (’$fileName’, ‘$fileType’, ‘$fileSize’, ‘$imgContent’)”;
mysql_query($query) or die(’Error, query failed’);
$imgid = mysql_insert_id(); // autoincrement id of the uploaded entry
mysql_close($dbconn);
echo “<br>Image successfully uploaded to database<br>”;
echo “<a href=\”viewimage.php?id=$imgid\”>View Image</a>”;
}else die(”You have not selected any image”);
?>
|
dbconfig.php
|
<?
$dbhost = “localhost”; // server host name
$dbusr = “root”; // mysql username to connect
$dbpass = “”; //password
$dbname = ”compass”; //database name to select to
?>
|
Let me explain the script step by step. dbconfig.php script contains the database connection information like host name, userid and password and database name to select. The script upload.php first includes the dbconfig.php in order to include the database information because we’ll be executing the SQL to insert the uploaded image file into the database BLOB field.
$_FILES is the global variable which is created by PHP itself to hold the information about the uploaded file object. First the uploaded image is stored as a temp file in the temp folder of the server. The temp name can be retrieved from $_FILES[imgfile][tmp_name]. Now the temp image is read and stored in the BLOB field.
The next script will show you how you can download the image from the BLOB field of the MySQL database and display to the browser.
showimages.php
|
<?
include “dbconfig.php”;
$dbconn = mysql_connect($dbhost, $dbusr, $dbpass) or die(”Error Occurred-”.mysql_error());
mysql_select_db($dbname, $dbconn) or die(”Unable to select database”);
$query = “SELECT `id`, `img_name`, `img_type`, `img_size`, `img_data`
FROM img_tbl ORDER BY `id`”;
$result = mysql_query($query) or die(’Error, query failed’);
while($row = mysql_fetch_array($result)){
echo “<img src=\”viewimage.php?id=$row[id]\” width=\”55\” height=\”55\” /> <br/>”;
}
mysql_close($dbconn);
?>
|
This script is actually fetching all the data from database and printing the images in the <img src=””/> tag. So the content is written to the browser by the viewimage.php script.
viewimage.php
|
<?
if(isset($_REQUEST['id']))
{
// get the file with the id from database
include “dbconfig.php”;
$dbconn = mysql_connect($dbhost, $dbusr, $dbpass) or die(”Error Occurred-”.mysql_error());
mysql_select_db($dbname, $dbconn) or die(”Unable to select database”);
$id = $_ REQUEST ['id'];
$query = “SELECT `img_name`, `img_type`, `img_size`, `img_data`
FROM img_tbl WHERE id = ‘$id’”;
$result = mysql_query($query) or die(mysql_error());
list($name, $type, $size, $content) = mysql_fetch_array($result);
header(”Content-length: $size”);
header(”Content-type: $type”);
print $content;
mysql_close($dbconn);
}
?>
|
In this script the header () function is playing an important role. This function actually tells the browser what to do with the BLOB content. It takes several parameters like size, type etc. “Content-length” sets the size of the file, and “Content-type” sets the type of the file (JPG or GIF or PNG etc.). After setting the header, the actual BLOB content is written using print $content. The <img src=”” /> tag then is able to display the images.
Related Articles:
- File upload to BLOB field and display attachment dialog box Sometimes a developer needs to provide the feature in their...
- Useful List of PHP MySQL Tutorial and Resources PHP is emerging as a leader in the server side...
Thanks for the code.
i’ll try this.
Nice site really!
I keep getting “Unable to select database” and I’ve checked my database connection (host name, userid and password and database name to select) ..it all seems correct. What else could I be missing???
I’m confused..is $dbname referring to the database table name?
Nevermind, on the $dbname, just realized it was the same name as my username. Now I’m getting “Error, query failed” this time and I’m almost sure everything in the “$query” is correct. Anything else I should look out for?
OK…traced the problem..but not sure how to solve it as I get this answer once I upload the image…
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”img_name’,'img_type’,'img_size’,'img_data’) VALUES (’DSCN0196.JPG’,'ima’ at line 1
Hmmm…can I get any help please?? ANyone out there?
I have tried to use above method but the contents was not displayed in the web page.It asks to open or save the file into system. how can i show the contents of word file in mypage stored from mysql .
i copied and pasted as you mention. but it shows the following error:
Warning: fopen(C:\DOCUME~1\polo\LOCALS~1\Temp\php11E.tmp) [function.fopen]: failed to open stream: No error in C:\xampp\htdocs\mybiz\tutorial-5\upload.php on line 14
Warning: fread(): supplied argument is not a valid stream resource in C:\xampp\htdocs\mybiz\tutorial-5\upload.php on line 15
Warning: fclose(): supplied argument is not a valid stream resource in C:\xampp\htdocs\mybiz\tutorial-5\upload.php on line 16
’Error, query failed