Log in

View Full Version : 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:


// 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:
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.


<!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:
// 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.


<?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:
// 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?
unified
08-22-2006, 03:31 AM
Thanks. I really appreciate your help with this.

I'm getting the information from all the other fields, and when I use MySQL Administrator, I can see everything, including the photo which is in jpeg format. You are welcome to check this also with the login information I provided.

Can you walk me through what the code you posted is supposed to do? How should I be calling this page? Can you point me into the direction of sample code (along with query script to be plugged into my database) that does work? This way I can probably get a better understanding of how it works. I've done a lot of searches looking for this information, but none of the search results were specific to pulling this information directly from the database.

Thanks.
unified
08-23-2006, 01:59 AM
The mind can only take so much and, in the time that I have left to live, life is too short. Whereas this is not as simple as it should be, I've decided to go along with simply storing a reference to the actual image file. This seems to me the preferred method, and it works for me. ;)