...

View Full Version : displaying an image from a MYSql database.



basil60
12-23-2010, 01:36 AM
Hi
I have a small MYSQL database that I'm trying to display an image. I modified some info I found online. It appears that the image is being inserted in the database - it's just my PHP to display it that's gone wrong.
I have a sign_up.html, sign_up.php, display_users.php, connect.php and image.php (which is where I think the error is). When I run display_users.php, it shows a placeholder for the image.

I've included some of the files - I'd appreciate any advice.

Also, while I started this project with the intention of saving the image as a BLOB, the info I found saved it as LONG TEXT. why is that?



============================================
sign_up.html
============================================
<form action="sign_up.php" method="POST" enctype="multipart/form-data">


<br />Please enter your preferred username: <br />
<input type="text" name="username" size="25" maxlength="50" value=""><br /><br />
Please enter first name: <br />
<input type="text" name="f_name" size="25" maxlength="55" value=""><br /><br />
Please enter last name: <br />
<input type="text" name="l_name" size="25" maxlength="55" value=""><br /><br />
Please enter location of your favicon: <br />
<input type="file" name="favicon" accept="image/jpeg">
<input name="MAX_FILE_SIZE" value="102400" type="hidden">

<br />


<input type="submit" value="Submit Form">
</form>
============================================
sign_up.php
============================================
require("connect.php");

//Post Variables
$username = mysql_real_escape_string($_POST['username']);
$first_name= mysql_real_escape_string($_POST['f_name']);
$last_name = mysql_real_escape_string($_POST['l_name']);
$favicon = mysql_real_escape_string($_POST['favicon']);
$id = mysql_real_escape_string($_POST['id']);

$image = basename( $_FILES['favicon']['name']);
$sizeimage=$_FILES['favicon']['size'];
if(move_uploaded_file($_FILES['favicon']['tmp_name'], $image))
{
$handle = fopen($image,'r');
$file_content = fread($handle,$_FILES['favicon']['size']);
fclose($handle);
$encodedimg = chunk_split(base64_encode($file_content));

//Update Table
mysql_query("INSERT
INTO user
( id,
username
, first_name
, last_name
, favicon
)
VALUES
( '$id',
'$username'
, '$first_name'
, '$last_name'
, '$encodedimg'
) ;
");
unlink($image);
$success=1;
}
else
{
$reason.= "<p>There was an error uploading the file, please try again!</p>";
}
============================================
display_users.php
============================================
require ("connect.php");

// select our database
mysql_select_db("basil_bookmark") or die(mysql_error());

//Display Table

if (!$result)
{
die('<p>Error performing query: ' . mysql_error() . '</p>');
}

echo "<table border='1' width='80%'cellpadding='3' cellspacing='0'>";
echo "<tr>";
echo "<td align='center' bgcolor='#FF3399'><strong>Username</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>First Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Last Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Favicon</strong></td>";

echo "</tr>";


while ($row = mysql_fetch_array($result)) {

echo "<tr>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["username"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["first_name"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["last_name"] ."</strong></td>";

echo "<td bgcolor='#FFccff' align='center'><strong><img src='http://basil.x10.mx/image.php?id='".$row['id']. "alt='favicon'></img></strong></td>";
echo "</tr>";
============================================
image.php
============================================
include('connect.php');
$id= $_REQUEST["id"];
$q = "SELECT ('favicon') FROM 'user' where id=".$id;
$result = mysql_query($q);
while ($row = mysql_fetch_array($result) ) {
$img = $row['favicon'];
}
echo base64_decode($img);
}

mlseim
12-23-2010, 02:45 AM
I think you need to have the content-type defined.
In your case, jpeg ... instead of x-icon,gif, or png?
You say "favicon", but you aren't using .ico ?



image.php
============================================
include('connect.php');
$id= $_REQUEST["id"];
$q = "SELECT ('favicon') FROM 'user' where id=".$id;
$result = mysql_query($q);
while ($row = mysql_fetch_array($result) ) {
$img = $row['favicon'];
}

header('Content-Type: image/jpeg');
// I'm not sure which one of these to use...
echo base64_decode($img);
//echo $img;
}

