Php > Databases

Upload image file to MySQL as BLOB

Upload image file to MySQL as BLOB

a tutorial to upload an image file to MySQL as BLOB data. 
u need 3 files: 

- test_imagedb.php = form to upload 
- test_imagedb_create.php = retrieve image from db 
- test_imagedb_view.php = yeah.. view it.. what else? :D 

but before we continue, change your setting on "php.ini" and "my.ini" to accept large image files. 

php.ini 
------ 
upload_max_filesize = 4M 
------ 

my.ini 
------ 
[mysqld] 
set-variable=key_buffer=16M 
set-variable=max_allowed_packet=16M 
------ 

set those directives above to any value that you want. now, we can continue.. 

mysql dump 
------ 
# 
# Table structure for table 'tblimage' 
# 

CREATE TABLE `tblimage` ( 
  `imgid` int(3) unsigned NOT NULL auto_increment, 
  `imgtype` varchar(16) NOT NULL default '', 
  `imgdata` mediumblob, 
  PRIMARY KEY  (`imgid`) 
) TYPE=MyISAM; 
------ 

test_imagedb.php 
------ 
<body> 
<? 
if (!isset($_REQUEST["submit"])) { 
?> 
<form method="POST" action="<?= $_SERVER["PHP_SELF"] ?>" enctype="application/x-www-form- 
urlencoded"> 
<table> 
<tr><td>Type</td><td><select name="imgtype"><option value="image/gif">GIF</option><option 
value="image/jpeg">JPEG</option></select></td></tr> 
<tr><td>File</td><td><input type="file" name="imgfile"></td></tr> 
<tr><td></td><td><input type="submit" name="submit" value="upload"><input type="reset"></td></tr> 
</table> 
</form> 
<? 
//-- save image to db -- 
} else { 
  /* 
  the code below is a suggestion from California Strong... 
  */ 
  $hndl=fopen($_REQUEST["imgfile"],"r"); 
  $isize=sizeof($_REQUEST["imgfile"]); 

  $imgdata=""; 
  while(!feof($hndl)){ 
    $imgdata.=fread($hndl,$isize); 
  }; 
     
  /* 
  my code was... 
   
  $hndl=fopen($_REQUEST["imgfile"],"r"); 
  $imgdata=fread($hndl,filesize($_REQUEST["imgfile"])); 
  */ 
   
  $imgdata=addslashes($imgdata); 

  $dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable"); 
  @mysql_select_db($dbname,$dbconn) or exit("DB Unavailable"); 

  $sql = "INSERT INTO tblimage VALUES(NULL,'". $_REQUEST["imgtype"] ."','". $imgdata ."')"; 

  @mysql_query($sql,$dbconn) or exit("QUERY FAILED!"); 

  mysql_close($dbconn); 

  fclose($hndl); 

  echo "<a href=\"test_imagedb_view.php\">view image</a>"; 
}; 
?> 
</body> 
------ 

test_imagedb_create.php 
------ 
<? 
$dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable"); 
@mysql_select_db($dbname,$dbconn) or exit("DB Unavailable"); 

$sql = "SELECT imgtype,imgdata FROM tblimage WHERE imgid=". $_GET["imgid"]; 

$result = @mysql_query($sql,$dbconn) or exit("QUERY FAILED!"); 

$contenttype = @mysql_result($result,0,"imgtype"); 
$image = @mysql_result($result,0,"imgdata"); 

header("Content-type: $contenttype"); 
echo $image; 

mysql_close($dbconn); 
?> 
------ 

test_imagedb_view.php 
------ 
<body> 
<? 
$dbconn = @mysql_connect($dbserver,$dbuser,$dbpass) or exit("SERVER Unavailable"); 
@mysql_select_db($dbname,$dbconn) or exit("DB Unavailable"); 

$sql = "SELECT imgid,imgtype FROM tblimage ORDER BY imgid"; 

$result = @mysql_query($sql,$dbconn) or exit("QUERY FAILED!"); 

echo "<table border=1>\n"; 
echo "<tr><th>imgid</th><th>imgtype</th><th>imgdata</th></tr>\n"; 
while ($rs=mysql_fetch_array($result)) { 
  echo "<tr><td>".$rs[0]."</td>"; 
  echo "<td>".$rs[1]."</td>"; 
  echo "<td><img src=\"test_imagedb_create.php?imgid=".$rs[0]."\"></td></tr>\n"; 
}; 
echo "</table>\n"; 

mysql_close($dbconn); 
?> 
</body>