How to store images in a MySQL database

T

tstivers

New Member
Messages
7
Reaction score
0
Points
0
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: 
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: 
<?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: 
<?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: 
<?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: 
<?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
 
Last edited:
S

stalkio

New Member
Messages
45
Reaction score
0
Points
0
This is all created using PHPMyAdmin right? Where do you upload it to? - I have no idea where to implement it..but it sure sounds useful..
 
jspcodes

jspcodes

New Member
Messages
60
Reaction score
0
Points
0
Every thing is fine.
But instead of using BLOB, one could store the image in the folder in the server and move the relative link to database table. IBM appreciated me for doing this in my project. They told me it has several advantages, like it avoids the database getting bigger & bigger.
 
brettzuya

brettzuya

New Member
Messages
30
Reaction score
0
Points
0
tstivers said:
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: 
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: 
<?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: 
<?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: 
<?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: 
<?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
Click to expand...
;) i really appreciate of what you have done here,thats helpful info men!!your not only helping us all but you really did a good idea,thanks for the clues thats very useful :biggrin:
 
S

stalkio

New Member
Messages
45
Reaction score
0
Points
0
I can't get this to work properly with the following message:

File exceeds the Maximum File limit
Maximum File limit is
File Array is Array bytes
Thank you for submitting
Click to expand...

Anyone getting this problem?
 
A

admbart

New Member
Messages
3
Reaction score
0
Points
0
This is a really helpful stuff, thanks guys. Sadly, doesn't seem to work on this site which stops binary file uploads.
Really want to add a facility where users can upload pictures which this site will then show.
Googled transloading, loading user images to a separate free image host, but not quite sure how to get it working from this site.
Need to be able to specify name and location of file in transload so that the right image can be shown.
Anyone done anything similar or found a way of allowing users to upload images here?
Edit:
stalkio said:
I can't get this to work properly with the following message:



Anyone getting this problem?
Click to expand...
Same here - this site won't let image uploads. Checked error log:
ALERT - uploaded file contains binary data - file dropped
V. frustrating
 
Last edited:
balaji2u

balaji2u

New Member
Messages
410
Reaction score
2
Points
0
im just wondering where did u get the tut.. coz as far as i searched for exactly the same topic i failed ...
Great and good work ..
 
jspcodes

jspcodes

New Member
Messages
60
Reaction score
0
Points
0
Thanks man. There is another way of uploading images instead of using BLOB or CLOB we can upload the picture to a folder in server and we can move the link to database.So if we need that file to be accessed we can use that link.
Because industry people preferred that method over this.

Kudos for your great effort in writing this tutorial
 
You must log in or register to reply here.
Top