retrieving several blobs 1

[craigey]

Hi,

I'm trying to retrieve several images (stored as blobs in mysql). I can display any of the images individually from the database.
However when I try to display all the images as scaled down thumbnails, I only get the binary data displayed.
I know this has to do with the header content, but when I try to use

Header( "Content-type: $type");  //$type is the mime field in db

I just end up getting headers already sent.

I've got different formats of images such as gif & jpeg.

Has anyone got any way of displaying all the images in the DB as thumbnails, in the same way as many of the image galleries display their thumbnails?

Thanks in advance - hope this is a reletively straight forward request!!

 

[jpadie]

i think you need to make each img tag point its src to a php page and pass the id in.
the src php page parses the id and looks up the db. you then send your headers. do not send any html at all. not even a blank line. then echo out the value of the db field

my experience with gallery applications is that the images (including all rescaled images) are always stored in the filesystem and the db (if any) just contains pointers. i think this is a more sensible structure.

 

[craigey]

Thanks for that. I think that's pretty much what I'm doing.
I understand the filesystem / BLOB argument, but as this is a small website with probably no more then 30 pictures. I doubt there will be too big a performance loss & it's something I'd like to learn to do.

My code looks something like:

<?php

$limit = 3;
$page =(int)$_GET['i'];
//set the default page as one
if ($page == null || $page < 1) {
$page = 1;
}
$page--;
$page*=$limit;


$dbServer = "localhost";
$dbDatabase = "db1";
$dbUser = "myusername";
$dbPass = "mypassword";

$sConn = mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");
$dConn = mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");


$get_images = mysql_query("SELECT * FROM `images` ORDER BY `id` ASC LIMIT ".$page.",".$limit."");

$image_count = mysql_num_rows($get_images); //count the images
if ($image_count < 1) {
echo "Sorry, no images to display";
} else {
while ($image_data = mysql_fetch_row($get_images)) {
echo $image_data[0]." <img src=".$image_data[1]." alt=\"".$image_data[3]."\" /><br /><br />";
//echo $image_data[0]." <img src=".$image_data[1]." alt=\"".$image_data[3]."\" /><br /><br />";
}


$total_count = mysql_num_rows(mysql_query("SELECT * FROM `images`"));
//display the progress
$s_1 = $page + 1;
$s_2 = ($s_1 + $image_count) - 1;
echo "Showing ".$s_1."-".$s_2." of ".$total_count."<br />";


$cur_page = ceil($s_1 / 15);
if ($cur_page == 1) {
echo "<br />Pages: <strong>1</strong>";
} else {
echo "<br />Pages: <a href=\"phpshowfilepaging.php?i=1\">1</a>";
}
$page_count = ceil($total_count / 15) + 1;
$counter = 2;
while ($counter < $page_count) {
$sp = (15 * ($counter - 1));
if ($cur_page == $counter) {
echo " <strong>".$counter."</strong>";
} else {
echo " <a href=\"phpshowfilepaging.php?i=".$counter."\">".$counter."</a>";
}
$counter++;
}
}
?>

 

[jpadie]

i'm not sure of your db schema so can't tell whether the "<img" tags are right. they don't look quite right though as you have not included a php file in the src and it would not be very efficient to store the entire path in the database for each image when it would be the same script you are calling.

but let's say the database of images holds the image and the image id and a description and mimetype.

in your general page you would have something like:

$sql = "select imageid, imagedescription from images limit 3");
$result = mysql_query($sql);
echo "<table>";
while ($row=mysql_fetch_assoc($result)):
?>
<tr><td><?=$row['imagedescription']?></td>
<td><img src="getimage.php?id=<?=$row['imageid']?>"></td></tr>
<?
endwhile;
</table>

and your php script called getimage.php would be

