[RESOLVED] Images from database won't show up

[jredwilli]

Hi,
I'm trying to make a photo gallery that paginates and shows images from a blob database, and each image links to individual pages to view them larger. I uploaded some images to the database, but when I go to view them, all that shows is the box where the image would be, with a link that is the name of the selected image.

I want to show each image in the db above each image name, but can't figure out how to get the correct "img src" of each image.

Here's the block of code I think the problem lies in, in red is the link and img src tag:

// Initialize the variable.
$first = TRUE;

// Make the query.
$query = "SELECT pic_id, name FROM upload ORDER BY pic_id DESC LIMIT $start, $display";		
$result = @mysql_query ($query); // Run the query.

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

	// If this is the first record, create the table header.
	if ($first) {

		// start the table for images
		echo '<table align="center" width="90%" cellspacing="5" cellpadding="3" border="0">
			<tr>
				<td valign="top" align="center">
		';
		$first = FALSE; // at least one record has been returned.			

	} // End of IF.

	// Display each record.
	echo '<div id="showimg" style="width: 130px; z-index: 5; display:block; text-align: center">
		[COLOR="Red"]<a href="graphics.php?pic_id="' . $row['pic_id'] . '&name=' . $row['name'] . '">
			<img src="upload/' . $row['pic_id'] . '" alt="' . $row['name'] . '" width="130px" /><br /></a>[/COLOR]

		</div>
	';

} // end of while loop

if ($first) {
	echo '<div id="showimg" style="width: 130px; z-index: 5; overflow: visible; text-align: center">
		Sorry, there is no images in the database for this page yet.  Please check back later today, as I am still working on uploading and displaying my work examples.
		</div>
		';
} else {	
	// close content table
	echo '</td>
	</tr>
	<tr>
		<td>
	';
}

[jredwilli]

Here is the link for the page that shows what I mean.
http://jredwilly.com/portfolio/graphics.php

Incase I didn't explain the problem clearly...

[bradgrafelman]

Well.. where are those images stored?

EDIT: Right, blob field. Anyway... what you'll want to do point the image source to a PHP script that retrieves the blob field from the database using the id given in the URL, outputs the appropriate Content-Type header, and then outputs the blob data.

[jredwilli]

Ok, so the img src would be something like for example:

img src="PHP script.php"

right?

if so, then what do I do to retrieve the blob field exactly, and then how would the Content-Type header be sent?

This is the database table I am storing the images in.

TABLE upload (
pic_id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL ,
type VARCHAR(30) NOT NULL ,
size INT(11) NOT NULL,
content LONGBLOB BINARY NOT NULL)
PRIMARY KEY (pic_id)
);

[bradgrafelman]

Well, you'd want to append the id of the current image in the loop (e.g. viewimage.php?id=2) so that it could run a query against the DB:

SELECT content FROM upload WHERE pic_id = 2 LIMIT 1

and then output the data:

header('Content-Type: image/jpeg');
echo $content;

[jredwilli]

I have to put the include('header.php'); after the part that has (viewimage.php?id=2), right?

Since the viewimage.php file will have the header(Content-Type: image/jpeg) in it?

I made a file called pic.php that has

SELECT content FROM upload WHERE pic_id = 2 LIMIT 1

header('Content-Type: image/jpeg');
echo $content;

and changed the img src in the other file to "pic.php?id=2" and that didn't work, and am not sure if it has something to do with my the header.php include...

[bradgrafelman]

What's in header.php ? I shouldn't think you'd need to include it at all...

Also, you'll actually have to execute the SQL query and retrieve the result, I was just writing pseudo-code above - I won't do all of the work...

[jredwilli]

It still doesn't work, I am not sure if I am retrieving the blob correctly or not. This is what I have changed the img src in the "view.php" file to:

// Make the query.
$query = "SELECT pic_id, name FROM upload ORDER BY pic_id DESC LIMIT $start, $display";		
$result = @mysql_query ($query); // Run the query.

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

           $pid = $row['pic_id'];

	// If this is the first record, create the table header.
	if ($first) {
		// start the table for images
		echo '<table align="center" width="90%" cellspacing="5" cellpadding="3" border="0">
			<tr>
				<td valign="top" align="center">
		';
		$first = FALSE; // at least one record has been returned.							
	} // End of IF.

	// Display each record.
	echo '<div id="showimg" style="width: 130px; z-index: 5; display:block; text-align: center">
[B]		<a href="graphics.php?id=' . $i . '&view=' . $pid . '">
			<img src="pic.php?view=' . $pid . '" alt="' . $row['name'] . '" width="130px" /><br /></a>
[/B]		</div>
	';

} // end of while loop

and this is what I have in the "pic.php" file:

<?php

include ('inc/connect.php');


$query = "SELECT content FROM upload WHERE pic_id = '$pid'";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$content = $row['content'];

header('Content-Type: image/jpeg');
echo $content; 

?>

I want to show all of the images in the table, so how do I get this to work?

SELECT content FROM upload WHERE pic_id = '$pid'

[jredwilli]

