Using what type to store a image into MySQL ?

hoanghp2000

I have heard that PHP can read and write a image into MySQL database. But I am not able to do that .
Please tell me how to solve this
Thank so much

bastien

store the image in a file and store the path to the image in the db as text....better performace, no db bloat

easier to code/use/maintain

hth

hoanghp2000

Hi all

I agree with your solution but it's not well when you want to back up the data of your Website.
I've read in the MSSQL documents about storing image data in database but I can not do it.
Who ever do that please help me to solve this problem ?
I ONLY WANT TO KNOW HOW TO DO IN THIS CASE

Thanks so much

mark_w_ryan

I need to do this to. I know it is easier and faster if the image is not in the data base But I have a application where it would be better if the image is stored im the db.

So can anyone answer the original question? "How can you display an image that is stored in mysql?"

Thanks

stolzyboy

just curious, but why would it be better if it were in the database rather than a path to the file in the directory structure

BeastRider

I did not load images into my database but rather converted a ThumbsPlus database into MySql. It already has thumbnails in it.

If you wish I will post the code for getting a thumbnail out of a database and displaying it on a web page. I know this code works because it runs this website.

http://www.herbhost.com/seasia/index.htm

I can also post code as to how to put an image into a database but it is not mine and not tested... it would be best regarded as a template for your own code.

As to the question as to whether images in a database are good or bad: It depends. Talking about "database bloat" is silly... you take no more space in a directory than if you load up all the individual image files. A database with big records is not slower than a database with small records.

Also, some say that images in a database are less efficient because you have to do a second query to retrieve the image in such a way as to put a content header in front of it. This is true, but note that you also have to load a file to display an image... the timings difference between a SELECT for a single row and reading a file are minimal.

There is one thing though that really does make a difference with respect to images in a database, and that is the frequency of update. Blob fields are not stored directly in the database. Instead, there is a relative file associated with the database. In the database, the blob field refererence actually is the relative address in the blob file where to get the blob data. One need only do a seek to that location.

If blobs/images are regularly updated, new/updated blob data is written at the end of the relative file... it never replaces existing blob data... so updates cause this file to expand. It needs to be packed to restore it to its original size. Also, some db's have trouble with blobs.

So, I successfully store thousands of megabyte size images in a database (310 megs) with no problems, but this is a static database. I'd be less willing to do this if updated daily.

mark_w_ryan

Yes, Please post the code. It would greatly be appreciated.

Mark

stolzyboy

beast, you are very incorrect, it is very different when you are grabbing a blob from a database, rather than from a directory, even mysql.com says not to store images in the database, it creates slower indexing, selecting, everything around is slower, and you probably won't notice the difference if you are talking a few hundred to a few thousand records, but you will notice the difference when you start hittting the tens and hundreds of thousands of images stored in the database

trust me - i went down the store images in db route once and i will never do it again

storing binary file into a database is much more easy to corrupt and damage the db, than storing in the directory structure

BeastRider

Originally posted by stolzyboy
beast, you are very incorrect, it is very different when you are grabbing a blob from a database, rather than from a directory, even mysql.com says not to store images in the database, it creates slower indexing, selecting, everything around is slower,

Not trying to get into a p*ssing contest, but I'd like to see justification for this statement. The BLOB is not contained in the database record itself. The record contains a pointer into the .MYD relative access file. Thus, if you put a 5000 byte image into the BLOB field, MySql will show a record size greater than 5000 bytes but the reality is that the record itself holds only a pointer... the data is in another file.

Now, I can see that if you you have large images, and do a SELECT that returns many rows, you can end up with a huge pool of image data in memory... unless of course, the image data is not actually returned until the field is assigned to a variable... this I do not know.

But for smaller images (ie - thumbnails, for example), or for applications that that return only a few records per query, I don't see the BLOB problem as being a problem at all. As I noted previously, I use a very large DB with 1000's of megabyte size blobs in it without problem, but then again, I retrieve one or two records at a time.

