By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,243 Members | 1,356 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,243 IT Pros & Developers. It's quick & easy.

Storing and retrieving Images in mysql

Kelicula
Expert 100+
P: 176
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?
Feb 5 '08 #1
Share this Question
Share on Google+
6 Replies


eWish
Expert 100+
P: 971
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
Feb 5 '08 #2

Kelicula
Expert 100+
P: 176
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!)
Feb 6 '08 #3

eWish
Expert 100+
P: 971
Set the type to 'BLOB' or 'MEDIUMBLOB' also make sure that you escape the data before the image into the DB.

Expand|Select|Wrap|Line Numbers
  1. Character Length Allowed:
  2. BLOB 0 - 65535 about 6meg
  3. MEDIUMBLOB  0 - 16777215 about 16meg
--Kevin
Feb 6 '08 #4

Kelicula
Expert 100+
P: 176
Set the type to 'BLOB' or 'MEDIUMBLOB' also make sure that you escape the data before the image into the DB.

Expand|Select|Wrap|Line Numbers
  1. Character Length Allowed:
  2. BLOB 0 - 65535 about 6meg
  3. MEDIUMBLOB  0 - 16777215 about 16meg
--Kevin

Then what?
Expand|Select|Wrap|Line Numbers
  1. use CGI;
  2. use DBI;
  3. my $dbh = DBI->connect("dbi:mysql:databse:localhost", $handle, $password);
  4.  my $q = new CGI;
  5.  
  6.  
  7. my $file = $q->upload('avatar');
  8.  
  9. my $sth = $dbh-prepare(qq~ UPDATE users SET avatar=? WHERE id =? ~);
  10.  
  11. $sth->execute($file, 10);
  12.  
  13.  

?????
Feb 8 '08 #5

eWish
Expert 100+
P: 971
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.

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl -T
  2.  
  3. use strict;
  4. use warnings;
  5.  
  6. use CGI;
  7. use CGI::Carp qw/fatalsToBrowser/;
  8. use File::Basename;
  9. use DBI;
  10.  
  11. my $q = CGI->new;
  12.  
  13. print $q->header();
  14. print $q->start_html();
  15.  
  16. my %config_vars = (    DBServerName    =>     'xxx',
  17.                     DBName                         =>     'xxx',
  18.                     DBUserName                     =>     'xxx',
  19.                     DBPassword                     =>     'xxx',
  20.                     );
  21.  
  22. my ($dbh, $data_source);
  23. my $dataSource = 'DBI:mysql:' . $config_vars{DBName} . ':' . $config_vars{DBServerName};
  24. my $dbh = DBI->connect( $dataSource, $config_vars{DBUserName}, $config_vars{DBPassword}, {RaiseError=>1});                 
  25.  
  26. my $image_dir = 'some_dir/images/';
  27. my $source_file = $q->upload('image_file');
  28.  
  29.  
  30. my ($image_name, undef ,$mime_type) = fileparse($soruce_file,qr{\..*});
  31. my  $image_file  =  $image_dir . $image_name . $mine_type;
  32. my ($image_data, $size); 
  33.  
  34.  
  35. open (my $UPLOAD, '>', $image_file) ||die "Can't open file $!:\n"; 
  36. binmode $UPLOAD;
  37. $size = (stat ($UPLOAD))[7];
  38. while (<$UPLOAD>) {
  39.     print $UPLOAD;
  40. }
  41. close ($UPLOAD);
  42.  
  43.  
  44. open (my $READ_FILE, '<', $image_file) || die "Can't open file $!:\n";
  45. binmode($READ_FILE);
  46. read($READ_FILE, $image_data, $size) == $size || die "Failed to write entire image file $image_file: $!\n";
  47. close ($READ_FILE);
  48.  
  49.  
  50. my $insert = $dbh->prepare(q{INSERT INTO image(image_name, mime_type, image_data) VALUES(?,?,?)});
  51.    $insert->execute($image_name, $mime_type, $image_data);
  52.    $insert->finish();
  53. }
  54.  
  55. unlink $image_file || die "Can't unlink $image_file $!:\n";
  56.  
  57.  
  58. print $q->end_html();
  59.  
  60. 1;
--Kevin
Feb 8 '08 #6

Kelicula
Expert 100+
P: 176
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:
Expand|Select|Wrap|Line Numbers
  1. $size = (stat ($UPLOAD))[7];
  2.  
Looks good.
Feb 9 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.