Ok, I got one image to show up, but I want to show all the images in the table, and paginate them so only a specific number of images(10) will show, making multiple pages if the number of images in the database exceeds the 10 image limit.

[jredwilli]

Ok, I was able to figure it all out. Thank you for all your help.

[j_70]

It is good forum etiquette to share your solution, not just tell us you have solved the problem. :bemused:

[jredwilli]

Sorry, here is what I did for the "graphics.php",

// connect to database
include ('inc/connect.php');

// Number of records to show per page:
$display = 6;

// Determine how many pages there are. 
if (isset($_GET['np'])) { // is already known.
	$num_pages = $_GET['np'];	
} else { // Need to determine.

// Count the number of records
$query = "SELECT COUNT(*) FROM graphics ORDER BY pic_id DESC";
$result = @mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$num_records = $row[0];

  // Calculate number of pages.
   if ($num_records > $display) {
      $num_pages = ceil ($num_records/$display);
   } else {
      $num_pages = 1;
   }

} // End of numpag IF.

// Determine where in the database to start returning results.
if (isset($_GET['img'])) {
   $start = $_GET['img'];
} else {
   $start = 0;
}

// start main content div
echo '<div id="otherpages">';

// Make the query.
$query = "SELECT pic_id, title FROM graphics ORDER BY pic_id DESC LIMIT $start, $display";		
$result = @mysql_query ($query); // Run the query.
if (mysql_num_rows($result) > 0) {

// start the table for images
echo '<table width="100%" border="0" align="center">
		<tr>
	    	<td align="center">
		';

while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
	$id = $row['pic_id'];

	// Display each record.
	echo '<table border="0" align="left" cellspacing="0" cellpadding="3" width="220px">
		     <tr>
		     	<td valign="top" align="center">
					<a href="show_pic.php?id=' . $id . '">
						<img src="pic.php?id=' . $id . '" alt="' . $row['title'] . '" width="220px" height="180px" /></a>
				</td>
		    </tr>
	    </table>
		   ';

} // end of while loop
echo '</td>
	</tr>
</table>
	';

}
mysql_free_result ($result); // Free up the resources.	
mysql_close(); // Close the database connection.


// close the "otherpages" div
echo '</div>';

// Make the links to other pages, if necessary.
if ($num_pages > 1) {

// open page links div
echo '<div id="pagelinks">';

// Determine what page the script is on.	
$current_page = ($start/$display) + 1;

// If it's not the first page, make a Previous button.
if ($current_page != 1) {
	echo '<a href="graphics.php?img=' . ($start - $display) . '&amp;np=' . $num_pages . '">&lt;&lt; &nbsp;</a>';
}

// Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
	if ($i != $current_page) {
		echo '<a href="graphics.php?img=' . (($display * ($i - 1))) . '&amp;np=' . $num_pages . '">' . $i . '</a> ';
	} else {
		echo $i . ' ';
	}
}

// If it's not the last page, make a Next button.
if ($current_page != $num_pages) {
	echo '<a href="graphics.php?img=' . ($start + $display) . '&amp;np=' . $num_pages . '">&nbsp; &gt;&gt;</a>';
}

// close pagelinks  div
echo '</div>';

} // End of IF links section.

And for the img src of the pictures here is the "pic.php" code:

header('Content-Type: image/jpeg');

//connect to database
include ('inc/connect.php');


$query = "SELECT content FROM graphics where pic_id='$id'";
$result = mysql_query($query) or die(mysql_error());

// need to fetch the array to get this line to work:
$row = mysql_fetch_assoc($result);

echo $row['content']; 

[jredwilli]

Also, for the individual pages each picture is linked to, this is the code that displays the larger image with more information of the image "show_pic.php":

if (isset($_GET['id'])) {
   $id = (int) $_GET['id'];
} elseif (isset($_POST['id'])) {
   $id = (int) $_POST['id'];
} else {
   $id = 0;
}


if ($id <= 0) { // Kill the script.
  echo '<p class="error">This page has been accessed in error.</p>';
  include ('inc/footer.php');
  exit();
}  

require_once ('inc/connect.php');

echo '<div id="otherpages">';

$id = $_GET['id'];
$query = "SELECT pic_id,title, description, name FROM graphics WHERE pic_id = '$id'";
$result = mysql_query ($query) or die ("Error in query: $query. " . mysql_error());
$row = mysql_fetch_array ($result, MYSQL_ASSOC);
$id = $row['pic_id'];
$title = $row['title'];
$des = $row['description'];

if ($id) {
  echo '<div id="showd">
  			<p style="font-size: 14px;"><strong>' . $title . '</strong></p>
			<p style="font-size: 14px;">' . $des . '</p>

		<a href="javascript: history.back(-1);"><span class="sitelink">&lt;&lt; Go Back</span></a>
	</div>

	<div id="showi">
		<a href="javascript: history.back(-1);">
		<img src="pic.php?id=' . $id . '" alt="' . $title . '" width="400px" height="340px" class="xhtml" /></a>
	</div>
	';

} else {
	echo '<p class="error">The image you requested was not found in the database.</p>';
}

echo '</div>';

mysql_close();