Displaying Image From MySQL

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 "<h3>{$row['First_Name']} {$row['Last_Name']}</h3> {$row['Biography']} <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> <hr />\n"; } } else { // Query didn't run. die ('Could not retrieve the data because: ' . mysql_error() . ". The query was $query."); } // 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?

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 "<h3>{$row['First_Name']} {$row['Last_Name']}</h3> {$row['Biography']} <img src=\"$PHP_SELF?photo&last={$row['Last_Name']}&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> <hr />\n"; } } else { // Query didn't run. die ('Could not retrieve the data because: ' . mysql_error() . ". The query was $query."); } // 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.

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?

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.

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 ('Could not select the database because: ' . mysql_error() . ''); } } else { die ('Could not connect to MySQL because: ' . mysql_error() . ''); } 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 "<h3>{$row['First_Name']} {$row['Last_Name']}</h3> {$row['Biography']} <img src=\"$PHP_SELF?photo&last={$row['Last_Name']}&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> <hr />\n"; } } else { // Query didn't run. die ('Could not retrieve the data because: ' . mysql_error() . ". The query was $query."); } // End of query IF. mysql_close(); // Close the database connection. ?> </body> </html>

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 ('Could not select the database because: ' . mysql_error() . ''); } } else { die ('Could not connect to MySQL because: ' . mysql_error() . ''); } 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.

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 ('Could not select the database because: ' . mysql_error() . ''); } } else { die ('Could not connect to MySQL because: ' . mysql_error() . ''); } 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 "<h3>{$row['First_Name']} {$row['Last_Name']}</h3> {$row['Biography']} <img src=\"$PHP_SELF?photo&last={$row['Last_Name']}&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> <hr />\n"; } } else { // Query didn't run. die ('Could not retrieve the data because: ' . mysql_error() . ". The query was $query."); } // End of query IF. mysql_close(); // Close the database connection. ?> </body> </html>

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.

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.

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

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.

Displaying Image From MySQL - Resolved

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. ;)