if(!isset($_GET['id'])):
die();
endif;
$sql = "select imagedata, mimetype from images where id='".trim($_GET['id']."'";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
header ($row['mimetype']);
echo $row['imagedata'];

 

[craigey]

Thanks will give it a shot later & let you know how I get on

 

[craigey]

That's magic. Thanks
Now just got to get this thing paging properly!

 

[jpadie]

there is a good paging code FAQ in the FAQ section of this forum. it does a first, previous, next, last link rather than provide links for each page.

i have posted in the past, a complete solution for a per page link set.

 

[craigey]

Thanks. I got the paging sorted out. The script works fine on my localhost, but when I upload the files. I keep getting an "internal server error 500" & the images wont display.

The webaddress is

http://www.cw-product.co.uk/pullimages.php

I've checked the permissions n the files & have set them to 755. If anyone wants to have a look & possibly give me some ideas.

Thanks

 

[jpadie]

what does your server error log report?
can you post the code of the getimage.php file

 

[craigey]

I haven't been able to get the server error log yet. I think I'd have to get on to the webhost to get the logs.

anyway, here's the pullimages.php file:

<?php
    global $page;
    global $offset;
    global $limit;

include "inc/inc_dbcon.php";

$sConn = @mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");
$dConn = @mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");

    $limit = 4;
    $result = mysql_query("select count(*) from images");
    $total = mysql_result($result, 0, 0);

 if(isset($_GET['page']))
{
    $page = (int) $_GET['page'];
}

if ($page == null || $page < 1) {
$page = (int) 1;
}

   include "inc/pager.class";

    $pager  = Pager::getPagerData($total, $limit, $page);
    $offset = $pager->offset;
    $limit  = $pager->limit;
    $page   = $pager->page;


$sql = "select id,description,type from images order by id limit $offset, $limit";

$result = mysql_query($sql);
echo "<table>";
Border=\1\>";
echo "<tr><td><center>Number</center></td><td><center>Image</center></td><td><center>Description</center></td></tr>";

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

?>

<tr>
<td><center><?=$row['id']?></center></td>
<td><center><A Href="getimage.php?id=<?=$row['id']?>" target="_blank"><img src="getimage.php?id=<?=$row['id']?>" height="100" width="100" alt="<?=$row['description']?>"></center></a></td>
<td><center><?=$row['description']?></center></td>
</tr>
<?
};
?>
</table>
<?
  //PAGES OUTPUT
echo "<p>";
// output paging system (could also do it before we output the page content)
if ($page == 1) // this is the first page - there is no previous page
echo "Previous";
else // not the first page, link to the previous page
echo "<a href=\"pullimages.php?page=" . ($page - 1) . "\">Previous</a>";

for ($i = 1; $i <= $pager->numPages; $i++) {
echo " | ";
if ($i == $pager->page)
echo "<b>Page $i</b>";
else
echo "<a href=\"pullimages.php?page=$i\">Page $i</a>";
}
echo " | ";

if ($page == $pager->numPages) // this is the last page - there is no next page
echo "Next";
else // not the last page, link to the next page
echo "<a href=\"pullimages.php?page=" . ($page + 1) . "\">Next</a>";
echo "</p>";

?>

 

[jpadie]

nope - just want the getimage.php file, please!

 

[craigey]

D'Oh

<?php

if(!isset($_GET['id'])):
die();
endif;


$id = (int) $_GET['id'];

include "inc/inc_dbcon.php";

$sConn = @mysql_connect($dbServer, $dbUser, $dbPass)
or die("Couldn't connect to database server");
$dConn = @mysql_select_db($dbDatabase, $sConn)
or die("Couldn't connect to database $dbDatabase");

// $sql = "select content, type from images where id='".trim($_GET['id']."'";
// $sql = "select  images where id='"$_GET['id']"'";
$sql = "SELECT content, type FROM `images` WHERE `id`='$id'";

$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
header ($row['type']);
echo $row['content'];

?>

 

[jpadie]

$id = (int) $_GET['id'];

you were having a bad hair day with this line. try instead

$id = intval(trim($_GET['id']));

 

[craigey]

I have lots of those days.

I've made the code change, but still getting the error. I've just downloaded the logs & I'm getting a problem with the header.

malformed header from script. Bad header=image/pjpeg: getimage.php,
malformed header from script. Bad header=image/gif: getimage.php,

 

[jpadie]

try

header('Content-type: '.$row['type']);

instead of the current header line.

 

[jpadie]

ok. so you're code now works.

but you will find the your page loads really slowly the more images you put in. why? because you are not loading thumbnails but the full image sample - both on the thumbnail page and the full page. simply scaling down the x and y sizes of the image does not change its byte size and because you are forcing to a particular X * Y you will find that images skew themselves.

It's time, imho, for you to look at image manipulation. having gone through the pain of getting blob in db storage working correctly i'd guess that you will rapidly conclude that it is better not to store your images in the db but instead in the filesystem.

there's lots of good stuff on thumbnail generation and using the gd/imagemagic libraries out there.

have fun!

 

[craigey]

Magic. That's got it. I was just about to start searching for correct header formatting.

The image resizing was just a temporary thing. I had previously seen tutorials on using GD to resize & watermark the images etc, that I thought might be useful to play with.

Thanks for all your help.

 

[jpadie]

no worries.