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

**Atli** · May 20 '07, 08:43 AM

Hi.

MySQL can store binary data in BLOB fields.

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

Code:

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:
[code=php]
// Read the image bytes into the $data variable
$fh = fopen("MyImg.jp g", "r");
$data = addslashes(frea d($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($SQ L) or die("Couldn't insert image");
[/code]

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:
[code=php]
// Fetch the latest image.
$SQL = "SELECT ImgData, ImgType FROM Image ORDER BY ImgID DESC LIMIT 1";
$RESULT = @mysql_query($S QL) or die("Query failed");

// Read the results
if($ROW = mysql_fetch_ass oc($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";
}
[/code]

Good luck!

P.S.
I changed the thread title.

MODERATOR

**seshu** · Jun 10 '07, 01:24 PM

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** · Jun 10 '07, 03:17 PM

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** · Jun 10 '07, 03:18 PM

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

Originally posted by Atli

[code=php]
// Read the image bytes into the $data variable
$fh = fopen("MyImg.jp g", "r");
$data = addslashes(frea d($fh, filesize("MyImg .jpg")));
fclose($fh);[/code]

Wouldn't it be faster just to do this:

[code=php]
$data = addslashes(file _get_contents(' MyImg.jpg'));
[/code]

**mission316** · Jul 2 '07, 09:22 AM

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** · Aug 3 '07, 01:50 PM

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:

Code:

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:
[code=php]
// Read the image bytes into the $data variable
$fh = fopen("MyImg.jp g", "r");
$data = addslashes(frea d($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($SQ L) or die("Couldn't insert image");
[/code]

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:
[code=php]
// Fetch the latest image.
$SQL = "SELECT ImgData, ImgType FROM Image ORDER BY ImgID DESC LIMIT 1";
$RESULT = @mysql_query($S QL) or die("Query failed");

// Read the results
if($ROW = mysql_fetch_ass oc($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";
}
[/code]

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:

Code:

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

Code:

 $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** · Aug 3 '07, 03:20 PM

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

**pbmods** · Aug 3 '07, 03:27 PM

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** · Dec 29 '10, 04:48 AM

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

Code:

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.

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment