Images in Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Adam
Forum Newbie
Posts: 11
Joined: Mon Jul 28, 2003 2:45 am

Images in Database

Post by Adam »

I am in a dilemma, my site http://www.gamer-talk.net has a lot of screenshots stored in my databse. Is there any way to get these files from the db without manually downloading every single one? Isn't it a lot better not to store images used for a purpose like them in a database? Anyone recommend a good freeware image gallery script?

Thanks in advance,
Adam
macewan
Forum Commoner
Posts: 97
Joined: Mon Jul 07, 2003 8:27 pm

Post by macewan »

store the names of the images in the database and images in a folder. then print it. be sure to get width & height so page displays properly.

<?php
list($width, $height)=getimagesize("../images/$nameofimage");
echo "
<img src=\"../images/$nameofimage\" width=\"$width\" height=\"$height\" alt=\" \" border=\"0\" />
";
?>
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Actually, getimagesize() is even smarter than that...

Code: Select all

<?php
$size = getimagesize("image.jpg"); 
echo '<img src="image.jpg" '.$size[3].'>'; 
?>
But why store them in the db?

Waste of space? Not really, adding the binary to a db (that in the bottom is file-driven) also uses space...
Waste of time? Making a connection to the db to get the image works. But if 100's of users does it, and you have 10 screenshots in one page, you get 1000 connections (roughtly, not thinking about pconnect etc.).
Safety? Disabling ofsite hosts to grabbing the images is a reason, but that can be done with using <img src="image.php"> coding...

And I still havn't found any reasons to why users want to store images in their db. Please explain why you do. (Serious, I really want to know)
?>
Adam
Forum Newbie
Posts: 11
Joined: Mon Jul 28, 2003 2:45 am

Post by Adam »

I dont know why, thats how my CMS was configured when I got it... I wish I could export all of them into folders, and have a program re-detect them out of the database.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

ikk

if you can fetch the id's or something else unique of the database-images, it can be done with two .php's (or less, or more).

Edited:
Possible that there is someway you can post a 'row' of an image from the db?
Adam
Forum Newbie
Posts: 11
Joined: Mon Jul 28, 2003 2:45 am

Post by Adam »

Yea I can fetch the ID's of all the images, where would I go from there? Thanks for all of your help thus far.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

On the fly, bare with me...

Code: Select all

<?php
// Filename image.php
//connect to database, and fetch info for the image...
$conn = mysql_connect("foo", "bar", "fb") OR DIE (mysql_error());
mysql_select_db ("images", $conn) OR DIE (mysql_error());
// ...with help from the $_GET here below.
$sql    = "SELECT * FROM image WHERE image_id=".$_GET["imageid"];
$result = mysql_query ($sql, $conn);
if (mysql_num_rows ($result)>0) {
  $row = @mysql_fetch_array ($result);
// not sure your db contains this below, but it would help (JPG/PNG/GIF etc)
  $image_type = $row["image_type"];
  $image = $row["image"];
  Header ("Content-type: $image_type");
  print $image;
}
?>
Then call it using a <img src="image.php?imageid=1"> to paste them onto a page... Or http://exampl.com/image.php?imageid=1 to start a download...

But to automaticly start downloading all images I'm not sure.

Adding a sleep() function that loops the same page, adding 1 to the id perhaps... Or get all id's and loop that to post all images to one page with the <img>-tag (could take time) and use a third part software to download the page with contents...

I might not have solved it, but given some ideas... =)
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

JAM wrote:And I still havn't found any reasons to why users want to store images in their db. Please explain why you do. (Serious, I really want to know)
Typically, storing the image path in the database has been the direction I have taken. A couple of scenarios have come up which offer a compelling reason to store the media in the DB:
1.) A large group of images that need to be accessed from both an Internet and an Intanet server. These two machines cannot see each other's files systems are are umpteen firewalls apart - replicate or put the images in a DB they can both see through certain protocols?

2.) A directive to get all files (except scripts, includes, etc.) off of a server so they can do some load balancing with a server farm.

Easiest method seems the database, as have some very robust and fast DBMS that will do compression on BLOB types. No messing around with some stupid include file containing a file path to another machine that will change 1,000 times before they make their mind up! It's in the DB, write a script to be the media-handler.

So far, access is very,very fast, and almost anything can be stored as a BLOB; images, PDFs, Excell spreadsheets, you name it. We have recently upgraded to DB2 V. 8, and it seems to handle it well.

