Images In Database

Sharif

Here's the plan:

For each article posted, I'd like my writers to pick an icon to represent that post. My question is, in a mysql tables' field, do I store the location of the icon image or is there another way of doing this?

szym9341

You can store images as a binary, then it is just stored as data in one of your columns... as a blob

I am learning how to do it as I type, so I hope someone will provide some answers...

trooper

Hye u guys its your lucky day

take a look at this article

I have done it and its pretty swell

HTH

AstroTeg

I continually recommend NOT storing the images in the database.

How are you going to update the image later on?
What if you need to move the images to another server because of space limitations
How are you going to work around the blob inconsistencies? (there's query restrictions/problems with blobs)

File handling is done best at the OS level, not DB level. Store the image PATH in the DB, but not the image.

trooper

AstroTeg

You have a good point however what happens if you have to move the site

You have to remember to "pick up all the files" from wherever on your server

If they are in the DB then you only need to take a dump of the sql and thats all

I do however agree that storing binary data in a db is not such a good idea?

AstroTeg

Let's take a look at the problem of moving a database with the images stored in the DB and not stored in the DB.

Let's assume you have 500mb of images (not unreasonable - this could be as many as ~1500 images).

Storing the images in the database, you now have to man-handle a 500mb data file.

Or if you decide you'll use phpmyadmin to create the sql, you're going to have a supper huge text file (I haven't tried it, but I'd predict the binary data would get encoded which can take up to twice the binary's size - although your mileage may vary).

Let's say you do go to another server, but they run a different DBMS instead of MySQL. How are you going to convert the blobs over to something compatible with the new DBMS? In theory, things may be ok. But "what if" they weren't 100% compatible? You'd have to write a script to dump the binary data back to a file and then create another script to import it into the new DB, right?

Let's take a look at storing the images in the file system instead of a database.

You change servers. From the new server you could initiate an FTP session (assuming you have some sort of FTP access). From FTP, you can bring over all the files.

If in the very possible chance the file path itself changes, you update your script with the new path (if you're smart, you put the path in an include file as a define() or something similar and only have it in one place - then when the path changes, you update it in one place only). Store the file name in the database, but not the full path.

Bang, you're done. And with this setup, there's no concern about which database you could be running or which OS you're running.

Something else to ponder is if your images take over your server. So you just need to move the images to another server. Now if you toss them all in the database, you have to move the entire database to a new server. But if you leave them as files, you can move the files to another server, leave the database where its at, and just update the path to point to the new location.

NerotheWarlord

But if you store them as files in the ftp, how do you allow users to upload pictures to be used with information that they submit which is stored in and called from the database?

I'm working on something that allows users to submit bios on their favorite comic book characters. I'd like a way to allow users to submit an image of this character, and have it displayed with the information on that character. And since the script I'm using pulls the info from the database, I'm hoping to figure out a way to allow users to send the pics to the db and retrieve them in the same fashion as the text.

Here's the script I'm using to list the characters and it also shows each character's bio when you choose one....

<html>
<body background="http://www.neropolis.com/vb/images/background.gif" TEXT="#000000" LINK="#0000FF" VLINK="#000000">

<table align="center" border="0">

<tr><td align="center">
<img src="/images/cbdimage.gif">
</td></tr>

<tr>
<td align="center"><a href="/cbd/submitbio.php"><b>Submit a Character bio</b></a> | <a href="/cbd/bioslist.php"><b>Back to Character Index</b></a></td>
</tr>

<tr><td>

<table border="0">
<tr><td>
<?php
$db = @mysql_connect('localhost', '*******', '**********')
or die("Could not connect: " . mysql_error()); 

mysql_select_db("**********", $db);

// display individual record
if ($id) 
{
	$result = mysql_query("SELECT * FROM cbd WHERE id=$id", $db);
	$myrow = mysql_fetch_array($result);
	printf("<b>Company:</b> %s\n<br>", $myrow['company']);
	printf("<b>Name:</b> %s\n<br>", $myrow['name']);
	printf("<b>Real Name:</b> %s\n<br>", $myrow['realname']);
	printf("<b>First Appearance:</b> %s\n<br>", $myrow['firstappearance']);
	printf("<b>Group Affiliation:</b> %s\n<br>", $myrow['team']);
	printf("<b>Height:</b> %s\n<br>", $myrow['height']);	
	printf("<b>Weight:</b> %s\n<br>", $myrow['weight']);
	printf("<b>Hair Color:</b> %s\n<br>", $myrow['hair']);
	printf("<b>Eye Color:</b> %s\n<br><br>", $myrow['eyes']);
	printf("<b>Powers and Abilities:</b> %s\n<br><br>", $myrow['powers']);
	printf("<b>Known Weaknesses:</b> %s\n<br><br>", $myrow['weakness']);
	printf("<b>History:</b> %s\n<br>", $myrow['history']);
	// single quotes marks array pointers
} 
else 
{
	// show character list
	$result = mysql_query("SELECT * FROM `cbd` ORDER BY `name` ASC", $db);
	if ($myrow = mysql_fetch_array($result)) 
	{
		// display list if there are records to display
		do 
		{
	//		printf("<a href=\"%s?id=%s\">%s %s</a><br>\n", $PHP_SELF, $myrow['id'], $myrow['company'], $myrow['name']);

echo "<a href='$PHP_SELF?id=" . $myrow['id'] . "'>". $myrow['name'] . " \n<br />";

	} 
	while ($myrow = mysql_fetch_array($result));
} 
else 
{
	// no records to display
	echo 'Sorry, no records were found!';
	// single quotes are best to use when theres no variables in the echo.
}
}
?>
</td></tr>
</table>


</td></tr>



</table>
</body>
</html>

And here's the submission form I'm using...

<html>

<body background="http://www.neropolis.com/vb/images/background.gif" TEXT="#000000" LINK="#0000FF" VLINK="#000000">


<table align="center" border="0">
  <form method="post" action="<?php echo $PHP_SELF?>">
<tr>
	<td colspan="2" align="center"><img src=/images/submit.gif></td>
<tr>
<td align="center" colspan="2"><a href="/cbd/submitbio.php"><b>Submit a Character bio</b></a> | <a href="/cbd/bioslist.php"><b>Back to Character Index</b></a></td>
</tr>
<tr><td align="center">
<?php



if ($submit) {

  // process form

  $db = mysql_connect("localhost", "*******", "*******");

  mysql_select_db("*******",$db);

  $sql = "INSERT INTO cbd (company,name,realname,firstappearance,team,height,weight,hair,eyes,powers,weakness,history) VALUES ('$company','$name','$realname','$firstappearance','$team','$height','$weight','$hair','$eyes','$powers','$weakness','$history')";

  $result = mysql_query($sql) or die("Error: ".mysql_error()); 

  echo "Thank you for your character bio submission!\n";

} else{



  // display character submission form



  ?>
</td></tr>
</table>


<table align="center" border="0">
  <form method="post" action="<?php echo $PHP_SELF?>">
<tr>
	<td colspan="2" align="center"><img src=/images/submit.gif></td>
<tr>
<td align="center" colspan="2"><a href="/cbd/submitbio.php"><b>Submit a Character bio</b></a> | <a href="/cbd/bioslist.php"><b>Back to Character Index</b></a></td>
</tr>
  <tr>
	<td>Company:</td><td><input type="Text" name="company"></td>
</tr><tr>
	<td>Character Name:</td><td><input type="Text" name="name"></td>
</tr><tr>
	<td>Real Name:</td><td><input type="Text" name="realname"></td>
</tr><tr>	
	<td>First Appearance:</td><td><input type="Text" name="firstappearance"></td>
</tr><tr>
	<td>Group Affiliation:</td><td><input type="Text" name="team"></td>
</tr><tr>
	<td>Height:</td><td><input type="Text" name="height"></td>
</tr><tr>
	<td>Weight:</td><td><input type="Text" name="weight"></td>
</tr><tr>
	<td>Hair Color:</td><td><input type="Text" name="hair"></td>
</tr><tr>
	<td>Eye Color:</td><td><input type="Text" name="eyes"></td>
</tr><tr>
	<td valign="top">Powers and Abilities:</td><td><textarea rows="10" cols="40" name="powers"></textarea></td>
</tr><tr>
	<td valign="top">Known Weaknesses:</td><td><textarea rows="5" cols="40" name="weakness"></textarea></td>
</tr><tr>
	<td valign="top">History:</td><td><textarea rows="10" cols="40"  name="history"></textarea></td>
</tr><tr>
	<td colspan="2" align="center">
  <input type="Submit" name="submit" value="Enter information">

  </form>

</td></tr>
</table>

  <?php



} // end if



?>



</body>



</html>

Now what would be an easy way to do what I'm asking, if not through the sql db? And if the sql db is my best bet... how would I do that?

Sorry still a newbie to all of this

commandonz

I've just written something similar for my own site. I'm not a PHP expert, but i'm an experienced commercial enterprise level Java developer.

My approach was (like AstroTeg) that images are best handled by the web server. The user uploads their image to the server, it puts them all into a predefined directory, and renames the file to a unique id - eg "1.jgp", "2.gif", etc. An entry is made in the database that associated the image with the appropriate article in the database.

It works pretty well and saves putting loads of binary data in the database, which is really what web servers and file systems are good for.

NerotheWarlord

So how would I do that? Is there a tutorial somewhere I can read? Or could you show me how I can modify my script to do this?

commandonz

It's easy enough. All files uploaded are stored as temporary files, you just have to move them. Look at the move_uploaded_file command.

   for ($i = 0; $i <= 5; $i++) {
      $str = $_FILES['image']['name'][$i];
      if (strlen($str) > 0) {
         // Get the file information
         $name = $_FILES['image']['name'][$i];
         $type = $_FILES['image']['type'][$i];
         $tmpFilename = $_FILES['image']['tmp_name'][$i];
         $error = $_FILES['image']['error'][$i];
         $size = $_FILES['image']['size'][$i];

     // code removed for brevity. you need to work out your $permanentLocationFilename)) 

     // Correct the filename, if we created one in the database to start with
     if ($permanentLocationFilename != null) {
        // Move the file to the appropriate place
        if (move_uploaded_file($tmpFilename, $permanentLocationFilename)) {
           // That's lovely
        } else {
           // Ah well
        }
     }
  }
   }

This works for uploading multiple files, its easy to change if you only want to upload one file. You have to set your $permanentLocationFilename if you use this code as written. Remember i'm not a php guru, this is just what worked for me.

NerotheWarlord

so confused

commandonz

Time for you to read some documentation, I think. Read the docs for file uploading, or google "php file upload tutorial".

NerotheWarlord

Will do. Thanks

sid

there even are fully working examples in the php documentation that show you how to upload files and move them - check [man]move_uploaded_file[/man] and what the docu has to say about the $_FILES global var. this should help you out.

sid