Image to Database

treeleaf20

All,
I can upload a filename into the database and then store the file in the web directory. I was wondering if there is a way to acutally upload the image into the database. I need this because people can update and delete pictures and for storage reasons I don't want old pictures being stored on the server that aren't in use. Does anyone have a snippet of code or know how to do this? I really need the help.

THank you in advance.

drew010

you would want to use a blob type field for the database, and just store the binary contents of the image in this field, as you would with any other text field. in order to display the image, you will need to create a script that would pull this data out and send the appropriate image headers and then output the contents of the image, so it can be callable via an img tag like <img src="image.php?userid=1234" />
Ive never looked for any snippets on this, but im sure a search in here or on google/yahoo could find some examples somewhere.
If your table is fairly large in size, this can greatly decrease the speed of searches since such a large data is in the table, so I would recommend using a separate table to store the image data. perhaps structure it with the username or id as one column, and the image data as the other. that way normal insertions and selects on the main table will still be very quick.

treeleaf20

Are you saying just insert the image like normal and then do something like this:

$query = "Select pic from table where userid = '1234' ";
$result = mysql_query($query);
$resultset = mysql_fetch_array($result);

echo "<img src = \"www.website.com\images\"".$resultset['pic'].">";

Would that work?

Thanks for your help.

drew010

its close but has to be like this:

image.php

<?php

//connect to database

$id = addslashes($_GET['id']);
$res = mysql_query("SELECT pic FROM table WHERE userid = '$id'");
$data = mysql_fetch_row($res);

header('Content-Type: image/jpeg');  //this will need to be changed accordingly with the image type
echo stripslashes($data[0]);  //the binary contents of the image

?>

then you call this in an image tag like <img src="image.php?userid=1234" />

the insert might look something like

//... form based upload

//connect to db

$imagedata = addslashes(file_get_contents($_FILES['file']['tmp_name']));

$res = mysql_query("INSERT INTO table VALUES ( '$userid', '$imagedata' )");

?>

treeleaf20

Would it be best to have a whole bunch of headers for like gif, jpeg, png and so forth or coudl you pull the type and then choose the right header dependent on the file type. Have a whole bunch of if statements. Which would be wiser?

Thanks

drew010

the best way would be to add a column that stores the content type, which you can also take from the $_FILES['file']['type'] variable during a form upload. alternatively, if the image is not uploaded you can determine the image type by examining the headers of the image, which would be looking at the hex values of the first few bytes.

treeleaf20

Ok I can get it to insert into the datbase. When I run this query to get it out:

$id = addslashes($_GET['id']);
$res = mysql_query("SELECT pic FROM group_pics WHERE group_id = '23'");
$data = mysql_fetch_row($res);

header('Content-Type: image/gif');   //this will need to be changed accordingly with the image type
echo stripslashes($data[0]);  //the binary contents of the image

It doesn't display the image.

The query that inserts the image into the database is:

insert into group_pics (group_id, pic, type) values ('23' ,'GIF89aK\0K\0Ä\0\0ÌÌÌìììÖÖÖ™™™äää¾¾¾**ÃÃÃÜÜÜ¥¥¥áááéééÑÑÑÙÙÙ³³³ÉÉÉÎÎÎ\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0!ù\0\0\0\0\0,\0\0\0\0K\0K\0\0ÿ Ždižhª®lë¾p,Ïtmßx®ï|ïÏŽEÈ@4~HQ„0lIßÒI @£;D•Ê8bqmuñÅiÅNF¤lc¢víMEÈgº“p—åõC|}0€‚ƒ.…z‡ˆ,ŠtŒ*o‘’(€Cv—,šW*B€^¢)€§+™zœ¬)¤o¦±\' Ÿh°¶(©bj¼+³iµÁ(gMÅÆ˜qÌ¹p.nNË_ÈT¡*®U«}Ô\\Ú%ÃTÑroîïðîo»_aš÷MôX”øtúQüú¡!#\' À-–\0Ô”0‰Á…{î<„èŽ/ŠhÚ8Pà€Ž#ˆ$HR&Ïÿ˜œurˆH t!Óc@\0€S„@\0h\"\0(Ð00Dé\0p5ÀÀ¨ÑM°s†Ô<\0x³\0ÙŠ8ðëÒ¦khÅ:×€ <ÐjïRRuöÔ aÙ.(j@)Ó\0N¼ Ð\0è§Ÿ\0üL@h×8À©t€/\0“Õ6h[Ú*ÛµM€æÁ¬ ™eº()ÔÕMšÓ.éŸO‡È@§mÔrƒ{V¬Ö¡\'FûÇ1\\Ž©-3®y\0g\0’|†Ñ‚V±?8›Vqïã¥;\0ñøÌ¥õÅb\0üÄv‹Ù×cN\0À{KåBzVqEH4IçÀHiˆ„\0* @ -å#À\"@P×`$†:_ÈHXØø†79ê(Æ?Høøã:iLsÜlBd2\0ÄxU\0éÃ#|8© ”u°Ae“a‰–TTÙ%ƒL–±¥•€xYægŽY…F=´)š•‘¤˜s’Yç6ar™g8lÜég?thŸh¾¢§¡v\"êf‡F”¨?‹JX†“xZ)˜ŠO¡”]êèŸUàØB\0;', 'image/gif')