Just my thoughts, any other thoughts and opinions quite welcome!

Phil J.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Interesting subject. And your ideas doesn't seem to idiotic.

I can grasp the thought you have on that the 'include to a path that changes 15 times/month' is a serious issue.
Also that the intranet/extranet aspects and problems (or should we say lack of) could be easely handled.

But if the site clearely is made for the www audience and nothing else? And if you one day need to change host, the path could be made the same (./images/ will allways be ./images/) on the new host...

Interesting readingon the subject, as I'm interested what really happens to a site with 100's or perhaps even 1000's of visitors per sec.

Keep posting comments!
Adam
Forum Newbie
Posts: 11
Joined: Mon Jul 28, 2003 2:45 am

Post by Adam »

All I know is I am going to manually download all of these images, and get them the heck out of my database.
snek_one
Forum Newbie
Posts: 7
Joined: Wed May 21, 2003 9:07 am

Post by snek_one »

JAM wrote:On the fly, bare with me...

Code: Select all

<?php
// Filename image.php
//connect to database, and fetch info for the image...
$conn = mysql_connect("foo", "bar", "fb") OR DIE (mysql_error());
mysql_select_db ("images", $conn) OR DIE (mysql_error());
// ...with help from the $_GET here below.
$sql    = "SELECT * FROM image WHERE image_id=".$_GET["imageid"];
$result = mysql_query ($sql, $conn);
if (mysql_num_rows ($result)>0) {
  $row = @mysql_fetch_array ($result);
// not sure your db contains this below, but it would help (JPG/PNG/GIF etc)
  $image_type = $row["image_type"];
  $image = $row["image"];
  Header ("Content-type: $image_type");
  print $image;
}
?>
Then call it using a <img src="image.php?imageid=1"> to paste them onto a page... Or http://exampl.com/image.php?imageid=1 to start a download...

But to automaticly start downloading all images I'm not sure.

Adding a sleep() function that loops the same page, adding 1 to the id perhaps... Or get all id's and loop that to post all images to one page with the <img>-tag (could take time) and use a third part software to download the page with contents...

I might not have solved it, but given some ideas... =)
Actually you could use fopen() to write the files back to the server ;)
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

snek_one wrote: Actually you could use fopen() to write the files back to the server ;)
Yes or imagepng/jpeg/gif and some other ways...

