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

[barrathi]

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

help me..

thanks
barrathi

[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:

CREATE TABLE Image
(
  ImgID SERIAL,
  ImgTitle VARCHAR(255) NOT NULL Default 'Untitled',
  ImgType VARCHAR(255) NOT NULL Default 'png',
  ImgData MEDIUMBLOB NOT NULL,
  Primary Key(ImgID)
)
 

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:

// Read the image bytes into the $data variable
$fh = fopen("MyImg.jpg", "r");
$data = addslashes(fread($fh, filesize("MyImg.jpg")));
fclose($fh);
 
// Create the query
$SQL = "
INSERT INTO Image(ImgTitle, ImgType, ImgData)
VALUES('My Image', 'jpg', '$data')";
 
// Execute the query        
$RESULT = mysql_query($SQL) or die("Couldn't insert image");
 

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:

// Fetch the latest image.
$SQL = "SELECT ImgData, ImgType FROM Image ORDER BY ImgID DESC LIMIT 1";
$RESULT = @mysql_query($SQL) or die("Query failed");
 
// Read the results
if($ROW = mysql_fetch_assoc($RESULT)) {    
  // Set the content type and print the data.
  header("Content-type: img/". $ROW['ImgType']);
  echo $ROW['ImgData'];
}
else {
  // No images were found, print error
  echo "No images were found";
}
 

Good luck!

P.S.
I changed the thread title.

MODERATOR

[seshu]

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]

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]

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

// Read the image bytes into the $data variable
$fh = fopen("MyImg.jpg", "r");
$data = addslashes(fread($fh, filesize("MyImg.jpg")));
fclose($fh);

Wouldn't it be faster just to do this:

$data = addslashes(file_get_contents('MyImg.jpg'));
 

[mission316]

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

[erp23]

Hi.

MySQL can store binary data in BLOB fields.

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

CREATE TABLE Image
(
  ImgID SERIAL,
  ImgTitle VARCHAR(255) NOT NULL Default 'Untitled',
  ImgType VARCHAR(255) NOT NULL Default 'png',
  ImgData MEDIUMBLOB NOT NULL,
  Primary Key(ImgID)
)
 

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:

// Read the image bytes into the $data variable
$fh = fopen("MyImg.jpg", "r");
$data = addslashes(fread($fh, filesize("MyImg.jpg")));
fclose($fh);
 
// Create the query
$SQL = "
INSERT INTO Image(ImgTitle, ImgType, ImgData)
VALUES('My Image', 'jpg', '$data')";
 
// Execute the query        
$RESULT = mysql_query($SQL) or die("Couldn't insert image");
 

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:

// Fetch the latest image.
$SQL = "SELECT ImgData, ImgType FROM Image ORDER BY ImgID DESC LIMIT 1";
$RESULT = @mysql_query($SQL) or die("Query failed");
 
// Read the results
if($ROW = mysql_fetch_assoc($RESULT)) {    
  // Set the content type and print the data.
  header("Content-type: img/". $ROW['ImgType']);
  echo $ROW['ImgData'];
}
else {
  // No images were found, print error
  echo "No images were found";
}
 

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:

<form action="newsupdate.php" method="POST" enctype="multipart/form-data" name="frm_insertnews" id="frm_insertnews">
.
.
.
 
<input type="file" name="file" id="file" />
      <input type="submit" name="submit" id="submit" value="Insert" />
 
</form>
 

And for the database:

 $file = fopen($_FILES['file'], "r");
$image = addslashes(fread($file, filesize($_FILES['file'])));
fclose($file);
 
 
  $insertSQL = "INSERT INTO tbl_news (date_added, title, description, picture, author) VALUES (CURDATE(), '".$_POST['title']."', '".$_POST['text']."', '$image', '".$_POST['author']."')";
 

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.

[erp23]

figured it out - needed to append ['tmp_name'] to $_FILES['file']...

[pbmods]

Heya, erp.

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

[basil rayner]

I'm having difficulty retrieving the images. This is what I've used to retrieve multiple records, each with an image.

display_users.php
========================================================
<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
require ("connect.php");
 
// select our database
        mysql_select_db("basil_bookmark") or die(mysql_error());
$result = mysql_query("SELECT * FROM user ORDER BY id DESC");
 
//Display Table
//header("Content-type: img/". $ROW['image_type']);
if (!$result)
 {
 die('<p>Error performing query: ' . mysql_error() . '</p>');
 }
 
echo "<table border='1' width='80%'cellpadding='3' cellspacing='0'>";
echo "<tr>";
echo "<td align='center' bgcolor='#FF3399'><strong>Username</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>First Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Last Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Favicon</strong></td>";
 
echo "</tr>";
 
 
while ($row = mysql_fetch_array($result)) {
 
echo "<tr>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["username"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["first_name"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["last_name"] ."</strong></td>";
 
 
//echo "<td bgcolor='#FFccff' align='center'>".$row["image_data"]. "</td>";
 echo "<td bgcolor='#FFccff' align='center'><img src='{$row['image_data']}' /></td>";
echo "</tr>";
 
}    
 
 echo ("\n</table>\n");
 print "There are " . mysql_num_rows($result) . " rows in the Bookmarks database\n";
 
?>
 

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.