469,357 Members | 1,834 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,357 developers. It's quick & easy.

Storing and retrieving Images in mysql

Kelicula
176 Expert 100+
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
6 3313
eWish
971 Expert 512MB
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
176 Expert 100+
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
971 Expert 512MB
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
176 Expert 100+
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
971 Expert 512MB
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
176 Expert 100+
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.

Similar topics

3 posts views Thread by Mark | last post: by
2 posts views Thread by Tom | last post: by
3 posts views Thread by dave | last post: by
reply views Thread by mrT | last post: by
7 posts views Thread by Benoit St-Jean | last post: by
6 posts views Thread by bissatch | last post: by
1 post views Thread by steelghost | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.