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

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

P: 29
how to insert image(like photo) in one particular table (mysql)

help me..

thanks
barrathi
May 8 '07 #1
Share this Question
Share on Google+
9 Replies


Atli
Expert 5K+
P: 5,058
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
May 20 '07 #2

seshu
100+
P: 156
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
Jun 10 '07 #3

pbmods
Expert 5K+
P: 5,821
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.
Jun 10 '07 #4

pbmods
Expert 5K+
P: 5,821
Alti, I notice you're using three steps to read the image file:

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.  
Jun 10 '07 #5

P: 1
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
Jul 2 '07 #6

P: 26
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.
Aug 3 '07 #7

P: 26
figured it out - needed to append ['tmp_name'] to $_FILES['file']...
Aug 3 '07 #8

pbmods
Expert 5K+
P: 5,821
Heya, erp.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)
Aug 3 '07 #9

P: 1
I'm having difficulty retrieving the images. This is what I've used to retrieve multiple records, each with an image.
Expand|Select|Wrap|Line Numbers
  1. display_users.php
  2. ========================================================
  3. <?php
  4. ini_set ("display_errors", "1");
  5. error_reporting(E_ALL);
  6. require ("connect.php");
  7.  
  8. // select our database
  9.         mysql_select_db("basil_bookmark") or die(mysql_error());
  10. $result = mysql_query("SELECT * FROM user ORDER BY id DESC");
  11.  
  12. //Display Table
  13. //header("Content-type: img/". $ROW['image_type']);
  14. if (!$result)
  15.  {
  16.  die('<p>Error performing query: ' . mysql_error() . '</p>');
  17.  }
  18.  
  19. echo "<table border='1' width='80%'cellpadding='3' cellspacing='0'>";
  20. echo "<tr>";
  21. echo "<td align='center' bgcolor='#FF3399'><strong>Username</strong></td>";
  22. echo "<td align='center' bgcolor='#FF3399'><strong>First Name</strong></td>";
  23. echo "<td align='center' bgcolor='#FF3399'><strong>Last Name</strong></td>";
  24. echo "<td align='center' bgcolor='#FF3399'><strong>Favicon</strong></td>";
  25.  
  26. echo "</tr>";
  27.  
  28.  
  29. while ($row = mysql_fetch_array($result)) {
  30.  
  31. echo "<tr>";
  32. echo "<td bgcolor='#FFccff' align='center'><strong>". $row["username"] ."</strong></td>";
  33. echo "<td bgcolor='#FFccff' align='center'><strong>". $row["first_name"] ."</strong></td>";
  34. echo "<td bgcolor='#FFccff' align='center'><strong>". $row["last_name"] ."</strong></td>";
  35.  
  36.  
  37. //echo "<td bgcolor='#FFccff' align='center'>".$row["image_data"]. "</td>";
  38.  echo "<td bgcolor='#FFccff' align='center'><img src='{$row['image_data']}' /></td>";
  39. echo "</tr>";
  40.  
  41. }    
  42.  
  43.  echo ("\n</table>\n");
  44.  print "There are " . mysql_num_rows($result) . " rows in the Bookmarks database\n";
  45.  
  46. ?>
  47.  
I've messed with this for days, trying to retrieve an image. The best I've got so far is jumbled data.
I'd appreciate your ideas.
Dec 29 '10 #10

Post your reply

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