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:
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:
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:
Good luck!

P.S.
I changed the thread title.

MODERATOR

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

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.

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:

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

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:

And for the database:

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.

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

Heya, erp.

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

I'm having difficulty retrieving the images. This is what I've used to retrieve multiple records, each with an image.
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.