How To Insert Images Into MySQL Database
Note: this is not a complete project. No support is offered.
Related scripts might provide some ideas to those of you who building your own File Upload Application, or wanting to insert images in a MySQL database table. These examples may be missing some certain (minor) functions that are part of the CMS from which these examples were extracted.
These scripts were extracted a CMS in the scenario that a user is logged in and wants to upload both image files and non-image files. An attempt is made to sort the incoming files in subdirectories located under a ./upl/ directory (based on MIME type and/or filename extension). And, optionally insert an uploaded image and/or image thumbnail in MySQL.
Part 1 of 5 - Part 2 of 5 - Part 3 of 5 - Part 4 of 5 - Part 5 of 5
<?php
// upload.func.php Functions for image upload application
if(!function_exists('abort')){
function abort($text = null, $js = null)
{
global $opts;
// Close out the HTML page gracefully if an error is encountered.
echo !empty($text) ? "\n".'<p>'.htmlentities($text).'</p>' : '';
if(isset($_FILES['toProcess']['tmp_name']) && count($_FILES['toProcess']['tmp_name']) > 0){
foreach($_FILES[toProcess][tmp_name] as $fn){
if(is_file($fn)){
if(unlink($fn)){
echo $opts['debug'] > 0 ? "\n".'<p>Deleting '.$fn.'</p>' : '';
}
}
}
}
echo !empty($js) ? "\n".'<a href="javascript:history.go(-1)" onmouseover="self.status=document.referrer;return true">Go Back</a>' : '';
echo "\n".'<p> </p></td></tr></table>';
do_footer();
exit;
};
}
function append_filename($filename, $str = '_')
{
global $opts;
if(empty($filename)){
abort('Filename not specified', 1);
}
$ext = strrchr($filename, '.');
return substr($filename, 0, -strlen($ext)).$str.$ext;
};
function rename_image_file($filename)
{
$ext = strrchr($filename, '.');
return substr($filename, 0, -strlen($ext)).'.OLD'.$ext;
};
function display_image_details($dir, $file, $mime_type, $comment)
{
global $opts;
// Executed upon successful upload
if(@is_readable($dir.'/'.$file)){
if(!@list($img_width, $img_height, $img_type_no, $img_src_attr) = getimagesize($dir.'/'.$file)){
abort('Cannot obtain the image properties for '.$dir.'/'.$file, 1);
}else{
echo "\n".'<p align="center"><img src="'.$opts['cfg']['dir_name'].$file.'" alt="'.htmlspecialchars($comment).'" border="1" width="'.$img_width.'" height="'.$img_height.'"></p>';
echo "\n".'<br><table align="center" border="1" cellpadding="3" cellspacing="0" width="100%">';
echo "\n".'<tr><td class="tiny" colspan="4">'.($opts['cfg']['expose_dir_name']>0?substr($dir,0,-1):'').'/'.$file.'</td></tr>';
echo "\n".'<tr><td class="tiny">width</td><td class="tiny">height</td><td class="tiny">attributes</td><td class="tiny">bytes</td></tr>';
echo "\n".'<tr>';
echo "\n".'<td class="tiny">'.$img_width.'</td>';
echo "\n".'<td class="tiny">'.$img_height.'</td>';
echo "\n".'<td class="tiny">'.$img_src_attr.'</td>';
echo "\n".'<td class="tiny">'.number_format(filesize($dir.$file)).'</td>';
echo "\n".'</tr>';
$html_tag = '<img src="'.$opts['cfg']['dir_name'].$file.'" border="0" width="'.$img_width.'" height="'.$img_height.'" alt="'.htmlspecialchars($comment).'">';
echo "\n".'<tr><td class="tiny" colspan="4">'.htmlspecialchars($html_tag).'</td></tr>';
echo $comment == '' ? '' : "\n".'<tr><td class="tiny" colspan="5">Notes: '.htmlspecialchars($comment).'</td></tr>';
echo "\n".'</table>';
echo "\n".'<p align="right" class="tiny"><a href="'.$opts['https_url'].'index.php?s=24" title="Upload Files" class="tiny">Upload Files</a></p>';
if(!execute_upld_log($comment, $mime_type, $opts['cfg']['dir_name'], $file, $html_tag, $opts['category'])){
$opts['error_msg'] .= 'Execution of execute_upld_log() failed'."\n";
}
}
}else{
abort('File is not readable', 1);
}
};
function execute_upld_log($comment, $mime_type, $dir, $file, $html_tag, $category)
{
global $opts, $ua;
// Log successful uploads into MySQL
if(@is_readable($dir.'/'.$file)){
if(stristr($mime_type, 'image')){
if(!@list($img_width, $img_height, $img_type_no, $img_src_attr) = getimagesize($dir.'/'.$file)){
abort('Cannot obtain the image properties for '.$dir.'/'.$file, 1);
}
}else{
$img_width = '';
$img_height = '';
$img_type_no = '';
$img_src_attr = '';
}
$dir = substr($dir, 0, -1);
$img_type_txt = $opts['upload_is_image'] == 1 ? $opts['image']['type_ary']["$img_type_no"] : $mime_type;
if(empty($opts['category'])){ $opts['category'] = 'other'; }
$qry = sprintf("INSERT INTO `vs_user_uploads`
(
`user_id`,
`user_name`,
`comment`,
`upld_time`,
`dir`,
`filename`,
`ext`,
`mime_type`,
`category`,
`html_tag`,
`img_width`,
`img_height`,
`img_type_no`,
`img_type_txt`,
`img_src_attr`,
`hidden`,
`deleted`
)
VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
addslashes($_SESSION['sess_user_id']),
addslashes($_SESSION['sess_user_name']),
addslashes($comment),
time(), // Should instead be a global variable set in the config file (multiple uploads may occur at different seconds)
addslashes($dir),
addslashes($file),
substr(strrchr($file, '.'), 1),
addslashes($mime_type),
addslashes($opts['category']),
addslashes($html_tag),
$img_width,
$img_height,
$img_type_no,
$img_type_txt,
$img_src_attr,
0,
0);
if(!$res = @mysql_query($qry)){
$opts['error_msg'] .= 'Insert into vs_user_uploads failed: '.$qry."\n";
return false;
}
return true;
}else{
return false;
}
};
function image2myql($dir, $file, $mime_type, $image_caption, $category)
{
// Optionally insert the uploaded image into MySQL
if(@is_readable($dir.'/'.$file)){
if(!@list($img_width, $img_height, $img_type_no, $img_src_attr) = getimagesize($dir.'/'.$file)){
abort('Cannot obtain the image properties for '.$dir.'/'.$file, 1);
}
if($data = addslashes(@fread(@fopen($dir.'/'.$file, "r"), @filesize($dir.'/'.$file)))){
if(empty($image_caption)){ $image_caption = $file; }
$qry = sprintf("INSERT INTO `vs_images1` (`user_id`, `category`, `filename`, `image`, `image_caption`, `image_width`, `image_height`, `image_type1`, `image_type2`, `img_attrib`)
VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
addslashes($_SESSION['sess_user_id']), $category, addslashes($file), $data, addslashes($image_caption), $img_width, $img_height, $img_type_no, $mime_type, $img_src_attr);
if(!$res = @mysql_query($qry)){
echo "\n".'<p>Insert query failed.</p>';
}else{
$id = @mysql_insert_id();
echo "\n".'<p class="red">MySQL Image ID #'.$id.' inserted into database. Do not re-load this page.';
echo "\n".'<br>'.htmlentities('<img src="getimage.php?id='.$id.'" alt="'.htmlspecialchars($image_caption).'" border="0" '.$img_src_attr.'>').'</p>';
}
fclose($dir.'/'.$file);
}else{
echo '<p>Cannot read image or insert into database.</p>';
}
}else{
abort('File is not readable', 1);
}
};
function filtered_filename($str)
{
// Rename the uploaded file to a Linux compatible filename.
// Replace accented characters with equivalents (hopefully!)
$str = strtr($str, "\xe1\xc1\xe0\xc0\xe2\xc2\xe4\xc4\xe3\xc3\xe5\xc5\xaa\xe7\xc7\xe9\xc9\xe8\xc8\xea\xca\xeb\xcb\xed\xcd\xec\xcc\xee\xce\xef\xcf\xf1\xd1\xf3\xd3\xf2\xd2\xf4\xd4\xf6\xd6\xf5\xd5\x8\xd8\xba\xf0\xfa\xda\xf9\xd9\xfb\xdb\xfc\xdc\xfd\xdd\xff\xe6\xc6\xdf\xf8", "aAaAaAaAaAaAacCeEeEeEeEiIiIiIiInNoOoOoOoOoOoOoouUuUuUuUyYyaAso");
// Force lower string
$str = trim(strtolower($str));
// Replace multiple spaces with one space
$str = ereg_replace(' +', ' ', $str);
// Replace spaces with underscore
$str = str_replace(' ', '_', $str);
// Replace hyphens with underscore
$str = str_replace('-', '_', $str);
// In case there was a space/hyphen next to an underscore
$str = ereg_replace('_+', '_', $str);
return trim(preg_replace('/[^[:lower:][:digit:]_\.]/', '', $str));
};
?>