The actual php query to insert it is:

$imgStr = addslashes(file_get_contents($_FILES['userfile']['tmp_name'])); 
//$imgStr=addslashes(file_get_contents($imagePath));
$filetype = $_FILES['userfile']['type'];
#Store Image String to the database
$querypic="insert into group_pics (group_id, pic, type) values ('$groupid' ,'".$imgStr."', '$filetype')";
echo $querypic;
mysql_query($querypic) or die(mysql_error());

Does that look right? It tries to pull an image but just displays a blank 75X75 picture which is the size of the actual file. Almost have it.

drew010

seems to look okay. heres a simple example i made quite a while ago. maybe you could play around with it and see if you can get it to fit with what you have set up.

upload.php

Upload Form:<br />
<form method="POST" enctype="multipart/form-data">
File: <input type="file" name="file1"><br />
<input type="submit">
</form>

<br /><br />


<?php

$link = mysql_connect("localhost", "USERNAME", "PASSWORD");
mysql_select_db("drew010_main", $link);

if ($_SERVER['REQUEST_METHOD'] == "POST") {
	$data = file_get_contents($_FILES['file1']['tmp_name']); //get binary data from tmp location
	$data = gzcompress($data, 9); //compress data at best level
	$data = addslashes($data); //escape special characters for db insertion

$type = $_FILES['file1']['type'];

$q = "INSERT INTO files VALUES('', '$data', '$type')";
$r = mysql_query($q);

if (!$r) {
	echo "Image not saved to DB!  Reason: " . mysql_error() . "<br /><br />\n\n";
} else {
	echo "Image saved to DB.<br /><br />\n\n";
}
}

$q = "SELECT id FROM files";
$r = mysql_query($q);

while($data = mysql_fetch_array($r)) {
	echo "<a href=\"view.php?id={$data['id']}\">View Image {$data['id']}</a><br />\n";
}

?>

showimage.php

<?php

if (!isset($_GET['id'])) {
	die("Invalid call");
}

$link = mysql_connect("localhost", "USERNAME0", "password");
mysql_select_db("drew010_main", $link);

$q = "SELECT data, type FROM files WHERE id = {$_GET['id']}";
$r = mysql_query($q);

if (!mysql_num_rows($r)) {
	echo "Invalid image id.";
} else {
	$data = mysql_fetch_array($r);

header("Content-Type: {$data['type']}");
echo gzuncompress($data['data']);
}

?>

treeleaf20

Thanks, I will try and make that fit into the stuff I have. One other question though. I will output stuff to the screen and then try and display the image(s) later down the page. Is it ok just to output the headers at the top and then do:

echo gzuncompress($data['data']);

Wherever I want the image to display? Please let me know if that would work.
I appreciate your help.

drew010

it wont work like that, if you did that, you would see some errors saying headers were already sent, and then you would see all the binary data show up in the middle of the page. you would have to call it via an image tag using the showimage.php script if you want to use it in the middle of a page.

treeleaf20

So possibly something like this:

echo "The image is: ";
echo "<img src=\"showimage.php\">";

Do you mean like that?

drew010

yea just like that, including the query string on showimage.php to specify which photo id to show if its still coded like that.

fine

This tutorial shows how to insert retrieve images(or any binary files) from Mysql database.