I looked through the MySql website and could not find references to image storage on the site. I'd appreciate a link. I'd also appreciate any information/links that describe what actually happens to blobs in a query in MySql... is a row returned with only a pointer to the blob data, or is the blob data returned as well?

Thanks.

BeastRider

I think I have to correct myself about storage methods for blobs in a MySql database... it looks like blobs are stored in the main data file and not externally as in other databases. MEA CULPA.

However, I think my comments regarding blob size and number of records retrieved still hold true. Small blob sizes and/or a small number of returned records will create no problems.

I'd still like references to the blob problem at mysql.com though. Can't find any.

Thanks.

BeastRider

While I'm talking to myself and apologizing, I would add that making a lot of changes to blob fields would result in a greatly expanded database that would have to compacted regularly, if the blobs are stored in the data records. Since a variable length record is not typically written over when updated, indexes need to be updated as well.

So, perhaps the right answer about images in databases is:

If you have a small number of records, or an intermediate to large number of records and small blob fields, and you don't regularly update, then images in a db is OK... otherwise unwanted problems will crop up.

And, as a practical matter, one does have to ask "why put images in a database"? For me, I'm using a database derivative for my website that came from an app that puts thumbnails in the db. But, the acutal photos are not in the DB... it really is easier just to upload them en masse.

The other image DB app I have (the 310 meg db) I created to be able to encyrpt images. I don't mind people displaying my images with my software, but I don't want them to copy them wholesale for use in other software. Hence, a database is a good way to hide the images.

stolzyboy

ummm, well i am glad you are understanding what i am talking about anyway, and yes a few record select insert update shouldn't be a problem, but when there are large numbers of records to be displaying, etc....a blob is not the way to go

anyway, i can't find that link i was talking about, mysql's manual is kinda crappy, as well as their search, i used to have it bookmarked, but recently reformatted HD and lost all that kind of stuff, if i ever run into it again, i will be sure to let you know

stolzyboy

just noticed this as i was looking for that link still, not the one i was looking for, but a little explanation as to why a blob isn't the greatest idea

Each index file is opened once and the datafile is opened once for each concurrently running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * n is allocated (where n is the maximum row length, not counting BLOB columns). A BLOB uses 5 to 8 bytes plus the length of the BLOB data. The ISAM/MyISAM storage engines will use one extra row buffer for internal usage.

For each table having BLOB columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, a buffer as large as the largest BLOB value is allocated.

this was from here

and was talking about memory used by mysql

stolzyboy

well come to find that this

http://www.mysql.com/doc/en/Tips.html

is the page i was looking for, but the wording has changed from specifically talking about the blob problem, but it gives some information as why not to use blob at all, sorry if i cause you grief, but there used to be a better explanation from them

BeastRider

You cause no grief at all. I am a firm believer in operating out of reality. That is why I am generally unwilling to take unsunstantiated statements at face value, especially statements that contain "never" in them.

As with many things technical, there is often no black and white answer... only the best case for a given set of conditions. For example, I have created databases with more than a million records, but I wouldn't consider even trying this in Access, no matter what the field types. Your links and knowledge help me determine the conditions and the solutions. Thanks.

Cogen

I currently am working on a site that is storing images in a MySQL database.

Most of the images are not in the database, but we have created an inventory system that stores all of the product images in the database. After reading this thread, I'm starting to think this wasn't a great idea, but It's not that big of a system yet so I'm not too worried.

Any way, the reason we are putting images into the database instead of uploading them to the file system is because we were told that there can be many issues with file permissions if you try to port the code to a new server (hosting company). I'm not sure if that's very accurate, but that's what I was told any way.

BeastRider

I would worry about it too much. After reading the two links that stolzyboy posted:

http://www.mysql.com/doc/en/Tips.html

http://www.mysql.com/doc/en/Memory_use.html

Your app should only be of concern IF

