将图片存入mysql

Saving   Images   in   MySQL  

Sometimes,   it 's   more   convenient   to   save   images   in   a   database   than   as   files.  
MySQL   and   PHP   make   it   very   easy   to   do   this.   In   this   article,   I   will   describe  
how   to   save   images   in   a   MySQL   database   and   display   them   later   on.  

    Setting   up   the   database  


The   difference   between   any   regular   text   or   integer   fields   and   a   field   that  
needs   to   save   an   image   is   the   amount   of   data   that   is   needed   to   be   held   in   the  
field.   MySQL   uses   special   fields   to   hold   large   amounts   of   data.   These   fields  
are   known   as   blobs   (blob).  

Here   is   the   BLOB   definition   from   the   MySQL   site   :  

A   BLOB   is   a   binary   large   object   that   can   hold   a   variable   amount   of   data.   The  
four   BLOB   types   TINYBLOB,   BLOB,   MEDIUMBLOB   and   LONGBLOB   differ   only   in   the  
maximum   length   of   the   values   they   can   hold  


For   more   information   about   MySQL   BLOBs   check   out   http://www.mysql.net/Manual_c
hapter/manual_Reference.html#BLOB

Use   the   next   syntax   to   create   a   basic   table   that   will   hold   the   images:

CREATE   TABLE   Images   (
PicNum   int   NOT   NULL   AUTO_INCREMENT   PRIMARY   KEY,
Image   BLOB
);
 
    Setting   the   upload   script  


An   example   of   a   file   upload   front   end   can   be   seen   at   File   Uploading   by   berber  
(29/06/99).   What   we   need   now   is   the   PHP   script   that   will   get   the   file   and  
insert   it   into   MySQL.   The   next   script   does   just   that.   In   the   script,   I 'm  
assuming   that   the   name   of   the   file   field   is   "Picture ".  


<?
If($Picture   !=   "none ")   {
$PSize   =   filesize($Picture);
$mysqlPicture   =   addslashes(fread(fopen($Picture,   "r "),   $PSize));
unlink($Picture);
mysql_connect($host,$username,$password)  
or   die( "Unable   to   connect   to   SQL   server ");
@mysql_select_db($db)  
or   die( "Unable   to   select   database ");
mysql_query( "INSERT   INTO   Images   (Image)   VALUES   '($mysqlPicture ') ")
or   die( "Can 't   Perform   Query ");
}
else   {
echo "You   did   not   upload   any   picture ";
}
?>

This   is   all   that   is   needed   to   enter   the   image   into   the   database.   Note   that   in  
some   cases   you   might   get   an   error   when   you   try   to   insert   the   image   into  
MySQL.   In   such   a   case   you   should   check   the   maximum   packet   size   allowed   by  
your   MySQL   ver.   It   might   be   too   small   and   you   will   see   an   error   about   this   in  
the   MySQL   error   log.  

What   we   did   in   the   above   file   is   :

1.   Check   if   a   file   was   uploaded   with   If($Picture   !=   "none ").
2.   addslashes()   to   the   picture   stream   to   avoide   errors   in   MySQL.
3.   Delete   the   temporary   file.
3.   Connect   to   MySQL,   choose   the   database   and   insert   the   image.


 
    Displaying   the   Images  
   

Now   that   we   know   how   to   get   the   images   into   the   database   we   need   to   figure  
out   how   to   get   them   out   and   display   them.   This   is   more   complicated   than  
getting   them   in   but   if   you   follow   these   steps   you   will   have   this   up   and  
running   in   no   time.  

Since   showing   a   picture   requires   a   header   to   be   sent,   we   seem   to   be   in   an  
impossible   situation   in   which   we   can   only   show   one   picture   and   than   we   can 't  
show   anymore   Since   once   the   headers   are   sent   we   can 't   send   any   more   headers.  


This   is   the   tricky   part.   To   outsmart   the   system   we   use   two   files.   The   first  
file   is   the   HTML   template   that   knows   where   we   want   to   display   the   image(s).  
It 's   a   regular   PHP   file,   which   builds   the   HTML   that   contains   the   <IMG>   tags,  
as   we   want   to   display   them.   The   second   file   is   called   to   provide   the   actual  
file   stream   from   the   database   directly   into   the   SRC   property   of   the   <IMG>  
tag.  

This   is   how   a   simple   script   of   the   first   type   should   look   like:  


<HTML>
<BODY>
<?
mysql_connect($host,$username,$password)  
or   die( "Unable   to   connect   to   SQL   server ");
@mysql_select_db($db)  
or   die( "Unable   to   select   database ");
mysql_query( "SELECT   *   FROM   Images ")
or   die( "Can 't   Perform   Query ");
While($row=mysql_fetch_object($result))   {
echo   " <IMG   SRC=/ "SecondType.php3?PicNum=$row-> PicNum/ "> ";
}
?>
</BODY>
</HTML>


While   the   HTML   is   being   displayed,   the   SecondType.php3   file   is   called   for  
each   image   we   want   to   display.   The   script   is   called   with   the   Picture   ID  
(PicNum)   which   allows   us   to   fetch   the   image   and   display   it.  

The   SecondType.php3   file   looks   like   this   :  


<?
$result=mysql_query( "SELECT   *   FROM   Images   WHERE   PicNum=$PicNum ")  
or   die( "Can 't   perform   Query ");
$row=mysql_fetch_object($result);
Header(   "Content-type:   image/gif ");
echo   $row-> Image;
?>


This   is   the   whole   theory   behind   images   and   MySQL.   The   scripts   in   this   example  
are   the   basics.   You   can   now   enhance   these   scripts   to   include   thumbnails,   set  
the   images   in   various   positions,   enhance   the   database   table   to   hold   an   ALT  
field,   Check   the   width   and   height   of   the   images   before   you   insert   them   into  
the   database   and   keep   that   data   in   the   table   too   etc...