Images In MySQL
Moderator: General Moderators
-
Rob Watton
- Forum Newbie
- Posts: 4
- Joined: Wed Mar 19, 2003 5:22 am
- Location: Staffordshie, UK
Images In MySQL
Hi
Can any advise please.
Is it possible to store images (JPG, GIF, etc) into a BLOB field within MySQL.
If this is possible can it be scripted with PHP and could someone give me an idea of the syntax for putting images into MySQL and also getting them out.
Can any advise please.
Is it possible to store images (JPG, GIF, etc) into a BLOB field within MySQL.
If this is possible can it be scripted with PHP and could someone give me an idea of the syntax for putting images into MySQL and also getting them out.
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
-
Rob Watton
- Forum Newbie
- Posts: 4
- Joined: Wed Mar 19, 2003 5:22 am
- Location: Staffordshie, UK
-
superwormy
- Forum Commoner
- Posts: 67
- Joined: Fri Oct 04, 2002 9:25 am
- Location: CT
AW:
Hi,
This is a nice and working example:
http://www.phpbuilder.com/columns/florian19991014.php3
But superwormy is right: it's better not to do this.
This is a nice and working example:
http://www.phpbuilder.com/columns/florian19991014.php3
But superwormy is right: it's better not to do this.
-
faerieprince
- Forum Newbie
- Posts: 1
- Joined: Mon May 19, 2003 4:05 pm
- Location: omaha, ne
Twigletmac: do you think you could help?
i am new to php and mysql. i have been trying the path to image approach, but it is not working. i will post my script below so you can *hopefully* look at it and tell me what i might be doing wrong. this is just the part that would display the results:
echo "<table><tr><td>Number of photos found: " .$num_results."</td></tr>";
for ($i=0; $i < $num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<tr><td><strong>".($i+1).". Caption: ";
echo htmlspecialchars( stripslashes($row["caption"]));
echo "</strong></td></tr><tr><td><img src=/images/";
echo htmlspecialchars( stripslashes($row["photo"]));
echo "\"></td></tr>";
}
echo "</table>";
i am able to connect and display everything except the image. do i need to escape the '/' or something? i have not found anything (although i have just started scouring the boards) that talks about this idea. they all talk about actually using 'blob' which i have no immediate interest in, although i would use it if it were the only working solution.
anyway, i hope you have some time to try and help or even point me to a tutorial.
thanks!
echo "<table><tr><td>Number of photos found: " .$num_results."</td></tr>";
for ($i=0; $i < $num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<tr><td><strong>".($i+1).". Caption: ";
echo htmlspecialchars( stripslashes($row["caption"]));
echo "</strong></td></tr><tr><td><img src=/images/";
echo htmlspecialchars( stripslashes($row["photo"]));
echo "\"></td></tr>";
}
echo "</table>";
i am able to connect and display everything except the image. do i need to escape the '/' or something? i have not found anything (although i have just started scouring the boards) that talks about this idea. they all talk about actually using 'blob' which i have no immediate interest in, although i would use it if it were the only working solution.
anyway, i hope you have some time to try and help or even point me to a tutorial.
thanks!
- Wayne Herbert
- Forum Commoner
- Posts: 34
- Joined: Tue Apr 29, 2003 3:13 pm
- Location: Houston, Texas
The issue of putting images into a MySql database is not so black and white as some posters would suggest. The original reference to not putting images into the MySql databases no longer exists in the MySql documentation.
Having said that,
a) using a blob, text or varchar field in a table automatically gives you a variable record length. If a variable length record is updated, it is not written back to the original location, but to the end of the database. Thus, the database grows and key trees may need to be split. Thus: if you do a lot of updates, blobs, texts (or varchars) will cause your db to grow and slow down. If you update only infrequently, or dump and rebuild a table, then this is not an issue.
UPDATE: I have to correct myself here. According to the comparision page between MySql and PostgreSQL (http://www.mysql.com/documentation/mysq ... PostgreSQL), MySql automatically recaptures lost space created with updates of variable length records. It says you nver need to run maintenance on a MySql database. If true, then a) is a non operational reason for not using blobs.
b) room must be made in memory for all the records that are selected in a query. If you have large blobs, and you retrieve a bunch of records at once, you will use a bunch of memory. There is a page on the MySql site that describes how memory is used... http://www.mysql.com/documentation/mysq ... Memory_use. So, if you have small images (like thumbnails) or retrieve only a handful of records, then you won't have a problem.
So, worst case scenario is you load up a db with 10,000 2 megabyte images, update them continuously through the day, and read 1000 of them at a time with a SELECT statement. I expect you will be real unhappy.
But, create a db with a couple of thousand entries, limit the blob size to something reasonable, reduce or eliminate real time updating, and control the number of records returned in a SELECT and it will work just fine.
This URL shows how to add images to a database.
http://www.phpbuilder.com/columns/florian19991014.php3
I wrote a photo gallery app that I use for my photos and is derived in part from florian's code (I found all the gotchas with image display). This app uses thumbnail images which are stored in the database (which is actually derived from a Thumbsplus database), and it uses a text field (another blob) for the photo annotation. Actual photos are stored as files external to the databse... it is a hassle to load a 1000 photos.
If you want the scripts for this site, email me. There are three, one of which is a bit long to post here.
http://www.herbhost.com/seasia/
Having said that,
a) using a blob, text or varchar field in a table automatically gives you a variable record length. If a variable length record is updated, it is not written back to the original location, but to the end of the database. Thus, the database grows and key trees may need to be split. Thus: if you do a lot of updates, blobs, texts (or varchars) will cause your db to grow and slow down. If you update only infrequently, or dump and rebuild a table, then this is not an issue.
UPDATE: I have to correct myself here. According to the comparision page between MySql and PostgreSQL (http://www.mysql.com/documentation/mysq ... PostgreSQL), MySql automatically recaptures lost space created with updates of variable length records. It says you nver need to run maintenance on a MySql database. If true, then a) is a non operational reason for not using blobs.
b) room must be made in memory for all the records that are selected in a query. If you have large blobs, and you retrieve a bunch of records at once, you will use a bunch of memory. There is a page on the MySql site that describes how memory is used... http://www.mysql.com/documentation/mysq ... Memory_use. So, if you have small images (like thumbnails) or retrieve only a handful of records, then you won't have a problem.
So, worst case scenario is you load up a db with 10,000 2 megabyte images, update them continuously through the day, and read 1000 of them at a time with a SELECT statement. I expect you will be real unhappy.
But, create a db with a couple of thousand entries, limit the blob size to something reasonable, reduce or eliminate real time updating, and control the number of records returned in a SELECT and it will work just fine.
This URL shows how to add images to a database.
http://www.phpbuilder.com/columns/florian19991014.php3
I wrote a photo gallery app that I use for my photos and is derived in part from florian's code (I found all the gotchas with image display). This app uses thumbnail images which are stored in the database (which is actually derived from a Thumbsplus database), and it uses a text field (another blob) for the photo annotation. Actual photos are stored as files external to the databse... it is a hassle to load a 1000 photos.
If you want the scripts for this site, email me. There are three, one of which is a bit long to post here.
http://www.herbhost.com/seasia/
Last edited by Wayne Herbert on Mon May 19, 2003 7:20 pm, edited 1 time in total.
- Wayne Herbert
- Forum Commoner
- Posts: 34
- Joined: Tue Apr 29, 2003 3:13 pm
- Location: Houston, Texas
Re: Twigletmac: do you think you could help?
1) Why are you using using stripslashes? Data that is dumped to text needs to have slashes in it, but once in the db blob field, the image data (and the text data, for that matter) are in the correct format. No slashes are stored in the blob field... except of course, those characters which are supposed to be slashes).faerieprince wrote:i am new to php and mysql.thanks!Code: Select all
echo "<tr><td><strong>".($i+1).". Caption: "; echo htmlspecialchars( stripslashes($row["caption"])); echo "</strong></td></tr><tr><td><img src=/images/"; echo htmlspecialchars( stripslashes($row["photo"])); echo ""></td></tr>";
2) The actual image display must come from a second php script so that you can add an image header. This first chunk of code calls the script which displays the image (you can see that I've left out all body and table definitions here).
Code: Select all
if ($row = @ mysql_fetch_array($result))
{
echo "<img src="getthumb.php?thumb={$row["thumbid"]}">";
} // if got a rowCode: Select all
<?php
ob_start(); // turn output buffering on
// open the database and retrieve the record
$dbh=mysql_connect ("localhost",
"$user",
"$pass");
mysql_select_db("$db",$dbh);
$query = "SELECT thumbnail FROM thumbs
WHERE thumbid = $thumb";
$result = @ mysql_query ($query, $dbh);
$data = @mysql_fetch_array($result);
$image = $data["thumbnail"];
header("Content-Type: image/jpeg");
echo $image;
ob_end_flush();
?>http://www.herbhost.com/seasia/index.htm
Wayne your code seems similar to mine I just joined this place looking for help with an gallery I wrote so hopefully you will be able to help me solve my problem.
I am able to display the images from the thumbnail field in mydatabase not problem. The problem I am having is making the larger image show up when you click the thumbnail. When I click the thumbnail with the below code instead of the larger image it displays the thumbnail. here is the section of code im using in the artgallery.php that puts each thumbnail in a HTML TABLE
$loopvar= 0;
echo "<TABLE width = \"100%\" BORDER=5>";
echo "<TR>";
while ($result = mysql_fetch_array($query))
{
if ($loopvar % 3 == 0)
{
echo "</tr><td align = center>";
echo "<A HREF=\"view.php?file={$result["id"]}\"><IMG SRC=\"view.php?file={$result["id"]}\"></a>"."<br>";
echo $result['id']."<br>";
echo $result['title']."<br>";
echo $result['date']."<br>";
echo "</td>";
}
else
{
echo "<td align = center>";
echo "<A HREF=\"view.php?file={$result["id"]}\"><IMG SRC=\"view.php?file={$result["id"]}\"></a>"."<br>";
echo $result['id']."<br>";
echo $result['title']."<br>";
echo $result['date']."<br>";
echo "</td>";
}
$loopvar++;
}
echo "</tr></table>";
Then this is the code im using for the veiw.php
mysql_pconnect($hostname,$username,$password) or die("could not connect");
mysql_select_db("ART_GALLERY") or die("could not connect to database");
$result = mysql_query("SELECT mimeType, tn_mimeType, fileContents, tn_fileContents From art_gallery2 WHERE id=$file") or die("could not perform query");
$row = mysql_fetch_array($result);
if(!empty($row["tn_fileContents"]))
{
header("Content-type: {$row['tn_mimeType']}");
echo $row['tn_fileContents'];
}
else if (!empty ($row["fileContents"]))
{
header("Content-type: {$row['mimeType']}");
echo $row['fileContents'];
}
if anyone can help complete this task of making my thumbnails linked to the larger image work I would greatly appricate it or if you see something I should do diffrently let me know this is my first real PHP script. thanks again
I am able to display the images from the thumbnail field in mydatabase not problem. The problem I am having is making the larger image show up when you click the thumbnail. When I click the thumbnail with the below code instead of the larger image it displays the thumbnail. here is the section of code im using in the artgallery.php that puts each thumbnail in a HTML TABLE
$loopvar= 0;
echo "<TABLE width = \"100%\" BORDER=5>";
echo "<TR>";
while ($result = mysql_fetch_array($query))
{
if ($loopvar % 3 == 0)
{
echo "</tr><td align = center>";
echo "<A HREF=\"view.php?file={$result["id"]}\"><IMG SRC=\"view.php?file={$result["id"]}\"></a>"."<br>";
echo $result['id']."<br>";
echo $result['title']."<br>";
echo $result['date']."<br>";
echo "</td>";
}
else
{
echo "<td align = center>";
echo "<A HREF=\"view.php?file={$result["id"]}\"><IMG SRC=\"view.php?file={$result["id"]}\"></a>"."<br>";
echo $result['id']."<br>";
echo $result['title']."<br>";
echo $result['date']."<br>";
echo "</td>";
}
$loopvar++;
}
echo "</tr></table>";
Then this is the code im using for the veiw.php
mysql_pconnect($hostname,$username,$password) or die("could not connect");
mysql_select_db("ART_GALLERY") or die("could not connect to database");
$result = mysql_query("SELECT mimeType, tn_mimeType, fileContents, tn_fileContents From art_gallery2 WHERE id=$file") or die("could not perform query");
$row = mysql_fetch_array($result);
if(!empty($row["tn_fileContents"]))
{
header("Content-type: {$row['tn_mimeType']}");
echo $row['tn_fileContents'];
}
else if (!empty ($row["fileContents"]))
{
header("Content-type: {$row['mimeType']}");
echo $row['fileContents'];
}
if anyone can help complete this task of making my thumbnails linked to the larger image work I would greatly appricate it or if you see something I should do diffrently let me know this is my first real PHP script. thanks again
- Wayne Herbert
- Forum Commoner
- Posts: 34
- Joined: Tue Apr 29, 2003 3:13 pm
- Location: Houston, Texas
The problem is that the link on your thumbnail points back to the thumbnail display, not to a full photo display. Notice that in my code below, a different routine is called.
Code: Select all
echo "<a href="viewphoto.php?thumb={$row["thumbid"]}&folderid=$folderid&pageid=$pageid"><br>";
echo "<img src="getthumb.php?thumb={$row["thumbid"]}"></a><br>";
?>