Storing images in mysql
Moderator: General Moderators
Storing images in mysql
OK, I know that this can be done. But how? I know how to store just about any other kind of data in the database. How do I do this. I want to have a web form that you select a local image file from your local machine, and upload that image file into the database. How do I do this??
Thanks
Thanks
Found this on the web:
As soon as my script is ready i'll share it with u, but first i need to figure out why it doesn't upload jpg's.
So sounds like u better not put them images in the dBase...In my opinion you have two choices:
>
> i) store the image in a binary BLOB field
> ii) store a reference to a filename in a sensible text field, and actually store the image
in your normal filesystem
>
> Of these, I absolutely recommend (ii) and think that (i) is a disastrous thing to do. It w
ill lead to large server load, much slower DB performance, more interface problems, and very
large data tables.
>
> I cannot think of a single good reason to actually store an image in a mysql database, but
if you have one please do post it![]()
As soon as my script is ready i'll share it with u, but first i need to figure out why it doesn't upload jpg's.
You wanted a good reason I will give you a good reason.
I am building this site for a charity, so I have to make it as simple as possible. Trusting that whoever they pick to update information on the site (not me) is going to enter the right name for the img file into the form , and that they will not pick the same name for the image file as a pre-existing file name , thus causing mass confusion. I agree with you that I would prefer to store the path in the db and just have them upload the image file. So any recommendations as how to eliminate human error would be greatly appreciated. Thanks
I am building this site for a charity, so I have to make it as simple as possible. Trusting that whoever they pick to update information on the site (not me) is going to enter the right name for the img file into the form , and that they will not pick the same name for the image file as a pre-existing file name , thus causing mass confusion. I agree with you that I would prefer to store the path in the db and just have them upload the image file. So any recommendations as how to eliminate human error would be greatly appreciated. Thanks
It's a lot simpler to put the path into the DB rather than the binary itself. Saves you a LOT of trouble in the future I'm sure. I haven't used the BLOB method, so I'm not speaking out of experience, but the theory behind it all seems very roundabout. The way I have seen it done sometimes is to take a random name, either a number or some MD5 creation and set that as the name, so no 2 image names are the same.
meaby http://www.zhuo.org/htmlarea/ can improve usability?
What if he/she uploads a picture that allready exists inside de dBase, u still got the same problem... of having 2 the same pictues.I am building this site for a charity, so I have to make it as simple as possible. Trusting that whoever they pick to update information on the site (not me) is going to enter the right name for the img file into the form , and that they will not pick the same name for the image file as a pre-existing file name , thus causing mass confusion.
This could easily be eliminated by using an id for the pictures, the id = filename - extention. In the form users are aksed to enter the id not the filename. In the dBase u set the id collom to be the primarykey.
When uploading first put the id inside the dBase if de id allready exists the script will the we only have to make sure that if the script fails the uploaded file is not saved on the server.
Any way that is what i'm working on, I allso want to automaticly create a thumbnail using GDlib. I'm hoping to finnish the code by the end of the week, I'll post it here (if it works
Wel here it is then. These 2 files (file 1 is a form and file 2 the upload and put in database script) will upload a picture too a folder resize it to the right size and create a thumbnail. It will allso make a entry in a database regarding the picture (id, explanation, etc of the picture).
Make sure u have permission to upload to the folder!!
file1:
file2: toevoegScript.php
I'm sorry that part of the script is in dutch..... 
Still have to clean up the code to.................. it's messy
Users can't upload pictures of the same name because the id (wich is used to create the name of the picture) is stored in the database as primary key, wich MUST be unique if it's not the script will fail.
Make sure u have permission to upload to the folder!!
file1:
Code: Select all
<body>
<form enctype="multipart/form-data" method="post" action="toevoegScript.php">
<p><strong>-- ADMIN-- -- FOTO TOEVOEGEN --</strong></p>
<p><?php
$mysql_host="....";
$my_database="....";
$my_table="....";
$mysql_user="....";
$mysql_password="....";
/* Connecting, selecting database */
$link = mysql_connect($mysql_host, $mysql_user, $mysql_password)
or die("Could not connect : " . mysql_error());
echo "Connected successfully<br>";
mysql_select_db($my_database) or die("Could not select database<br>");
?></p>
<p>bestand:
<input type="file" name="userfile">
</p>
<p>
id:
<input name="id" type="text" id="id">
</p>
<p>titel:
<input name="titel" type="text" id="titel">
</p>
<p>omschrijving:
<input name="omschrijving" type="text" id="omschrijving">
</p>
<p>thema:
<select name="thema" id="thema">
<option value="new">nieuw thema</option>
<?php /* Performing SQL query */
$query = "SELECT DISTINCT thema FROM ".$my_table;
$result = mysql_query($query) or die("Query failed : " . mysql_error());
/* Printing results in HTML */
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
foreach ($line as $col_value) {
echo '<option value="'.$col_value.'">'.$col_value.'</option>';
}
}?>
</select>
<input name="themaNew" type="text" id="themaNew">
</p>
<p>fotograaf:
<select name="fotograaf" id="fotograaf">
<option value="new">nieuwe fotograaf</option>
<?php /* Performing SQL query */
$query = "SELECT DISTINCT fotograaf FROM ".$my_table;
$result = mysql_query($query) or die("Query failed : " . mysql_error());
/* Printing results in HTML */
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
foreach ($line as $col_value) {
echo '<option value="'.$col_value.'">'.$col_value.'</option>';
}
}?>
</select>
<input name="fotograafNew" type="text" id="fotograafNew">
</p>
<p>
<input type="submit" name="Submit" value="toevoegen">
</p>
</form>
<hr><p><strong><a href="index.php"><< terug >> </a></strong></p>
</body>Code: Select all
<body>
<p><strong>-- ADMIN-- -- FOTO TOEVOEGEN --</strong></p>
<p><?php
$mysql_host="....";
$my_database="....";
$my_table="....";
$mysql_user="....";
$mysql_password="....";
/* Connecting, selecting database */
$link = mysql_connect($mysql_host, $mysql_user, $mysql_password)
or die("Could not connect : " . mysql_error());
echo "Connected successfully<br>";
mysql_select_db($my_database) or die("Could not select database<br>");
?></p>
<p> </p>
<?php
// check for empty fields
/*while (list($key, $val) = each($HTTP_POST_VARS)) {
if ($val==""){
echo '<p>Niet alle gegevens zijn in gevuld<hr><strong><a href="toevoegForm.php"><< terug >> </a></strong></p>';
exit;
}
}*/
//
if ($thema=="new"){
$thema=$themaNew;
}
if ($fotograaf=="new"){
$fotograaf=$fotograafNew;
}
// orientatie bepalen
list($width_orig, $height_orig) = getimagesize($userfile);
if ($width_orig<=$height_orig){
$orientatie=1;
} else {
$orientatie=2;
}
/* insert data */
$insert = "INSERT INTO ".$my_table." (id, titel, omschrijving, thema, fotograaf, orientatie) VALUES ('".$id."', '".$titel."', '".$omschrijving."', '".$thema."', '".$fotograaf."', '".$orientatie."')";
echo $insert."<br>";
$result = mysql_query($insert, $link);
if ($result){
echo "Gegevens met succes toegevoegd aan de database.<br>";
} else {
echo "Fout bij gegevens toevoegen aan de database - ".mysql_errno($link)." : ".mysql_error($link).".<br>";
echo '<hr><strong><a href="toevoegForm.php"><< terug >> </a></strong>';
exit;
}
?>
<p> </p>
<?php
$doelmap="../dBasePictures";
$bestandsnaam=$id.".jpg";
// resize original
$width = 350;
// Get new dimensions
list($width_orig, $height_orig) = getimagesize($userfile);
$height = ($width / $width_orig) * $height_orig;
// Resample
// if GD 2.0.l or greater is available use imagecreatetruecolor() instead of imagecreate()
$image_p = imagecreate($width, $height);
$image = imagecreatefromjpeg($userfile);
// if GD 2.0.l or greater is available use imagecopyresampled() instead of imagecopyresized()
imagecopyresized($image_p, $image, 0, 0, 0, 0, $width, $height, $width_orig, $height_orig);
// save resized picture in desination folder
imagejpeg($image_p, $doelmap."/".$bestandsnaam, 95);
// resize to thumbnail
$width = 125;
$height = 125;
// Get new dimensions
list($width_orig, $height_orig) = getimagesize($userfile);
if ($width && ($width_orig < $height_orig)) {
$width = ($height / $height_orig) * $width_orig;
} else {
$height = ($width / $width_orig) * $height_orig;
}
// Resample
// if GD 2.0.l or greater is available use imagecreatetruecolor() instead of imagecreate()
$image_p = imagecreate($width, $height);
$image = imagecreatefromjpeg($userfile);
// if GD 2.0.l or greater is available use imagecopyresampled() instead of imagecopyresized()
imagecopyresized($image_p, $image, 0, 0, 0, 0, $width, $height, $width_orig, $height_orig);
// save resized picture in desination folder
imagejpeg($image_p, $doelmap."/thumb".$bestandsnaam, 80);
?>
<hr><strong><a href="toevoegForm.php"><< terug >> </a></strong>
</body>Still have to clean up the code to.................. it's messy
Users can't upload pictures of the same name because the id (wich is used to create the name of the picture) is stored in the database as primary key, wich MUST be unique if it's not the script will fail.