Storing images in mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
cto1mac
Forum Commoner
Posts: 54
Joined: Tue Jan 27, 2004 6:11 am
Location: Virginia Beach, VA

Storing images in mysql

Post by cto1mac »

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
User avatar
GhostXL
Forum Newbie
Posts: 12
Joined: Sun Nov 28, 2004 10:58 am
Location: Netherlands

Post by GhostXL »

I'm trying to do the same thing. But i was thinking to just upload the pictures to a folder and store the url of the image in the database.
Only problem is i can't get jpg's to upload, but gif will work BUT I NEED jpg's :evil:.
User avatar
cto1mac
Forum Commoner
Posts: 54
Joined: Tue Jan 27, 2004 6:11 am
Location: Virginia Beach, VA

Post by cto1mac »

To my knowledge it shouldn't matter what the file type is. I know that it should be a two step process, step one upload the file to the server and then use the file_load feature to put it into the database, but I don't know how.
User avatar
GhostXL
Forum Newbie
Posts: 12
Joined: Sun Nov 28, 2004 10:58 am
Location: Netherlands

Post by GhostXL »

Found this on the web:
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 :)
So sounds like u better not put them images in the dBase...
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.
User avatar
cto1mac
Forum Commoner
Posts: 54
Joined: Tue Jan 27, 2004 6:11 am
Location: Virginia Beach, VA

Post by cto1mac »

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
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

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.
fl0w
Forum Newbie
Posts: 8
Joined: Tue Nov 23, 2004 2:19 pm
Location: Stockholm Sweden

Post by fl0w »

I'm now, since I havn't done this before, but wouldn't serializing work?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

meaby http://www.zhuo.org/htmlarea/ can improve usability?
User avatar
GhostXL
Forum Newbie
Posts: 12
Joined: Sun Nov 28, 2004 10:58 am
Location: Netherlands

Post by GhostXL »

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.
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.

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 ;))
User avatar
GhostXL
Forum Newbie
Posts: 12
Joined: Sun Nov 28, 2004 10:58 am
Location: Netherlands

Post by GhostXL »

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:

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>
file2: toevoegScript.php

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>&nbsp;</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>&nbsp;</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>
I'm sorry that part of the script is in dutch..... :roll:
Still have to clean up the code to.................. it's messy :oops:

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.
Post Reply