Loading images from a Database

[Ogdan]

Hi, alright I am trying to load images from a database into a website, think photo bucket, the actual images are stored in the BLOB format in the table, along with name, type, id, size and some other stuff. I cannot for the life of me figure out how I can get this to work. Right now all I would like to do is be able to produce an image thats it. I would love any help on this topic.

 

Thanks,

 

Blake

[havenpets]

Is this something you were looking for? :

 

http://www.w3schools.com/php/php_file_upload.asp

 

It's a great tutorial and it shows you exactly how to make an "Image Uploading" system. And if you already have them in the database, simply run a query and instead of $_file[file][variable]... do $retrieved['VARIABLE_FROM_MYSQL'].

[mrMarcus]

you don't want to store images that way.  upload the image to the server, storing the image name in the database.  then just retrieve the name which in turn will call the image from the server.  gotta normalize.

[Ogdan]

Thank you for the quick replies, why do you say that it is a bad idea to store the images on the database in the BLOB format? could you explain a bit more?

[seventheyejosh]

Just copy(); it -> http://php.net/manual/en/function.copy.php

 

Much easier.

[mrMarcus]

to now view the image from the db, you will have to set the Content-type header.  i don't know your db schema, or any of your existing code for that matter, so i can't really help further at this time.

 

and to avoid a heated debate, storing images as BLOB format in a database is up to developers discretion.  i don't have enough experience with BLOB to entirely back it up.

[Ogdan]

Here is the simple web page right now i'm just trying to one image off my db and display properly. This is the test web site

 

<?php
$errmsg = "";
if (! @mysql_connect("localhost","root",""))
{
$errmsg = "Cannot connect to database";
}
@mysql_select_db("(database)");

$strSQL = "SELECT * FROM picture_page";
$rsPix = mysql_query($strSQL);
$numRows = mysql_num_rows($rsPix);
$i = 0;
echo "<h4>HI</h4>";

while($i < $numRows){
?>

<img src="testscript.php?id=1"/>
<?php
$i++;
}
?>

 

then the <img> tag calls the src of the test script which is here:

 

<?php
$errmsg = "";
if (! @mysql_connect("localhost","root",""))
{
$errmsg = "Cannot connect to database";
}
@mysql_select_db("(database)");

if (IsSet($_GET['pixID'])){
$gotten = @mysql_query("SELECT picture FROM picture_page WHERE pixID = ".$_GET['pixID']);
header("Content-type: image/jpeg");
while ($row = mysql_fetch_array($gotten))
{
print $row['picture'];

}
mysql_free_result($gotten);
}
?>

 

[PFMaBiSmAd]

Some of the reasons why you should not store files (images or any other relatively large file) in a database -

 

1) There is additional overhead, beyond what there is when the file is simply stored as a file using the file system, every time the file is requested. This adds to both the web server and database server load.

 

2) There is a maximum allowed packet size (default 1,048,576) that determines how much information you can transferrer in one query and determines how large of a file can be stored and retrieved without additional code to break the file into pieces and put it back together again.

 

3) Binary BLOB data is backed-up as two HEX characters per byte of data, which means that the backup file is twice the size of the actual binary data. This makes saving a backup or moving your database take twice as long and requires twice the amount of storage for the backup copy. The backup must also be successfully transfered as one piece.

 

Use a file system to store files, that is what file systems were designed to do efficiently. 1) There is less overhead, less load on the servers, and faster transfer rates, 2) the size is only limited by the operating system, 3) you can backup and restore many individual files/folders easier and with the ability to restart from the last successful file transfered when an error occurs. The backup requires less storage space as well.

[mrMarcus]

you try changing out:

 

if (isset($_GET['pixID']))

 

to

 

if (isset($_GET['id']))

 

and change $_GET['pixID'] in the query to $_GET['id'], seeing as how that's what you're using in your <img> tag.

[Ogdan]

@MrMarcus

yup i saw it right after I posted and good news it works!! thanks for your help.

 

@PFMaBiSmAd

Thank you very much for your explanation, if I had more time for the assignment then I would re-implement this using a file system. Just for reference what types of things would a BLOB be good for storing over a file system? just curious.

 

Thanks for everyone's help.

 

[mrMarcus]

your initial query isn't actually doing anything:

 

$strSQL = "SELECT * FROM picture_page";
$rsPix = mysql_query($strSQL);
$numRows = mysql_num_rows($rsPix);
$i = 0;
echo "<h4>HI</h4>";

while($i < $numRows){
?>

<img src="testscript.php?id=1"/>
<?php
$i++;
}

 

while() is a loop, therefore, you should refrain from using it unless you are retrieving multiple records from the db.

 

also, try and not use * in a query unless you are actualyl calling everything.  practice querying only the fields you require.

 

consider this:

 

<?php
$strSQL = "
select `pixID`
from `picture_page`
";

if ($rsPix = mysql_query($strSQL))
{
if (mysql_num_rows($rsPix) > 0)
{
	echo '<h4>HI</h4>';

	while($res = mysql_fetch_array($rsPix))
	{
		echo '<img src="testscript.php?id='. $res['pixID'] .'" />';
	}
	//OR...if not calling multiple records, get rid
	//of while(), and replace with $res = mysql_fetch_array($rsPix);
	//probably will have to edit query to call specific record in that case;
}
else
{
	//no records;
	echo 'nothing to see here.';
}
}
else
{
//query failed; display errors;
trigger_error(mysql_error());
}

[Ogdan]

@MrMarcus

Yeah I was just trying to get one picture to display I have been working on this problem all day so I just wanted to see somthing. I ended up fixing it and doing something similar to what you suggested.