Connecting Tech Pros Worldwide Forums | Help | Site Map

How to store Images / binary data in MySQL Database using BLOB fields.

Newbie
 
Join Date: Jun 2006
Posts: 29
#1: May 8 '07
how to insert image(like photo) in one particular table (mysql)

help me..

thanks
barrathi

Atli's Avatar
Moderator
 
Join Date: Nov 2006
Location: Iceland
Posts: 3,748
#2: May 20 '07

re: How to store Images / binary data in MySQL Database using BLOB fields.


Hi.

MySQL can store binary data in BLOB fields.

So to store images you would have to set up a table somwhat like this:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE Image
  2. (
  3.   ImgID SERIAL,
  4.   ImgTitle VARCHAR(255) NOT NULL Default 'Untitled',
  5.   ImgType VARCHAR(255) NOT NULL Default 'png',
  6.   ImgData MEDIUMBLOB NOT NULL,
  7.   Primary Key(ImgID)
  8. )
  9.  
You would have to provide a script that reads the image as binary data and inserts it into the ImgData field of the table.

With php this could be done like this:
Expand|Select|Wrap|Line Numbers
  1. // Read the image bytes into the $data variable
  2. $fh = fopen("MyImg.jpg", "r");
  3. $data = addslashes(fread($fh, filesize("MyImg.jpg")));
  4. fclose($fh);
  5.  
  6. // Create the query
  7. $SQL = "
  8. INSERT INTO Image(ImgTitle, ImgType, ImgData)
  9. VALUES('My Image', 'jpg', '$data')";
  10.  
  11. // Execute the query        
  12. $RESULT = mysql_query($SQL) or die("Couldn't insert image");
  13.  
To get the image from the database another script is needed. This script will have to output a file of the same type stored in our ImgType field that contains the binary data in our BLOB field.

Again, this can be accomplished with PHP like this:
Expand|Select|Wrap|Line Numbers
  1. // Fetch the latest image.
  2. $SQL = "SELECT ImgData, ImgType FROM Image ORDER BY ImgID DESC LIMIT 1";
  3. $RESULT = @mysql_query($SQL) or die("Query failed");
  4.  
  5. // Read the results
  6. if($ROW = mysql_fetch_assoc($RESULT)) {    
  7.   // Set the content type and print the data.
  8.   header("Content-type: img/". $ROW['ImgType']);
  9.   echo $ROW['ImgData'];
  10. }
  11. else {
  12.   // No images were found, print error
  13.   echo "No images were found";
  14. }
  15.  
Good luck!

P.S.
I changed the thread title.

MODERATOR
seshu's Avatar
Familiar Sight
 
Join Date: Nov 2006
Location: Hyderabad,India
Posts: 156
#3: Jun 10 '07

re: How to store Images / binary data in MySQL Database using BLOB fields.


Hi I am happy to see that there is some procedure insert an image into mysql table because i have been tring this since 4 months but i dint find any where but the problem here is the above given code is in php and iwant this in vb.net
someone plzz look in to this
pbmods's Avatar
Site Moderator
 
Join Date: Apr 2007
Location: Texas
Posts: 5,435
#4: Jun 10 '07

re: How to store Images / binary data in MySQL Database using BLOB fields.


Quote:

Originally Posted by seshu

but i dint find any where but the problem here is the above given code is in php and iwant this in vb.net
someone plzz look in to this

Try creating a thread in the VB forum with a link to this thread as an example.
pbmods's Avatar
Site Moderator
 
Join Date: Apr 2007
Location: Texas
Posts: 5,435
#5: Jun 10 '07

re: How to store Images / binary data in MySQL Database using BLOB fields.


Alti, I notice you're using three steps to read the image file:

Quote:

Originally Posted by Atli

Expand|Select|Wrap|Line Numbers
  1. // Read the image bytes into the $data variable
  2. $fh = fopen("MyImg.jpg", "r");
  3. $data = addslashes(fread($fh, filesize("MyImg.jpg")));
  4. fclose($fh);

Wouldn't it be faster just to do this:

Expand|Select|Wrap|Line Numbers
  1. $data = addslashes(file_get_contents('MyImg.jpg'));
  2.  
Newbie
 
Join Date: Jul 2007
Posts: 1
#6: Jul 2 '07

re: How to store Images / binary data in MySQL Database using BLOB fields.


Hey all,

