Storing and retrieving Images in mysql

[Kelicula]

I'm trying to allow users to upload an avatar, that will be displayed on the site.

I have a column in a database that is set to binary(1) and I would like to store an image in it, that will be uploaded from a "file field" form input.
Then later retrieved and displayed on a web page.

How can I set the content-type to image, for only that item, without changing the whole document. I need "Content-type: text/html\n\n"; for the rest of the page.

I apologize if this is a vague, or "stupid" question, but I honestly have no idea of how to go about it.

Should I create a variable that contains the image file? Or pipe the image buffer to the <img src="$image"> tag?

Or ... Hummmm??

I have no idea!!

please help.

I'm not necessarily expecting the "actual" code that would do this, but can someone explain (theoretically) how this should/could be done, with perl + Template Toolkit?

[eWish]

I would store the image name in the db. Have the file uploaded to the an avatar directory. Then in your template file have an image tag that is populated with the image name from the db for the said user.


--Kevin

[Kelicula]

I would store the image name in the db. Have the file uploaded to the an avatar directory. Then in your template file have an image tag that is populated with the image name from the db for the said user.


--Kevin

Yes, after much goggling and your response, I believe that MUST be the best solution.

Thanks!!

(Although I have read that mysql 5.0 + can facilitate this. I have yet to find out how!)

[eWish]

Set the type to 'BLOB' or 'MEDIUMBLOB' also make sure that you escape the data before the image into the DB.

Character Length Allowed:
BLOB 0 - 65535 about 6meg
MEDIUMBLOB  0 - 16777215 about 16meg

--Kevin

[Kelicula]

Set the type to 'BLOB' or 'MEDIUMBLOB' also make sure that you escape the data before the image into the DB.

Character Length Allowed:
BLOB 0 - 65535 about 6meg
MEDIUMBLOB  0 - 16777215 about 16meg

--Kevin

Then what?

use CGI;
use DBI;
my $dbh = DBI->connect("dbi:mysql:databse:localhost", $handle, $password);
 my $q = new CGI;
 
 
my $file = $q->upload('avatar');
 
my $sth = $dbh-prepare(qq~ UPDATE users SET avatar=? WHERE id =? ~);
 
$sth->execute($file, 10);
 
 

?????

[eWish]

I am not certain if you can upload the file directly to the database. It is my understanding that you upload the file to the server. Then use read or sysread to read the image file data. Then store the data to store in the database. Below is a script to help illustrate how to do it. If you have MySQL Cookbook, chapter 17 covers this and has a sample script.

This code is untested. You should add the code to do a sanity check on the image file which this script does not do.

I hope this helps.

#!/usr/bin/perl -T
 
use strict;
use warnings;
 
use CGI;
use CGI::Carp qw/fatalsToBrowser/;
use File::Basename;
use DBI;
 
my $q = CGI->new;
 
print $q->header();
print $q->start_html();
 
my %config_vars = (    DBServerName    =>     'xxx',
                    DBName                         =>     'xxx',
                    DBUserName                     =>     'xxx',
                    DBPassword                     =>     'xxx',
                    );
 
my ($dbh, $data_source);
my $dataSource = 'DBI:mysql:' . $config_vars{DBName} . ':' . $config_vars{DBServerName};
my $dbh = DBI->connect( $dataSource, $config_vars{DBUserName}, $config_vars{DBPassword}, {RaiseError=>1});                 
 
my $image_dir = 'some_dir/images/';
my $source_file = $q->upload('image_file');
 
 
my ($image_name, undef ,$mime_type) = fileparse($soruce_file,qr{\..*});
my  $image_file  =  $image_dir . $image_name . $mine_type;
my ($image_data, $size); 
 
 
open (my $UPLOAD, '>', $image_file) ||die "Can't open file $!:\n"; 
binmode $UPLOAD;
$size = (stat ($UPLOAD))[7];
while (<$UPLOAD>) {
    print $UPLOAD;
}
close ($UPLOAD);
 
 
open (my $READ_FILE, '<', $image_file) || die "Can't open file $!:\n";
binmode($READ_FILE);
read($READ_FILE, $image_data, $size) == $size || die "Failed to write entire image file $image_file: $!\n";
close ($READ_FILE);
 
 
my $insert = $dbh->prepare(q{INSERT INTO image(image_name, mime_type, image_data) VALUES(?,?,?)});
   $insert->execute($image_name, $mime_type, $image_data);
   $insert->finish();
}
 
unlink $image_file || die "Can't unlink $image_file $!:\n";
 
 
print $q->end_html();
 
1;

--Kevin

[Kelicula]

Thank You!!

I will have to pick over this, but I am definitely excited.
That is another question I had was to see how large the file is before uploading.
I am limiting to 39kb. I tried loading one larger and it just hung...

I need a way to test, and create error accordingly.

the:

$size = (stat ($UPLOAD))[7];
 

Looks good.