Adam:
Test making them all on one page (loop <img src>-tag's from imageid1 to imageid99 (or whatever)) and then File -> Save As in IE or similiar in other brosers to download everything on the page... Still just ideas.
Adam
Forum Newbie
Posts: 11
Joined: Mon Jul 28, 2003 2:45 am

Post by Adam »

Ok, I saved 466 screenshots to folders on my hard drive... I wrote a pretty simple script and made a new table in my database also. The table has these columns.

screensid int(11) No auto_increment
folder text Yes NULL
path text No
thumb text No
gamename text No
gameid int(11) No 0
systemid int(11) No 0
date datetime No 0000-00-00 00:00:00

The script I wrote basically asks for folder, screenshot path and thumbnail path. I have to upload them on to my server with ftp. I want to be able to upload the screen, thumb and have the db automatically know the path. Anyone know how I would go about doing this? Even a good tutorial would help. My code I uses to add screenshots is below.

Code: Select all

<?php

          if ($action == "add") {

	if ($step == "2") {
	if ($folder == "") {
		print "You must fill in all feilds"; die;
	}
		if ($path == "") {
			print "You must fill in all feilds"; die;
	}
		if ($thumb == "") {
			print "You must fill in all feilds"; die;
	}

	$query="SELECT * FROM games WHERE gameid = '$gameid'";
	$result=mysql_query($query);
	$row = mysql_fetch_array($result);
	$gamename = "$row[gamename]";

	$linkID = @mysql_connect("$dbhost", "$dbuser", "$dbpassword");
	mysql_select_db("$dbname", $linkID) or die(mysql_error());

	$date = date("Y-m-d H:i:s");
	$resultID = mysql_query("INSERT INTO screenshots (screensid, folder, path, thumb, gameid, gamename, systemid, date) VALUES ('$screensid', '$folder', '$path', '$thumb', '$gameid', '$gamename', '$systemid', '$date')", $linkID) or die(mysql_error());
	if ($resultID == TRUE) {
		print "Screenshot was added to the database.<BR>";
	} else {
		print "Screenshot was not added to the database. Please try again.<BR>";
	}
}
else
{
	$query="SELECT * FROM games WHERE systemid = '$systemid'";
	$result=mysql_query($query);
	$row = mysql_fetch_row($result);
	$ngames = $row[0];
	if ($ngames == "") { print "There is not games in this system."; die; }

	echo "<form method="POST"action="?module=screenshots.php&action=add&step=2">
	<input type="hidden" name="systemid" value="$systemid">
  <table cellSpacing="0"cellPadding="5"width=668 border="0"height="546">
    <tr>
      <td vAlign="top"width=147 align="right"height="22">
      <p><font face=""Verdana"">Game:</font></td>
      <td vAlign="top"width=511 height="22"><b>
      <font face="Arial, Helvetica, sans-serif"size="2">
      <p align="left">";
	echo "<input type="hidden" name="systemid" value="$systemid">";
	$query="SELECT * FROM games WHERE systemid = '$systemid' ORDER BY gamename ASC";
	$result=mysql_query($query);
	echo "<select name="gameid" style="width:"100%";" tabindex="2">";
	while($row = mysql_fetch_object($result)) {
		echo "<option value="".$row->gameid.""";
		echo ">".$row->gamename."</option>";
	}
	echo "</select>";
      echo "</font></b></td>
    </tr>
    <tr>
      <td vAlign=top align="right" height="10">
      <face="Verdana">folder:</font></td>
      <td vAlign="top" height="10">
	  <input type="text" name="folder" size="20"></td>

    </tr>
        <tr>
	      <td vAlign=top align="right" height="10">
	      <face="Verdana">File Path:</font></td>
	      <td vAlign="top" height="10">
		  <input type="text" name="path" size="20"></td>

    </tr>
        <tr>
	      <td vAlign=top align="right" height="10">
	      <face="Verdana">thumb:</font></td>
	      <td vAlign="top" height="10">
		  <input type="text" name="thumb" size="20"></td>

    </tr>
    <tr>
      <td vAlign=top width=147 align="right"height="324">
      &nbsp;</td>
      <td vAlign="top"width=511 height="324">
      <p align="left"><BR>
  	 <p><input type="submit"value="Add Screenshots"name="B1"><input type="reset"value="Clear"name="B2"></p>
  		</td>
    </tr>
  </table>
</form>";
die;
}
}
?>
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Never strive to be idiotic....

The intranet/internet problem is not easily handled, since things beyond our control are locked down between the two and they shall never meet...

If it is strictly www, yes, by all means just store the path to it! As far as the database taking hits, that would depend upon the DBMS. I gather MySQL is fairly robust in this respect, as is DB2.

Phil
=========

[quote="JAM"]Interesting subject. And your ideas doesn't seem to idiotic.

I can grasp the thought you have on that the 'include to a path that changes 15 times/month' is a serious issue.
Also that the intranet/extranet aspects and problems (or should we say lack of) could be easely handled.

But if the site clearely is made for the www audience and nothing else? And if you one day need to change host, the path could be made the same (./images/ will allways be ./images/) on the new host...

quote]
Tovaar
Forum Newbie
Posts: 1
Joined: Fri Feb 27, 2004 3:20 am
Location: Pasadena Tx

Databease Image problem

Post by Tovaar »

:?
Ok Here is My Delima...
I am creating a Member database for an organization that I belong to, Have the database Working fine, Except that I cannot link images TO a Database Entry to be displayed in the Output file.
I have been focusing on ONE entry in the database trying to get the filename and the "image" data base entry on this one person to work right...
Here is the Scenario

The "image" field is varchar(30) in the database,
For Member # 190, the data in the "image" variable is "190.jpg".
In the look-up form, the php code is as follows:

Code: Select all

<?php  echo "<img src="http://www.domain.com/images/php/$image.jpg">";  ?>
When the Look-up is accessed, Where the Image is supposed to be, is a broken image icon, and the properties look like this:

Code: Select all

http://www.domain.com/images/php/.jpg
when it is suposed to be

Code: Select all

http://www.domain.com/images/php/190.jpg
I am probably going to feel like a schmuck on this one because I feel like I am over looking something very easy with this, But I would apreciate any help possible.
Post Reply