I am coding a website in in DreamWeaver php and using a MySQL database. The problem that I have is that I want to dynamically load an image for every record in the db.

At this stage I am considering using a blob and simply dragging the field into the page. I am not sure if this will work though.

Oh and are there any free tools to add a blob into a db on your webserver from your local pc?

Any help will be appreciated.

Mission316
Newbie
 
Join Date: Aug 2007
Posts: 26
#7: Aug 3 '07

re: How to store Images / binary data in MySQL Database using BLOB fields.


Quote:

Originally Posted by Atli

Hi.

MySQL can store binary data in BLOB fields.

So to store images you would have to set up a table somwhat like this:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE Image
  2. (
  3.   ImgID SERIAL,
  4.   ImgTitle VARCHAR(255) NOT NULL Default 'Untitled',
  5.   ImgType VARCHAR(255) NOT NULL Default 'png',
  6.   ImgData MEDIUMBLOB NOT NULL,
  7.   Primary Key(ImgID)
  8. )
  9.  
You would have to provide a script that reads the image as binary data and inserts it into the ImgData field of the table.

With php this could be done like this:
Expand|Select|Wrap|Line Numbers
  1. // Read the image bytes into the $data variable
  2. $fh = fopen("MyImg.jpg", "r");
  3. $data = addslashes(fread($fh, filesize("MyImg.jpg")));
  4. fclose($fh);
  5.  
  6. // Create the query
  7. $SQL = "
  8. INSERT INTO Image(ImgTitle, ImgType, ImgData)
  9. VALUES('My Image', 'jpg', '$data')";
  10.  
  11. // Execute the query        
  12. $RESULT = mysql_query($SQL) or die("Couldn't insert image");
  13.  
To get the image from the database another script is needed. This script will have to output a file of the same type stored in our ImgType field that contains the binary data in our BLOB field.

Again, this can be accomplished with PHP like this:
Expand|Select|Wrap|Line Numbers
  1. // Fetch the latest image.
  2. $SQL = "SELECT ImgData, ImgType FROM Image ORDER BY ImgID DESC LIMIT 1";
  3. $RESULT = @mysql_query($SQL) or die("Query failed");
  4.  
  5. // Read the results
  6. if($ROW = mysql_fetch_assoc($RESULT)) {    
  7.   // Set the content type and print the data.
  8.   header("Content-type: img/". $ROW['ImgType']);
  9.   echo $ROW['ImgData'];
  10. }
  11. else {
  12.   // No images were found, print error
  13.   echo "No images were found";
  14. }
  15.  
Good luck!

P.S.
I changed the thread title.

MODERATOR

I am trying to incorporate an image file within a BLOB field of a 'latest news' table, so that it can be pulled dynamically with the rest of the news text. So far I have succeeded in displaying this once the image is already in the database (using phpmyadmin).

I have been less successful in uploading the image into the database from a php newsupdate page using a form.
The relevant code snippets are shown below:

Expand|Select|Wrap|Line Numbers
  1. <form action="newsupdate.php" method="POST" enctype="multipart/form-data" name="frm_insertnews" id="frm_insertnews">
  2. .
  3. .
  4. .
  5.  
  6. <input type="file" name="file" id="file" />
  7.       <input type="submit" name="submit" id="submit" value="Insert" />
  8.  
  9. </form>
  10.  
And for the database:

Expand|Select|Wrap|Line Numbers
  1.  $file = fopen($_FILES['file'], "r");
  2. $image = addslashes(fread($file, filesize($_FILES['file'])));
  3. fclose($file);
  4.  
  5.  
  6.   $insertSQL = "INSERT INTO tbl_news (date_added, title, description, picture, author) VALUES (CURDATE(), '".$_POST['title']."', '".$_POST['text']."', '$image', '".$_POST['author']."')";
  7.  
When the form is submitted, the other fields are updated, but the blob is only a few bytes....

Any help / suggestions would be greatly appreciated.
Newbie
 
Join Date: Aug 2007
Posts: 26
#8: Aug 3 '07

re: How to store Images / binary data in MySQL Database using BLOB fields.


figured it out - needed to append ['tmp_name'] to $_FILES['file']...
pbmods's Avatar
Site Moderator
 
Join Date: Apr 2007
Location: Texas
Posts: 5,435
#9: Aug 3 '07

re: How to store Images / binary data in MySQL Database using BLOB fields.


Heya, erp.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)
Reply