basil60
12-23-2010, 03:02 AM
Thanks. Favicon probably should have been avatar - but I guess that's irrelevant.

The example I used - here (http://leoslab.net/how-to-save-image-using-php-mysql/) - didn't need the datatype - and I verified this on my own system.

That is, unless my interpretation has gone awry.

*Using FF - I can see for the image placeholders, the path is - http://basil.x10.mx/bookmark/image.php?id=
That suggest to me that image.php isn't successfully appending the image id to each image. But I can't see why!

mlseim
12-23-2010, 12:24 PM
I think I know why ...

This line:
$id= $_REQUEST["id"];

There is a risk to using $_REQUEST.
$_REQUEST has changed a bit during the last PHP revisions.
I'll bet that with your PHP version, in the config, it has been disabled along with register_global (as a default).

Use this instead ...
$id= $_GET["id"];

============================

If you are using a variable with a URL and you also sometimes use it with a <form method="post>,
you have to determine whether the variable is GET or POST. In the past, you could have used
REQUEST to cover either of them.

If $_REQUEST is disabled for you ...



<?php

// The "old way" of doing it...
// covers both GET and POST ...
$action=$_REQUEST['action'];

// If REQUEST has been disabled, check for either one ...
if(isset($_GET['action']){
$action=$_GET['action'];
}
if(isset($_POST['action']){
$action=$_POST['action'];
}

?>




.

basil60
12-23-2010, 10:32 PM
Thank you. I changed that, but without success. The example I used from the web uses _REQUEST, and that tutorial does work.

It's only when I try to adapt it into my own implementation that it fails - operator error!!!!!

Something I've done is preventing the id being appended to the <img src ..> tag, giving me
http://basil.x10.mx/bookmark/image.php?id=.

I tried comparing tags from mine to the tutorial, without success. The only other thing I can see that could be is the way I tried to concatenate the elements.

echo "<td bgcolor='#FFccff' align='center'><strong><img src='http://basil.x10.mx/bookmark/image.php?id='".$row['id']. "alt='favicon'></img></strong></td>";
Any other advice might help to eliminate more grey hairs!!! :(

mlseim
12-23-2010, 11:21 PM
aha ... I see the problem!

Missing the single quote ... see the red single quote ...


echo "<td bgcolor='#FFccff' align='center'><strong><img src='http://basil.x10.mx/bookmark/image.php?id='".$row['id']."' alt='favicon'></img></strong></td>";


alternative method:

echo "<td bgcolor='#FFccff' align='center'><strong><img src='http://basil.x10.mx/bookmark/image.php?id='{$row['id']}' alt='favicon'></img></strong></td>";


.

basil60
12-24-2010, 05:30 AM
Thanks for taking the time.
When I use either of the methods you've suggested, I see the word favicon only (I'm guessing the alt tag).
If I leave it the way I had it, it's a broken image. There must be some other operator error hidden away. :(

mlseim
12-24-2010, 04:05 PM
oops ....

I still had it wrong ... try this ....

echo "<td bgcolor='#FFccff' align='center'><img src='http://basil.x10.mx/bookmark/image.php?id=".$row['id']."' alt='favicon' /></td>";

DJCMBear
12-24-2010, 04:55 PM
Why not make it easier to understand and put the PHP variable inside the string using these brackets {} like this.



echo "<td bgcolor='#FFccff' align='center'><img src='".
"http://basil.x10.mx/bookmark/image.php?id={$row['id']}' alt='favicon' /></td>";


You can also use double quotes with a backslash like so


echo "<td bgcolor=\"#FFccff\" align=\"center\"><img src=\"".
"http://basil.x10.mx/bookmark/image.php?id={$row['id']}\" alt=\"favicon\" /></td>";

basil60
12-26-2010, 10:35 PM
Thanks - but again, if I use either "bits" of code supplied, it shows the word (alt tag?) favicon, rather than an image or even broken image.

I'm reposting my code, in case I've accidentally altered something. I've been through, and double checked what I've done, but I could easily be overlooking some glaring error.


=====================================================
image.php
<?php
include('connect.php');
$id= $_GET['id'];
$q = "SELECT ('favicon') FROM 'user' where id = " . $id;
$result = mysql_query($q);
while ($row = mysql_fetch_array($result) ) {
$img = $row['favicon'];
}
echo base64_decode($img);
=========================================================
display_users.php

require ("connect.php");

// select our database
mysql_select_db("basil_bookmark") or die(mysql_error());
$result = mysql_query("SELECT * FROM user");
//Display Table

if (!$result)
{
die('<p>Error performing query: ' . mysql_error() . '</p>');
}

echo "<table border='1' width='80%'cellpadding='3' cellspacing='0'>";
echo "<tr>";
echo "<td align='center' bgcolor='#FF3399'><strong>Username</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>First Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Last Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Favicon</strong></td>";

echo "</tr>";


while ($row = mysql_fetch_array($result)) {

echo "<tr>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["username"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["first_name"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["last_name"] ."</strong></td>";

echo "<td bgcolor='#FFccff' align='center'><img src='http://basil.x10.mx/bookmark/image.php?id=".$row['id']."' alt='favicon' /></td>";//echo "<td bgcolor='#FFccff' align='center'><strong>". $content ."</strong></td>";
echo "</tr>";

}

echo ("\n</table>\n");
print "There are " . mysql_num_rows($result) . " rows in the Bookmarks database\n";

?>


?>

mlseim
12-27-2010, 01:43 AM
When I go to this link by itself ...

http://basil.x10.mx/bookmark/image.php?id=2

I get a query failure on line 6 ...

Line 6 is this:
$q = "SELECT ('favicon') FROM 'user' where id = " . $id;

Try this:

$q = "SELECT favicon FROM user where id = '$id' ";



.

basil60
12-27-2010, 11:58 AM
Thanks.

When I alter that, and call the line as you did, I see a whole bunch of code. (http://basil.x10.mx/bookmark/image.php?id=9)
If I then call the display_users.php file, it appends a number to the image - ie Src=http://basil.x10.mx/bookmark/image.php?id=9, but still you can only see the alt tag.

I think that narrows down the problem. To insert the images into the database, I used:


sign_up.php
=========================================================
<?
require("connect.php");

//Post Variables
$username = mysql_real_escape_string($_POST['username']);
$first_name= mysql_real_escape_string($_POST['f_name']);
$last_name = mysql_real_escape_string($_POST['l_name']);
$favicon = mysql_real_escape_string($_POST['favicon']);
$id = mysql_real_escape_string($_POST['id']);

$image = basename( $_FILES['favicon']['name']);
$sizeimage=$_FILES['favicon']['size'];
if(move_uploaded_file($_FILES['favicon']['tmp_name'], $image))
{
$handle = fopen($image,'r');
$file_content = fread($handle,$_FILES['favicon']['size']);
fclose($handle);
$encodedimg = chunk_split(base64_encode($file_content));

//Update Table
mysql_query("INSERT
INTO user
( id,
username
, first_name
, last_name
, favicon
)
VALUES
( '$id',
'$username'
, '$first_name'
, '$last_name'
, '$encodedimg'
) ;
");
unlink($image);
$success=1;
}
else
{
$reason = "<p>There was an error uploading the file, please try again!</p>";
}

print("Thank you <br />Your signup is complete.");
print ("<p><a href='http://basil.x10.mx/bookmark/'>Home</a></p>");
// Close our MySQL Link

?>

The info I found on the web, did it this way, and I tried to modify it.
The oriiginal insert looked like this.


<?php
include('config.php');
$image = basename( $_FILES['imagefile']['name']);
$sizeimage=$_FILES['imagefile']['size'];
if(move_uploaded_file($_FILES['imagefile']['tmp_name'], $image))
{
$handle = fopen($image,'r');
$file_content = fread($handle,$_FILES['imagefile']['size']);
fclose($handle);
$encodedimg = chunk_split(base64_encode($file_content));
$sql = "INSERT INTO imagesdata SET image='$encodedimg'";
mysql_query($sql);
unlink($image);
$success=1;
}
else
{
$reason.= "<p>There was an error uploading the file, please try again!</p>";
}
?>
<html>
<body>
<? if($success==1) {?>
<p>Upload success.</p>
<a href="javascript:history. go(-1)">Back</a>
<? }
else {echo $reason;}?>
</body>
</html>

mlseim
12-27-2010, 05:25 PM
How about if you forget about the "base64_encode" stuff you're using ...

In examples I've seen with BLOB, that isn't used.

Like this example ...
http://www.anyexample.com/programming/php/php_mysql_example__image_gallery_(blob_storage).xml

Like you mentioned earlier ... your problem is most likely in how you defined the column.
It has to be defined as a BLOB of some kind... in that example it's:
data` mediumblob NOT NULL,

basil60
12-29-2010, 12:47 AM
Thank you. I did work through the thread you gave me. But couldn't apply it to my scenario. I suppose I could have just done a copy and paste, but I'm not sure what I would have learnt. Trawled the web and went back to this (http://bytes.com/topic/mysql/answers/643396-how-store-images-binary-data-mysql-database-using-blob-fields)

And I think I'm a lot closer than I was. When I run display_users.php, I get a whole heap of code now. See here (http://basil.x10.mx/bookmark4/display_users.php). I'm guessing that's cos of my header stuff, but I'm not sure.
Here's what my display_users.php looks like now.


<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
require ("connect.php");

// select our database
mysql_select_db("basil_bookmark") or die(mysql_error());
$result = mysql_query("SELECT * FROM user ORDER BY id DESC");
//$content = "<img src=".$domain."/image2.php?id=".$row['id']."alt='favicon'></img>";
//Display Table
header("Content-type: img/". $ROW['image_type']);
if (!$result)
{
die('<p>Error performing query: ' . mysql_error() . '</p>');
}

echo "<table border='1' width='80%'cellpadding='3' cellspacing='0'>";
echo "<tr>";
echo "<td align='center' bgcolor='#FF3399'><strong>Username</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>First Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Last Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Favicon</strong></td>";

echo "</tr>";


while ($row = mysql_fetch_array($result)) {

echo "<tr>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["username"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["first_name"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["last_name"] ."</strong></td>";


echo "<td bgcolor='#FFccff' align='center'>".$row["image_data"]. "</td>";

echo "</tr>";

}

echo ("\n</table>\n");
print "There are " . mysql_num_rows($result) . " rows in the Bookmarks database\n";

?>
========================================================
and sign_up.php just in case

require("connect.php");

//Post Variables
$username = mysql_real_escape_string($_POST['username']);
$first_name= mysql_real_escape_string($_POST['f_name']);
$last_name = mysql_real_escape_string($_POST['l_name']);
//$image_data = $_POST['file'];

//read image bytes in to the data variable

$file = fopen($_FILES['file']['tmp_name'], "r");
$data = addslashes(fread($file, filesize($_FILES['file']['tmp_name'])));
fclose($file);

//Update Table
mysql_query("INSERT
INTO user
( username
, first_name
, last_name
, image_data
,image_type
)
VALUES
( '$username'
, '$first_name'
, '$last_name'
, '$data'
,'jpg'
) ;
");


print("Thank you <br />Your signup is complete.");
print ("<p><a href='http://basil.x10.mx/bookmark/'>Home</a></p>");
// Close our MySQL Link

?>

mlseim
12-29-2010, 01:49 AM
You can't mix header types.
By echoing text, you've already created a header.

Try this ... using the <img> tag ...
Here is the script modified ....


<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
require ("connect.php");

// select our database
mysql_select_db("basil_bookmark") or die(mysql_error());
$result = mysql_query("SELECT * FROM user ORDER BY id DESC");
//$content = "<img src=".$domain."/image2.php?id=".$row['id']."alt='favicon'></img>";
//Display Table
//header("Content-type: img/". $ROW['image_type']);
if (!$result)
{
die('<p>Error performing query: ' . mysql_error() . '</p>');
}

echo "<table border='1' width='80%'cellpadding='3' cellspacing='0'>";
echo "<tr>";
echo "<td align='center' bgcolor='#FF3399'><strong>Username</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>First Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Last Name</strong></td>";
echo "<td align='center' bgcolor='#FF3399'><strong>Favicon</strong></td>";

echo "</tr>";


while ($row = mysql_fetch_array($result)) {

echo "<tr>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["username"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["first_name"] ."</strong></td>";
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["last_name"] ."</strong></td>";


//echo "<td bgcolor='#FFccff' align='center'>".$row["image_data"]. "</td>";

echo "<td bgcolor='#FFccff' align='center'><img src='{$row['image_data']}' /></td>";



echo "</tr>";

}

echo ("\n</table>\n");
print "There are " . mysql_num_rows($result) . " rows in the Bookmarks database\n";

?>
========================================================
and sign_up.php just in case

require("connect.php");

//Post Variables
$username = mysql_real_escape_string($_POST['username']);
$first_name= mysql_real_escape_string($_POST['f_name']);
$last_name = mysql_real_escape_string($_POST['l_name']);
//$image_data = $_POST['file'];

//read image bytes in to the data variable

$file = fopen($_FILES['file']['tmp_name'], "r");
$data = addslashes(fread($file, filesize($_FILES['file']['tmp_name'])));
fclose($file);

//Update Table
mysql_query("INSERT
INTO user
( username
, first_name
, last_name
, image_data
,image_type
)
VALUES
( '$username'
, '$first_name'
, '$last_name'
, '$data'
,'jpg'
) ;
");


print("Thank you <br />Your signup is complete.");
print ("<p><a href='http://basil.x10.mx/bookmark/'>Home</a></p>");
// Close our MySQL Link

?>



If that still doesn't work, I have one more thing to try.



.

basil60
12-29-2010, 11:09 PM
Again thanks. I have been able to get the output to look like this:
Src=http://basil.x10.mx/bookmark4/getdata.php?id=24 favicon, when I use the web developer toolbar in FF.

But if I call http://basil.x10.mx/bookmark4/getdata.php?id=20 I get a blank page.

The getdata.php


<?php
require ("connect.php");

$id ='id';

if($id) {



$query = "select image_data,image_type from user where id=$id";
$result = @MYSQL_QUERY($query);

$data = @MYSQL_RESULT($result,0,"image_data");
$type = @MYSQL_RESULT($result,0,"image_type");

Header( "Content-type: $type");
echo $data;

};
?>

seemed to be a better way to separate the calls I was making. It seems so close, but ....
The only obvious area I can see where I can see I may have got it wrong is in the collation "type" for image type. Instead of "utf8_unicode", I have "utf8_unicode_ci", but I couldn't change that?

mlseim
12-30-2010, 02:12 AM
How about doing this ...

Use the database to save the filename, but store the images in a directory ...
don't use the BLOB method. I think you'll find it solves your problem(s).

The other option is to have a separate script to render the image from
an "id" ... but that means it has to execute that in a loop. That seems to be
a large CPU usage , (querying the database in a loop).

My suggestion is to quit using MySQL to store the images, and use a directory instead.
You can save each image as a timestamp ...
example:
1293631200.jpg

No two would ever be the same name, and you can serve them without
giving-away the location of the file.

basil60
12-31-2010, 11:00 PM
Eventually solved it, as you suggested using the link, rather than storing as a BLOB. Hopefully I've learnt a little along the way!!

One hopefully last question, how do I get the timestamp alone in front of the extension. I found this -
$file = $timestamp.$_FILES['file']['name'];
$filename = strtolower($file);

but it just appended the timestamp before the file name. Couldn't find a way around that!!???

mlseim
01-01-2011, 12:03 AM
Follow this method to extract extension and then make a new filename.
http://php.about.com/od/advancedphp/ss/rename_upload.htm

That explains it pretty good.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum