Images In MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Rob Watton
Forum Newbie
Posts: 4
Joined: Wed Mar 19, 2003 5:22 am
Location: Staffordshie, UK

Images In MySQL

Post by Rob Watton »

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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Generally it is better to store the path to an image in MySQL and store the image itself in a directory as normal.

If you search this forum for 'blob' and 'image' you'll get a bunch of threads on the same question.

Mac
Rob Watton
Forum Newbie
Posts: 4
Joined: Wed Mar 19, 2003 5:22 am
Location: Staffordshie, UK

Post by Rob Watton »

Thanks for the reply.

I have since searched the forum and found some articles about storing images into MySQL.

Sorry for the double posting.
superwormy
Forum Commoner
Posts: 67
Joined: Fri Oct 04, 2002 9:25 am
Location: CT

Post by superwormy »

The MySQL manual actually reccomends NOT storing images in MySQL... so you know...

It's better to store the filename, and get it later.
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

AW:

Post by bluenote »

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.
faerieprince
Forum Newbie
Posts: 1
Joined: Mon May 19, 2003 4:05 pm
Location: omaha, ne

Twigletmac: do you think you could help?

Post by faerieprince »

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!
User avatar
Wayne Herbert
Forum Commoner
Posts: 34
Joined: Tue Apr 29, 2003 3:13 pm
Location: Houston, Texas

Post by Wayne Herbert »

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/
Last edited by Wayne Herbert on Mon May 19, 2003 7:20 pm, edited 1 time in total.
User avatar
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?

Post by Wayne Herbert »

faerieprince wrote:i am new to php and mysql.

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>";
thanks!
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).

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 row
getthumb.php looks like the code below. Note that if you have ANY lines, spaces... anything... outside of the php code, a header type of text will be issued before you issue your image header and you will get a big red X.

Code: 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();
?>
The full scripts drive this simple image gallery below. Email me if you want the whole thing.

http://www.herbhost.com/seasia/index.htm
CStrauss
Forum Newbie
Posts: 1
Joined: Wed May 28, 2003 3:31 pm
Location: United States

Post by CStrauss »

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
User avatar
Wayne Herbert
Forum Commoner
Posts: 34
Joined: Tue Apr 29, 2003 3:13 pm
Location: Houston, Texas

Post by Wayne Herbert »

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>";

?>
Post Reply