Adding a filename to a MySql database
Hi there, i have been trying to figure out how to add the filename of an uploaded image to Mysql database for an image voting system i am building. The php i use converts the filename to a number and then uploads it to a specified folder. The database structure looks like this:
db name: eval
db table: image_voting_pictures
Fields: id, creator, file, comment, points, votes
Currently the 'file' field is set to VARCHAR and no data is inserted. Im aware that BLOB can be used but i want to add the fiename to the database rather than the actual file.
The php code to upload the file is below:
<?php
//load configuration
require("config.php");
//connect to database
@mysql_connect($db_server,$db_user,$db_password) or die("Database server connection failed. Check variables \$db_server, \$db_user and \$db_password in config.php");
@mysql_select_db($db_name) or die("Selecting database failed. Check variable \$db_name in config.php");
//print header
echo $header;
//print html
?><form action="create_new.php" enctype="multipart/form-data" method="post">
<table border="0" cellpadding="0" cellspacing="15" width="<?php echo $img_width; ?>">
<tr>
<td colspan="2"><?php
//upload image (form submitted)
if(isset($_POST["submit"])){
$file = $_FILES["file"];
$name = $_POST["name"];
$email = $_POST["email"];
$comment = $_POST["comment"];
$error = false;
$error_file = false;
//check name
if(strlen($name) < 3){
$name = "<b>At least three characters.</b>";
$error = true;
}
//check email
if($email && !ereg('^[-^!#$%&\'*+\/=?`{|}~._a-z0-9]+@([-a-z0-9]+(\.[-a-z0-9]+)*\.[-a-z0-9]{2,6}|\[[0-9]{1,3}(\.[0-9]{1,3}){3}\])$',$email)){
$email = "<b>Must be valid.</b>";
$error = true;
}
//check file
if($file == ""){
$file = "<b>No file chosen.</b>";
$error_file = true;
$error = true;
}
//check comment
if(strlen($comment) < 3){
$comment = "<b>At least three characters.</b>";
$error = true;
}
//file uploaded
if(!$error_file){
$file_info = getimagesize($file["tmp_name"]);
$file_size = filesize($file["tmp_name"]);
//check image width
if($file_info[0] > $img_width){
$message_width = "<b>{$file_info[0]} - image is ".($file_info[0] - $img_width)." pixels too broad.</b>";
$error = true;
}else{
$message_width = $file_info[0]." pixels - ok";
}
//check image height
if($file_info[1] > $img_height){
$message_height = "<b>{$file_info[1]} - image is ".($file_info[1] - $img_height)." pixels too high.</b>";
$error = true;
}else{
$message_height = $file_info[1]." pixels - ok";
}
//check image format
// if($file_info[2] != $img_format){
// $message_type = "<b>Image format must be $img_format_info.</b>";
// $error = true;
// }else{
// $message_type = $img_format_info." - ok";
// }
//check image size
if($file_size > $img_size){
$message_size = "<b>".number_format($file_size/1024,0,",","")." - filesize is ".number_format(($file_size - $img_size) / 1024,0,",","")."KB too large.</b>";
$error = true;
}else{
$message_size = number_format($file_size/1024,0,",","")."KB - ok";
}
}
?>
</td>
</tr>
<tr>
<td>Name of Resource:</td>
<td><?php echo $name; ?></td>
</tr>
<tr>
<td>Email address:</td>
<td><?php echo $email; ?></td>
</tr>
<tr>
<td>Image Name:</td><?php
//no error with image
if(!$error_file){
?><td><?php echo $file; ?></td>
</tr>
<tr>
<td> - Width:</td>
<td><?php echo $message_width; ?></td>
</tr>
<tr>
<td> - Height:</td>
<td><?php echo $message_height; ?></td>
</tr>
<tr>
<td> - Type:</td>
<td><?php echo $message_type; ?></td>
</tr>
<tr>
<td> - Size:</td>
<td><?php echo $message_size; ?></td>
</tr><?php
//error with image
}else{
?><td><?php echo $file["name"]; ?></td>
</tr><?php
}
?><tr>
<td>Description of Resource:</td>
<td><?php echo $comment; ?></td>
</tr><?php
//error message
$error_goback = <<<EOT
<tr>
<td align="center" colspan="2">
<br><br>
<b>Your image could not be uploaded. Please click <a href="javascript:history.back();">here</a> to return to the upload form.</b>
</td>
</tr>
EOT;
//error occurred
if($error){
echo $error_goback;
//store image
}else{
//get next id
$row = @mysql_fetch_array(@mysql_query("SELECT id FROM $db_table_pictures ORDER BY id DESC LIMIT 0,1;"));
$id = $row["id"] + 1;
//copy image
if(!(@copy($file["tmp_name"],$img.$id.$img_format_info))){
echo $error_goback;
}else{
//insert into database
@mysql_query("INSERT INTO $db_table_pictures VALUES('$id','$name',$file,'$comment',0,0);");
//send mail to user
@mail($email,"Thank you for uploading your image at $title!","Your image has been uploaded successfully at $title!\n\nView image: {$url}index.php?show=$id\nView toplist: {$url}toplist.php\nVote for images: {$url}\nUpload another image: {$url}create_new.php","From: $webmaster <$webmaster>\n");
//send mail to administrator
@mail($webmaster,"New image at $title","A new image has been uploaded at $title.\n\nName: $name\nEmail: $email\nComment: $comment\n\nView image: {$url}index.php?show=$id\nView toplist: {$url}toplist.php\nVote for images: {$url}\nUpload an image: {$url}create_new.php","From: $name <$email>\n");
?><tr>
<td align="center" colspan="2">
<br><br>
<b>Your image has been uploaded successfully! <a href="index.php?show=<?php echo $id; ?>">Click here to continue.</a></b>
</td>
</tr><?php
}
}
?></table>
</form><?php
//print upload form
}else{
?>Upload your own picture and let others vote for it.
<br><br>
</td>
</tr>
<tr>
<td width="300">Name of Resource:</td>
<td><input maxlength="50" name="name" size="30" type="text"></td>
</tr>
<tr>
<td>Your email address:</td>
<td><input name="email" size="30" type="text"></td>
</tr>
<tr>
<td valign="top">Choose image file:</td>
<td><input name="file" size="30" type="file"><br>
640x480px max (gif, jpg, png, swf)</td>
</td>
</tr>
<tr>
</tr>
<tr>
<td valign="top">Description of Resource:<br>(max. <?php echo $comment_size; ?> characters)</td>
<td><textarea cols="70" name="comment" onkeyup="this.value=this.value.slice(0,<?php echo $comment_size; ?>);" rows="6"></textarea></td>
</tr>
<tr>
<td> </td>
<td><input name="submit" type="submit" value="Upload image"></td>
</tr>
</table>
</form><?php
}
//print footer
echo $footer;
//close database connection
@mysql_close();
?>
Im aware that the temp file can also be moved to a specified folder but its just adding the filename to the database 'file' field which i need to do.
Move temp name:
<?php
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "<br />";
}
else
{
echo "Upload: " . $_FILES["file"]["name"] . "<br />";
echo "Type: " . $_FILES["file"]["type"] . "<br />";
echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
echo "Stored in: " . $_FILES["file"]["tmp_name"];
}
?>
<?php
if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/png")
|| ($_FILES["file"]["type"] == "image/jpg"))
&& ($_FILES["file"]["size"] < 200000))
{
if ($_FILES["file"]["error"] > 0)
{
echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
}
else
{
echo "Upload: " . $_FILES["file"]["name"] . "<br />";
echo "Type: " . $_FILES["file"]["type"] . "<br />";
echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";
if (file_exists("images/" . $_FILES["file"]["name"]))
{
echo $_FILES["file"]["name"] . " already exists. ";
}
else
{
move_uploaded_file($_FILES["file"]["tmp_name"],
"images/" . $_FILES["file"]["name"]);
echo "Stored in: " . "images/" . $_FILES["file"]["name"];
}
}
}
else
{
echo "Invalid file";
}
?>
Thanks.
- Steven Collins