storing graphics in mySQL

[pdstein]

I've been tinkering with phpAds and it gives the capability to store ad graphics as blobs in mySQL.[nbsp][nbsp]Seems to me that serving ads from mySQL rather than the filesystem would require additional resources.[nbsp][nbsp]Is this true?

[Terra]

IMHO, yes and no

FileSystem: Depends if the image is cached in memory or not from a previous recent access...[nbsp][nbsp]The caching is granular to the individual file level...

MySQL: Depends on how well your table is optimized for quick delivery via indexes...[nbsp][nbsp]Caching also helps here as the memory regions are cached which may increase the hit factor for a particular group/cluster of adds...

I don't have any hard numbers, but if there was a choice between CGI/File and PHP/MySQL - I would go the PHP way as this has less impact on the server...

There are a lot of syscalls that must happen in either case, but the avoidance of CGI forking is always a plus...

--
Terra
--Deductive-- :)
FutureQuest

[Justin]

I've never much cared for the idea of storing images in a database, but I know it can be done easily (must escape any single quotes, as well as any high ascii character I think) - my ad software uses PHP/MySQL to store each ad's properties, clicks, impressions etc - but the images are standard GIF files, with numbers for filenames, which corresponds to the ID field (unique key, auto_increment)...

I suppose either way works - end result, if you are serving banner ads, storing the image data in a database would require an additional script call (one to output the HTML code to the browser, one to output the actual image data - the two cannot be done in the same step), where mine is one script call to generate the HTML, the image being called directly by the browser...

Hope this helps.

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[MrBlunt]

I have been running phpAds using MySQL blobs and have not noticed any server issues. Considering that since the new year (uhh millenium) I redesigned my site to be almost all PHP and there are MANY script functions for every page it is very fast. I still have some Perl lying around, but as I get to it I am converting as much as possible. I will miss you you ol' camel :(

Also if you don't have it, get phpAdmin. Its the best MySQL admin program I've seen. I use it on FutureQuest and my WIndows 98 Machine. With PHP and MySQL on both it is easy to develop and then transfer with only minor changes for some paths.

www.mrblunt.com (http://www.mrblunt.com)

p.s. If you develop locally I would recommend OmniHTTPD as a server. Its easy to configure with a Windows GUI and less hassle than Apache or the dreaded MS servers(sic).

[pdstein]

storing the image data in a database would require an additional script call (one to output the HTML code to the browser, one to output the actual image data - the two cannot be done in the same step), where mine is one script call to generate the HTML, the image being called directly by the browser...
You're right Justin, when the image is stored in the database, a second script replaces the opening of the image file directly.

1) Image stored as file
<A HREF=&quot;clickReferalScript.php&quot;>
<IMG SRC=&quot;banner.gif&quot;>
</A>

2) Image stored in database
<A HREF=&quot;clickReferalScript.php&quot;>
<IMG SRC=&quot;showBanner.php&quot;>
</A>

Where showBanner.php looks like:
<?
// query database for $banner and $bannerFormat
Header( &quot;Content-type:[nbsp][nbsp]image/$bannerFormat&quot;);
echo $banner;
?>

It seems to me that since showBanner.php has to connect to the database and pull the data from a table that it would be less efficient in terms of both speed and resources than simply accessing the banner as a file.[nbsp][nbsp]I don't really know though.[nbsp][nbsp]I'm just curious if it will make any difference.

- Paul

[Justin]

Which is better depends on what you are doing - let's say you wanted to display random (or rotating or just different) images, using a static IMG tag:

<img src=&quot;image.cgi&quot;>

-- Or --

<img src=&quot;image.php&quot;>

I think what TeRRa was refering to above was a CGI, which opens a GIF file and outputs the results, rather than static GIF files - eg, he was comparing using CGI to load (via file) and display the GIF as opposed to using PHP to load (via MySQL) and display the GIF.

But if you are doing what I'm doing, where the HTML must be varied, I think it's best to load the images from files - for one, the GIF files would be easily cachable, helping to reduce bandwidth/load time. Plus it should be a lot lighter on Apache...

The only time I could see using a script as the IMG SRC would be if you need to manipulate the image in some way (counters, for example) or if you need to rotate out graphics and have no choice but to use static HTML (calling the image from a different server, perhaps).

I can envision a counter system where each style of counter digits is stored in a database (as well as the actual count data) - and I suspect this would be easier on the system than using flat text files and image files... but this is of course quite elaborate for a counter program :)

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[SneakyDave]

This is an old topic, but I was searching for comparisons between images actually stored in a database and situations where the image information and name is only stored, and naturally, I looked here first.

I wrote an image hosting script for auctions (not on FQ, btw!) which strictly uses PHP/SQL for members to administer, and the images are stored as files separately. They can sort the images a number of ways, add a &quot;friendly name&quot;, etc. They don't even see the image unless they click on the link which takes them to the actual URL, so its all database driven. The member's auction just links to the image URL, no PHP/SQL interaction is involved there. I just couldn't see thousands of visitors executing a PHP/MYSQL script to view an image. It seems to have worked out pretty well, there are over 60 members, and over 1200 images. (I'll post again if MySQL poops the bed!)

There are some synchronization issues to deal with between the image directory and the database, but those are pretty minor things to deal with.[nbsp]
[This message has been edited by SneakyDave (edited 12-01-00@10:22 am)]