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.
