How to store Images / binary data in MySQL Database using BLOB fields. 
May 8th, 2007, 06:28 AM
| | Newbie | | Join Date: Jun 2006
Posts: 29
| |  How to store Images / binary data in MySQL Database using BLOB fields.
how to insert image(like photo) in one particular table (mysql)
help me..
thanks
barrathi
|
May 20th, 2007, 08:43 AM
|  | Moderator | | Join Date: Nov 2006 Location: Iceland Age: 22
Posts: 3,520
| |
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
 |
June 10th, 2007, 01:24 PM
|  | Familiar Sight | | Join Date: Nov 2006 Location: Hyderabad,India Age: 25
Posts: 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
|
June 10th, 2007, 03:17 PM
|  | Site Moderator | | Join Date: Apr 2007 Location: Texas Age: 25
Posts: 5,435
| |
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.
|
June 10th, 2007, 03:18 PM
| | Site Moderator | | Join Date: Apr 2007 Location: Texas Age: 25
Posts: 5,435
| |
Alti, I notice you're using three steps to read the image file: Quote: |
Originally Posted by Atli -
// 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'));
-
|
July 2nd, 2007, 09:22 AM
| | Newbie | | Join Date: Jul 2007
Posts: 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
|
August 3rd, 2007, 01:50 PM
| | Newbie | | Join Date: Aug 2007
Posts: 26
| |
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: -
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.
|
August 3rd, 2007, 03:20 PM
| | Newbie | | Join Date: Aug 2007
Posts: 26
| |
figured it out - needed to append ['tmp_name'] to $_FILES['file']...
|
August 3rd, 2007, 03:27 PM
| | Site Moderator | | Join Date: Apr 2007 Location: Texas Age: 25
Posts: 5,435
| |
Heya, erp.
Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)
|  |
| Thread Tools | Search this Thread | | | |
| | | What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 220,662 network members.
|
