i'm bored, and this seems like some nice information, so i'm going to write a tutorial for it... i learned how to do this from phpriot.com, but the tutorial is in my own words, not copied/pasted from their site
How to store/retrieve images in a MySQL database:
First off, the images are going to be stored in what is called a BLOB column
Q: What is a BLOB column?
A: A Binary Large Object, basically, it's used to store binary data in a MySQL database
Creating the image table:
You must create a table to store the images in.
Create a table named Images (or whatever you like) with 6 columns
Name the first column Image_ID with type tinyint and a length of 3, this should be an auto-incremented column
Name the second column Image_Type with type varchar and length of 25, we'll use this to store the mime type
Name the third column Image with type blob, this is where we will store the actual image
Name the fourth column Image_Size with type varchar and length of 25, this will tell us the size of the image
Name the fifth column Image_Category with type varchar and length of 25, this will be used to categorize the images
Name the sixth column Image_Name with type varchar and length of 50, if you havn't guessed, this will be the name of our image
the following code *should* create the table for you, although i havn't tested it
PHP Code:
create table Images (
Image_ID tinyint(3) not null default '0',
Image_Type varchar(25) not null default '',
Image blob not null,
Image_Size varchar(25) not null default '',
Image_Category varchar(25) not null default '',
Image_Name varchar(50) not null default ''
);
Next we need the upload form
ok, i lied, the following is a direct copy/paste from phpriot
Code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head><title>File Upload To Database</title></head>
<body>
<h3>Please Choose a File and click Submit</h3>
<form enctype="multipart/form-data" action="upload_image2.php" method="post">
<input type="hidden" name="MAX_FILE_SIZE" value="10000000" />
<input name="userfile[]" type="file" />
<input type="submit" value="Submit" />
</form>
</body>
</html>
this is just a basic form, put it in a .php file (we'll name it upload_image.php for this tutorial)
Create the uploader
Now we have to create the php script that does the actual uploading, this is another direct copy/paste from phpriot
PHP Code:
<?php
// check if a file was submitted
if(!isset($_FILES['userfile'])) {
echo '<p>Please select a file</p>';
}
else
{
try {
upload();
// give praise and thanks to the php gods
echo '<p>Thank you for submitting</p>';
}
catch(Exception $e) {
echo $e->getMessage();
echo 'Sorry, could not upload file';
}
}
?>
don't run this script yet as there isn't an upload function created
as of yet, for now, just put it in a file (we'll name the file upload_image2.php for this tutorial)
basically, what this does, is check that the user actually entered a file, if so, it attempts to upload the image
if there is an error, it will return the error and abort
next we need to create the upload function
following is another copy/paste from phpriot
PHP Code:
<?php
// the upload function
function upload(){
if(is_uploaded_file($_FILES['userfile']['tmp_name'])) {
// check the file is less than the maximum file size
if($_FILES['userfile']['size'] < $maxsize)
{
// prepare the image for insertion
$imgData =addslashes (file_get_contents($_FILES['userfile']['tmp_name']));
// $imgData = addslashes($_FILES['userfile']);
// get the image info..
$size = getimagesize($_FILES['userfile']['tmp_name']);
// put the image in the db...
// database connection
mysql_connect("localhost", "$username", "$password") OR DIE (mysql_error());
// select the db
mysql_select_db ("$dbname") OR DIE ("Unable to select db".mysql_error());
// our sql query
$sql = "INSERT INTO Images
( Image_ID , Image_Type ,Image, Image_Size, Image_Name)
VALUES
('', '{$size['mime']}', '{$imgData}', '{$size[3]}', '{$_FILES['userfile']['name']}')";
// insert the image
if(!mysql_query($sql)) {
echo 'Unable to upload file';
}
}
}
else {
// if the file is not less than the maximum allowed, print an error
echo
'<div>File exceeds the Maximum File limit</div>
<div>Maximum File limit is '.$maxsize.'</div>
<div>File '.$_FILES['userfile']['name'].' is '.$_FILES['userfile']['size'].' bytes</div>
<hr />';
}
}
?>
put this at the top of the upload_image2.php file
what this does:
1. check if the file is an allowed type
2. make sure the uploaded file isn't bigger than the max file size
3. format the binary data for insertion into the database
4. connect to the database
5. insert the data
now we must display the image
another copy/paste from phpriot
PHP Code:
<?php
// just so we know it is broken
error_reporting(E_ALL);
// some basic sanity checks
if(isset($_GET['Image_ID']) && is_numeric($_GET['Image_ID'])) {
//connect to the db
$link = mysql_connect("localhost", "username", "password") or die("Could not connect: " . mysql_error());
// select our database
mysql_select_db("Images") or die(mysql_error());
// get the image from the db
$sql = "SELECT Image FROM Images WHERE Image_ID=0";
// the result of the query
$result = mysql_query("$sql") or die("Invalid query: " . mysql_error());
// set the header for the image
header("Content-type: image/jpeg");
echo mysql_result($result, 0);
// close the db link
mysql_close($link);
}
else {
echo 'Please use a real id number';
}
?>
put this in a file named get_image.php
Displaying the image and related information
another copy/paste
PHP Code:
<?php
// again we check the $_GET variable
if(isset($_GET['Image_ID']) && is_numeric($_GET['Image_ID'])) {
$sql = "SELECT Image_Type, Image_Size, Image_Name FROM Images WHERE Image_ID=".$_GET['Image_ID'];
$link = mysql_connect("localhost", "username", "password") or die("Could not connect: " . mysql_error());
// select our database
mysql_select_db("Images") or die(mysql_error());
$result = mysql_query($sql) or die("Invalid query: " . mysql_error());
while($row=mysql_fetch_array($result)) {
echo 'This is '.$row['Image_Name'].' from the database<br />';
echo '<img '.$row['Image_Size'].' src="http://forums.x10hosting.com/tutorials/view_image.php?Image_ID='.$_GET['Image_ID'].'">';
}
}
else {
echo 'File not selected';
}
?>
put this in a file named view_image.php
there you have it, that's how you store and display images in a MySQL database, suggestions/comments appreciated
if you like this tutorial, please rep me for it
LinkBack URL
About LinkBacks
Reply With Quote
Re: How to store images in a MySQL database
Originally Posted by tstivers