a) you do a lot of updates. Blob (and varchar) fields force rows to be of variable length. Updates to variable length records never get put back in their original spot, they get added onto the end of the database. This increases database size and decreases index efficiency as the index entries must be split to point to the new location(s). This gets cleaned up when the database is compacted and reindexed.

b) you select a lot of records all at once. If you do, you may bust maximum memory allowances for your app.

But, with infrequent updates, and a limit to the number of rows selected, you can probably run the app for a long time.

mark_w_ryan

So the question still remains. Good, bad or ugly: How do you load image into mysql and how do you display them using PHP?
I just need some code fragment that can show me how it is done.

Thanks 🙂

Mark

stolzyboy

try this, i haven't actually used it, but i found it doing a yahoo search

good luck

BeastRider

OK... The following scripts display a table of thumbnails, then display a single photo when a thumbnail is clicked. I know they work since they drive this website.

http://www.herbhost.com/seasia/index.htm

thumbview.php produces the table of thumbnails.
getthumb.php retrieves the thumbnail image from the database for display on the thumnail page
viewphoto.php displays a photo when the link is clicked. The full size photos are NOT stored in the database.

Of miscellaneous note: I've got background images, etc. in this script. You will need to edit to work on your system.

I've had to split this script into two parts to get under the 10,000 character limit on this system. Here is the first half.

thumbview.php - display thumbnails

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<?php
// this script displays thumbnail images from a mysql database which
// is essentially a copy of a ThumbsPlus Access database.  A second
// table (pagedata) is used for heading descriptions for each folder

// id this page for future reference
$foldermax = 15; // largest folder number of photos

// $folderid is the primary selection key - hanoi, saigon, etc.
// $pageid is the page number to print for mult pages in a folder

// clean up input
$pageid = substr($pageid, 0, 2);
$pageid = EscapeShellCmd($pageid);

$folderid = substr($folderid, 0, 2);
$folderid = EscapeShellCmd($folderid);

// build query to get folder data
if ($folderid == 1)
  {
  $skipfolders = 0;
  $foldercount = 2;
  }
else if ($folderid == $foldermax)
  {
  $skipfolders = $folderid - 2;
  $foldercount = 2;
  }
else
  {
  $skipfolders = $folderid - 2;
  $foldercount = 3;
  }
$folderquery = "SELECT * FROM pagedata
            LIMIT $skipfolders, $foldercount";

// build query for list of thumbnails up to 37 max
$skiprows = (($pageid - 1) * 28); // number of thumbs to skip in query
$query = "SELECT thumbid, annotation, filename FROM thumbs
            WHERE pageno = $folderid
            ORDER BY filename
            LIMIT $skiprows, 37";

// connect to the database
if(!($dbh=mysql_connect ("localhost",
                         "$username",
                         "$pass")))
  die("Cannot connect to the database");

// open the connected database
if (!mysql_select_db("$database",$dbh))
  die("Cannot open the database");

// get the folder information for page descriptions and save what we need
// for prior, current, and next folder
if (!($folderresult = @ mysql_query ($folderquery, $dbh)))
  die("Cannot execute query against the database - pagedata");

// 1st record is old data for all but first folder
$folderrow = @ mysql_fetch_array($folderresult);
if ($folderid == 1)
  {
  $titlestr = $folderrow["title"];
  $imagefile1 = $folderrow["image1"];
  $imagefile2 = $folderrow["image2"];
  $imagefile3 = $folderrow["image3"];
  $pagetext = $folderrow["pagetext"];
  $pagedesc = $folderrow["description"];
  }
else
  {
  $olddesc = $folderrow["description"];
  }

// 2nd record is current data for all but first folder
$folderrow = @ mysql_fetch_array($folderresult);
if ($folderid == 1)
  {
  $nextdesc = $folderrow["description"];
  }
else
  {
  $titlestr = $folderrow["title"];
  $imagefile1 = $folderrow["image1"];
  $imagefile2 = $folderrow["image2"];
  $imagefile3 = $folderrow["image3"];
  $pagetext = $folderrow["pagetext"];
  $pagedesc = $folderrow["description"];
  }

