Displaying Image From MySQL

**unified** · 08-21-2006, 04:08 AM

I have images stored in a field called 'Photo' in a MySql table called 'Authors', and I am trying to figure out a way to display these images from the table. The code I have is:

Code:

// Define the query.
$query = 'SELECT * FROM Authors';
	
if ($r = mysql_query ($query)) { // Run the query.

	// Retrieve and print every record.
	while ($row = mysql_fetch_array ($r)) {
		print "<p><h3>{$row['First_Name']} {$row['Last_Name']}</h3>
		<p>{$row['Biography']}<p/>
                <img src=\"{$row['Photo']}\">
		<a href=\"edit_entry.php?id={$row['blog_id']}\">Edit</a>
		<a href=\"delete_entry.php?id={$row['blog_id']}\">Delete</a>
		</p><hr />\n";
	}

} else { // Query didn't run.
	die ('<p>Could not retrieve the data because: <b>' . mysql_error() . "</b>. The query was $query.</p>");
} // End of query IF.

mysql_close(); // Close the database connection.

Everything works okay, except I get the usual white box with the red x in it because the image will not display.

The 'Photo' field is a LONGBLOB field. Where did I go wrong?

**Twey** · 08-21-2006, 04:29 AM

It sounds as though the image is stored in binary form. In that case, you can't just embed it into the src= attribute, you have to use a separate request:

Code:

if(isset($_GET['photo'])) {
  $r = mysql_fetch_array(mysql_query('SELECT Photo FROM Authors WHERE Last_Name="' . $_GET['first'] . '" AND First_Name="' . $_GET['last'] . '" LIMIT 1;'));
  header('Content-Type: image/png'); // may need altering depending on the image format
  die($r['Photo']);
}

// Define the query.
$query = 'SELECT * FROM Authors';
	
if ($r = mysql_query ($query)) { // Run the query.

	// Retrieve and print every record.
	while ($row = mysql_fetch_array ($r)) {
		print "<p><h3>{$row['First_Name']} {$row['Last_Name']}</h3>
		<p>{$row['Biography']}<p/>
                <img src=\"$PHP_SELF?photo&amp;last={$row['Last_Name']}&amp;first={$row['First_Name']}\">
		<a href=\"edit_entry.php?id={$row['blog_id']}\">Edit</a>
		<a href=\"delete_entry.php?id={$row['blog_id']}\">Delete</a>
		</p><hr />\n";
	}

} else { // Query didn't run.
	die ('<p>Could not retrieve the data because: <b>' . mysql_error() . "</b>. The query was $query.</p>");
} // End of query IF.

mysql_close(); // Close the database connection.

The code block at the top must be run after the connection has been established, but before any output is sent.

**unified** · 08-21-2006, 05:16 AM

Thanks.

I changed to "image/jpeg", but still red x.

As this is much too complicated (no relialble documentation), I will have to explore the technique where the image location is stored rather than the image itself. Storing the image in the database is a more practical and reliable solution since there is a possibility that the actual image file may be moved or deleted, and there will only be about four images total.

I can't believe how difficult this is, and I know others are storing images as well, especially in shopping carts?

**Twey** · 08-21-2006, 03:38 PM

It's not that difficult. Can you give a link to the page? Maybe I can figure out what you're doing wrong. The full source would help too.

**unified** · 08-21-2006, 05:35 PM

Good thing I sneaked home for lunch from work. Thanks for your help.

The test page is ---> www.unified-dsa.com/dbtest2.php , and the source is below.

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
	<title>View My Blog</title>
</head>
<body>
<?php // Script 12.6 - view_blog.php 
// This script retrieves blog entries from the database.

// Address error handling.
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);

// Connect and select.
if ($dbc = @mysql_connect ('MYSQLA19.webcontrolcenter.com', 'kevinchase', 'kevinchase')) {
	
	if (!@mysql_select_db ('tajtene')) {
		die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>');
	}

} else {
	die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');
}
	
if(isset($_GET['photo'])) {
  $r = mysql_fetch_array(mysql_query('SELECT Photo FROM Authors WHERE Last_Name="' . $_GET['first'] . '" AND First_Name="' . $_GET['last'] . '" LIMIT 1;'));
  header('Content-Type: image/jpeg'); // may need altering depending on the image format
  die($r['Photo']);
}

// Define the query.
$query = 'SELECT * FROM Authors';
	