// third record exists for all but first and is next folder data
if ($folderrow = @ mysql_fetch_array($folderresult))
  $nextdesc = $folderrow["description"];

// return the rows for this page - if the rows are less than 37 we
// either have only one page or the last page of a query.  if 37 or
// greater we know we have at least 3 rows for a second page, using
// 28 rows per page... doing this way stops single row orphan pages

if (!($result = @ mysql_query ($query, $dbh)))
  die("Cannot execute query against the database - thumbs");

// if we got some rows back then get some numbers and output page
$numrows = mysql_num_rows($result);
if ($numrows > 0)
  {
  if ($numrows < 37) // put all thumbs on this page
    {
    $tablerows = floor($numrows / 4);
    if (($numrows % 4) > 0)
      $tablerows = $tablerows + 1;
    $newpageid = $pageid;
    }
  else // more pages to come - 28 per page
    {
    $tablerows = 7;
    $newpageid = $pageid + 1;
    }
  echo "<title>$titlestr</title>"; // put out the title
?>

</head>
<body background="Paper08.jpg" link=saddlebrown vlink=black alink=gray>
<font face="Arial"><font size="2"></font></font>
<center>

<?php
  // put out the images that make the header
  if ($imagefile1 != "X")
    echo "<img SRC=\"$imagefile1\"><br>";
  if ($imagefile2 != "X")
    echo "<img SRC=\"$imagefile2\"><br>";
  if ($imagefile3 != "X")
    echo "<img SRC=\"$imagefile3\"><br>";

  // put out the folder descriptive text on first page only
  if ($pageid == 1)
    echo "</center><br><p>$pagetext</p><center>";

  // put out the page description and page number unless only
  // one page, then just page description
  echo "<h3>";
  if (($pageid == 1) and ($newpageid == $pageid))
    {
    echo"$pagedesc Photos Below";
    }
  else
    {
    echo "$pagedesc Photos - Page $pageid Below";
    }
  echo "</h3>";

  // put out some top of page links to other pages
?>
<h5>Click on Any Photo for the Larger Version</h5>
<table BORDER="0" WIDTH="100%">
<tr>

<?php
  // if not in the first folder, make link back to previous
  $colcnt = 0;
  if ($folderid > 1)
    {
    $colcnt += 1;
    $oldfolderid = $folderid - 1;
    echo "<td WIDTH=\"25%\"><h4><a href=\"thumbview.php?folderid=$oldfolderid&pageid=1\">";
    echo "Back to the $olddesc Photos</a></h4></td>";
    }

  // if not on the first page, make link to previous page
  if ($pageid > 1)
    {
    $colcnt += 1;
    $oldpageid = $pageid - 1;
    echo "<td WIDTH=\"25%\"><h4><a href=\"thumbview.php?folderid=$folderid&pageid=$oldpageid\">";
    echo "$pagedesc Photos - Page $oldpageid</a></h4></td>";
    }

  // if not the last page, make link to next page in folder
  if ($newpageid != $pageid) // more than one page
    {
    $colcnt += 1;
    echo "<td WIDTH=\"25%\"><h4><a href=\"thumbview.php?folderid=$folderid&pageid=$newpageid\">";
    echo "$pagedesc Photos - Page $newpageid</a></h4></td>";
    }

  // now if it is not the last folder make a link to the next folder
  if ($folderid < $foldermax)
    {
    $colcnt += 1;
    $newfolderid = $folderid + 1;
    echo "<td WIDTH=\"25%\"><h4><a href=\"thumbview.php?folderid=$newfolderid&pageid=1\">";
    echo "Go to the $nextdesc Photos</a></h4></td>";
    }

  while ($colcnt < 4)
    {
    echo "<td WIDTH=\"25%\"><br></td>";
    $colcnt += 1;
    }
?>

The remainder of the code is in the next post... the guts of the thumbnail display.