if ($r = mysql_query ($query)) { // Run the query.

	// Retrieve and print every record.
	while ($row = mysql_fetch_array ($r)) {
		print "<p><h3>{$row['First_Name']} {$row['Last_Name']}</h3>
		<p>{$row['Biography']}<p/>
                <img src=\"$PHP_SELF?photo&amp;last={$row['Last_Name']}&amp;first={$row['First_Name']}\">
		<a href=\"edit_entry.php?id={$row['blog_id']}\">Edit</a>
		<a href=\"delete_entry.php?id={$row['blog_id']}\">Delete</a>
		</p><hr />\n";
	}

} else { // Query didn't run.
	die ('<p>Could not retrieve the data because: <b>' . mysql_error() . "</b>. The query was $query.</p>");
} // End of query IF.


mysql_close(); // Close the database connection.

?>
</body>
</html>

**Twey** · 08-21-2006, 06:30 PM

Well, there's your problem for starters. I said that code block had to be after the connection was opened, but before any HTML output was sent. However, you have it after the <head> and opening <body> tag of the HTML document. The easiest way to do this is to move this whole block to above the DOCTYPE:

Code:

// Connect and select.
if ($dbc = @mysql_connect ('MYSQLA19.webcontrolcenter.com', 'xxxxxxx', 'xxxxxxx')) {
	
	if (!@mysql_select_db ('tajtene')) {
		die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>');
	}

} else {
	die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');
}
	
if(isset($_GET['photo'])) {
  $r = mysql_fetch_array(mysql_query('SELECT Photo FROM Authors WHERE Last_Name="' . $_GET['first'] . '" AND First_Name="' . $_GET['last'] . '" LIMIT 1;'));
  header('Content-Type: image/jpeg'); // may need altering depending on the image format
  die($r['Photo']);
}

Remember to wrap it the <?php ?> delimiters, and don't leave any whitespace before the opening <?php.

Please note that I have removed your username and password from the code sample above. I would strongly advise you to do the same with yours (using the "Edit" button in the bottom right-hand corner of your post), and also to change your MySQL password when you have done so.

**unified** · 08-21-2006, 09:36 PM

Ah, still no picture.

I moved the code as instructed, and it is below. The page is actually on a site that I only use for testing, so the username and password are not significant. The entire database will be deleted and another created on a live site.

Code:

<?php
// Connect and select.
if ($dbc = @mysql_connect ('MYSQLA19.webcontrolcenter.com', 'kevinchase', 'kevinchase')) {
	
	if (!@mysql_select_db ('tajtene')) {
		die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>');
	}

} else {
	die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>');
}
	
if(isset($_GET['photo'])) {
  $r = mysql_fetch_array(mysql_query('SELECT Photo FROM Authors WHERE Last_Name="' . $_GET['first'] . '" AND First_Name="' . $_GET['last'] . '" LIMIT 1;'));
  header('Content-Type: image/jpeg'); // may need altering depending on the image format
  die($r['Photo']);
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
	<title>View My Blog</title>
</head>
<body>
<?php // Script 12.6 - view_blog.php 
// This script retrieves blog entries from the database.

// Address error handling.
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);



// Define the query.
$query = 'SELECT * FROM Authors';
	
if ($r = mysql_query ($query)) { // Run the query.

	// Retrieve and print every record.
	while ($row = mysql_fetch_array ($r)) {
		print "<p><h3>{$row['First_Name']} {$row['Last_Name']}</h3>
		<p>{$row['Biography']}<p/>
                <img src=\"$PHP_SELF?photo&amp;last={$row['Last_Name']}&amp;first={$row['First_Name']}\">
		<a href=\"edit_entry.php?id={$row['blog_id']}\">Edit</a>
		<a href=\"delete_entry.php?id={$row['blog_id']}\">Delete</a>
		</p><hr />\n";
	}

} else { // Query didn't run.
	die ('<p>Could not retrieve the data because: <b>' . mysql_error() . "</b>. The query was $query.</p>");
} // End of query IF.


mysql_close(); // Close the database connection.

?>
</body>
</html>

**Twey** · 08-21-2006, 09:56 PM

Could you apply those changes to the test page, please? Also, moving:

Code:

// Address error handling.
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);

to the top as well (above the rest) might help debugging.

**unified** · 08-21-2006, 10:19 PM

Done.

I'm sorry that I actually changed the name of the test page after applying the changes, but I've changed it back.

Still no picture. I'm wondering if something went wrong when I migrated the table from ACCESS? I did check, and I confirmed that there is indeed the picture stored in the PHOTO field. Also, the table only has the one record.

**Twey** · 08-21-2006, 10:28 PM

Hm, well, there's no output. No image, no errors.
Are you sure the query is matching?

Thread: Displaying Image From MySQL

Thread Tools

Search Thread

Displaying Image From MySQL

Bookmarks

Bookmarks

Posting Permissions