Hi,

The problem I am having is that I cannot retreive pictures I have put into my MySQL db.

I can upload pictures to my db, but I cannot retrive them to view them on a page.

At the moment I have 4 pages.

  1. up.html:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-UK">
<head>
<title>WAT PHP Lecture Notes - Image Upload to MySQL</title>
<meta http-equiv="Expires" content="Tue, 10 Sep 2002 00:00:00 GMT"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<meta name="Author" content=""/>
</head>
<body>
<form action="upload.php" method="post" enctype="multipart/form-data">
<p>
Your name: <input type="text" name="username" /><br />
Small image to upload: <input type="file" size="40" name="userfile" /><br />
<input type="submit" value="Upload file" />
</p></form>

</body></html>

  1. upload.php

<body>
<h1>Uploading Images to MySQL</h1><p>
You submitted this file:<br /><br />
Temporary name: <?php echo $FILES[userfile][tmp_name] ?><br />
Original name: <?php echo $FILES[userfile][name] ?><br />
Size: <?php echo $FILES[userfile][size] ?> bytes<br />
Type: <?php echo $FILES[userfile][type] ?></p>

<?php
//require file that states the values for the db connection, i.e. Password etc. Makes them secure
require '/home/involve/my.private';
function prntErr($errMesg) {
printf("<p><strong> %s </strong></p>\n", $errMesg);
}
if ( !ereg( "gif|png|x-png|jpeg", $FILES[userfile][type]) ) {
prntErr("Sorry only browser compatible images allowed");
} else if ( strlen($username) < 3 ) {
prntErr("Sorry username too short<br />min 3 characters");
} else if ( $FILES[userfile][size] > 5096 ) {
prntErr("Sorry file too large");
} else if ( !($link=mysql_connect($host, $user, $passwd)) ) {
prntErr("Error connecting to database");
} else if ( !(mysql_select_db($dbName)) ) {
prntErr("Error selecting database");
} else if ( !($handle = fopen ($FILES[userfile][tmp_name], "r")) ) {
prntErr("Error opening temp file");
} else if ( !($image = fread ($handle, filesize($FILES[userfile][tmp_name]))) ) {
prntErr("Error reading temp file");
} else {
$ext = explode('.', $userfile_name);
$filename = $username . '.' . $ext[count($ext)-1];
fclose ($handle);
$image = mysql_escape_string($image);
//Insert image selected into db
$query = "INSERT INTO hero (file,img) VALUES ('$filename','$image')";
if ( !(mysql_query($query,$link)) ) {
prntErr("Error writing image to database");
prntErr(sprintf("Error %s : %s", mysql_errno(), mysql_error()));
} else {
prntErr("Image successfully copied to database") ;
}
}
?>
</body>

These two pages work as when I look in my db table it has entered in the new file id and name, and alot of gobbledigook, which is obviously the image being stored.

However my next 2 pages are supposed to retreive all my images and print them out to the screen. However all I get is a blank image with the red cross, and the gobbledigook from the table.

3.ImageShow.php

<title>WAT PHP Lecture Notes - Image Download from MySQL</title>
<meta http-equiv="Expires" content="Tue, 10 Sep 2002 00:00:00 GMT"/>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<meta name="Author" content=""/>
</head>
<body>
<h1>Images Stored in MySQL</h1>
<snip>
<?php
//require file that states the values for the db connection, i.e. Password etc. Makes them secure
require '/home/involve/my.private';
function prntErr($errMesg) {
printf("<p><strong> %s </strong></p>\n", $errMesg);
}
if ( !($link=mysql_connect($host, $user, $passwd)) ) {
prntErr("Error connecting to database");
} else if ( !(mysql_select_db($dbName)) ) {
prntErr("Error selecting database");
} else {
$query = "SELECT img FROM hero ORDER BY file";
if ( !($result = mysql_query($query,$link)) ) {
prntErr("Error reading database");
prntErr(sprintf("Error %s : %s", mysql_errno(), mysql_error()));
} else {
for ( $i = 0 ; $i < mysql_num_rows($result) ; $i++ ) {
$row = mysql_fetch_row($result);
echo "<img src=\"getImage.php?file=$row[0]\" alt=\"$row[0]\"/> \n";
}
}
}
?>
</snip>

</body></html>

  1. getImage.php

<?php
//require file that states the values for the db connection, i.e. Password etc. Makes them secure
require '/home/involve/my.private';
$link = mysql_connect($host, $user, $passwd);
mysql_select_db($dbName);
$query = "SELECT img FROM hero WHERE file='$GET[file]'";
$result = mysql_query($query,$link);
$row = mysql_fetch_row($result);
$ext = explode('.', $GET[file]);
$type = $ext[count($ext)-1];
if ( $type == 'gif' )
header("Content-Type: image/gif");
else if ( $type == 'jpg' )
header("Content-Type: image/jpeg");
else if ( $type == 'jpeg' )
header("Content-Type: image/jpeg");
else if ( $type == 'png' )
header("Content-Type: image/png");
echo $row[0];
?>

I can't see why it won't display the images!!!

Sorry there's alot of code, but thought it would be better to post it all, so you can see what I have already done!!

Thanks for any help!!

    I know you can store images in a DB, but I've never had a need to do so (I'll explain later). Otherwise, the code looks somewhat ok. In file #3, you end up querying for the img field and then print out the file field instead. You'll need to update that query to retrieve file instead of img.

    In file #4, I'd recommend printing out the query (just echo $query is fine). Verify the file name is making it into the query. To go the extra step, I'd suggest copying the printed out query as is and putting it in phpMyAdmin to make sure you get a row.

    Beyond that, I'd say there's an issue with echoing the binary data or there's an issue with how the binary data was saved.

    Is there a reason for putting the raw image data in the database? My past experiences have shown it was a bad idea since you're putting a large chunk of data that you don't need to do any manipulations on (you're just using the DB engine for storage and thats it - and you're returning queries with a large chunk of image data). The 2nd problem is if you wish to update the image, you have to go through the database engine to do it (versus just FTPing the new file up in the old files place). But I'm not sure what you're goals are and your reasoning for doing this...

      Hmmmm.... not sure what's wrong with the code, but here's a working example (It uses a sqlclass for query, and it uses id for pic indentification):

      <?PHP
function cleanId($id) {
$id = ereg_replace("[^[:digit:]]", "", $id);
return($id);
}
## Picture id 53 is a default pic ##
if($picture_id == "") $picture_id = 53; else $picture_id = cleanId($picture_id);
$sqlShowPic = new DB_dbname("select file, file_type, picture_name from picture_table where picture_id=$picture_id");
$sqlShowPic -> next_record();
$data = $sqlShowPic -> f("file");
$type = $sqlShowPic -> f("file_type");
$picture_name = $sqlShowPic -> f("picture_name");
header("Content-Type: $type");
header("Content-Disposition: inline; filename=$picture_name");
echo $data;
?>

      There's some issues with storing images as blobs (in db), especially if the image-table gets big and fat, but as long as your image-table is relatively small, it should be no prob.

      But you should consider using id's instead of imagename.

      knutm :-)

        Well my goals and reasons are that, I am creating a web page that displays a list of dvd's from a db. With the text I wanted to put a front cover picture of the DVD, a bit like Amazon for example...

        So in theory I could have hundreds of DVD titles, all which have a picture image that goes with it. I thought about storing these images in a db, and just querying them from the table like I do with the DVD titles. However it seems that it would be easier just to save the images on the server, and reference them by their file name.

        Still can't see why the images are not being displayed. Must have something to do withhow the binary data was saved maybe??

          Perhaps you should try to access the image-script directly in the browser, and see if it displays any php-errors?
          Just leaf through some of the image names and try it out.
          If you are linking the script in an image-src, the error won't show.

          Tip:
          If you use this line:
          header("Content-Disposition: inline; filename=$picture_name");
          the image will be saved as the ... erm image name, else it will be saved as the image-scripts name: getImage.php

          knutm :-)

            a year later

            hi:
            I have read your post about "Retreiving and Displaying graphics from a MySQL db using PHP"
            Currently, I have the same problem as you. I am just wondering did you figure out how to get around this problem.

            I post your code in case you forget:

            Hi,

            The problem I am having is that I cannot retreive pictures I have put into my MySQL db.

            I can upload pictures to my db, but I cannot retrive them to view them on a page.

            At the moment I have 4 pages.

            1. up.html:

            <?xml version="1.0" encoding="UTF-8"?>
            <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "DTD/xhtml1-strict.dtd">
            <html xmlns="http://www.w3.org/1999/xhtml" lang="en-UK">
            <head>
            <title>WAT PHP Lecture Notes - Image Upload to MySQL</title>
            <meta http-equiv="Expires" content="Tue, 10 Sep 2002 00:00:00 GMT"/>
            <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
            <meta name="Author" content=""/>
            </head>
            <body>
            <form action="upload.php" method="post" enctype="multipart/form-data">
            <p>
            Your name: <input type="text" name="username" /><br />
            Small image to upload: <input type="file" size="40" name="userfile" /><br />
            <input type="submit" value="Upload file" />
            </p></form>

            </body></html>

            1. upload.php

            <body>
            <h1>Uploading Images to MySQL</h1><p>
            You submitted this file:<br /><br />
            Temporary name: <?php echo $FILES[userfile][tmp_name] ?><br />
            Original name: <?php echo $            FILES[userfile][name] ?><br />
            Size: <?php echo $FILES[userfile][size] ?> bytes<br />
            Type: <?php echo $            FILES[userfile][type] ?></p>

            <?php
            //require file that states the values for the db connection, i.e. Password etc. Makes them secure
            require '/home/involve/my.private';
            function prntErr($errMesg) {
            printf("<p><strong> %s </strong></p>\n", $errMesg);
            }
            if ( !ereg( "gif|png|x-png|jpeg", $FILES[userfile][type]) ) {
            prntErr("Sorry only browser compatible images allowed");
            } else if ( strlen($username) < 3 ) {
            prntErr("Sorry username too short<br />min 3 characters");
            } else if ( $            FILES[userfile][size] > 5096 ) {
            prntErr("Sorry file too large");
            } else if ( !($link=mysql_connect($host, $user, $passwd)) ) {
            prntErr("Error connecting to database");
            } else if ( !(mysql_select_db($dbName)) ) {
            prntErr("Error selecting database");
            } else if ( !($handle = fopen ($FILES[userfile][tmp_name], "r")) ) {
            prntErr("Error opening temp file");
            } else if ( !($image = fread ($handle, filesize($            FILES[userfile][tmp_name]))) ) {
            prntErr("Error reading temp file");
            } else {
            $ext = explode('.', $userfile_name);
            $filename = $username . '.' . $ext[count($ext)-1];
            fclose ($handle);
            $image = mysql_escape_string($image);
            //Insert image selected into db
            $query = "INSERT INTO hero (file,img) VALUES ('$filename','$image')";
            if ( !(mysql_query($query,$link)) ) {
            prntErr("Error writing image to database");
            prntErr(sprintf("Error %s : %s", mysql_errno(), mysql_error()));
            } else {
            prntErr("Image successfully copied to database") ;
            }
            }
            ?>
            </body>

            These two pages work as when I look in my db table it has entered in the new file id and name, and alot of gobbledigook, which is obviously the image being stored.

            However my next 2 pages are supposed to retreive all my images and print them out to the screen. However all I get is a blank image with the red cross, and the gobbledigook from the table.

            3.ImageShow.php

            <title>WAT PHP Lecture Notes - Image Download from MySQL</title>
            <meta http-equiv="Expires" content="Tue, 10 Sep 2002 00:00:00 GMT"/>
            <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
            <meta name="Author" content=""/>
            </head>
            <body>
            <h1>Images Stored in MySQL</h1>
            <snip>
            <?php
            //require file that states the values for the db connection, i.e. Password etc. Makes them secure
            require '/home/involve/my.private';
            function prntErr($errMesg) {
            printf("<p><strong> %s </strong></p>\n", $errMesg);
            }
            if ( !($link=mysql_connect($host, $user, $passwd)) ) {
            prntErr("Error connecting to database");
            } else if ( !(mysql_select_db($dbName)) ) {
            prntErr("Error selecting database");
            } else {
            $query = "SELECT img FROM hero ORDER BY file";
            if ( !($result = mysql_query($query,$link)) ) {
            prntErr("Error reading database");
            prntErr(sprintf("Error %s : %s", mysql_errno(), mysql_error()));
            } else {
            for ( $i = 0 ; $i < mysql_num_rows($result) ; $i++ ) {
            $row = mysql_fetch_row($result);
            echo "<img src=\"getImage.php?file=$row[0]\" alt=\"$row[0]\"/> \n";
            }
            }
            }
            ?>
            </snip>

            </body></html>

            1. getImage.php

            <?php
            //require file that states the values for the db connection, i.e. Password etc. Makes them secure
            require '/home/involve/my.private';
            $link = mysql_connect($host, $user, $passwd);
            mysql_select_db($dbName);
            $query = "SELECT img FROM hero WHERE file='$GET[file]'";
            $result = mysql_query($query,$link);
            $row = mysql_fetch_row($result);
            $ext = explode('.', $            GET[file]);
            $type = $ext[count($ext)-1];
            if ( $type == 'gif' )
            header("Content-Type: image/gif");
            else if ( $type == 'jpg' )
            header("Content-Type: image/jpeg");
            else if ( $type == 'jpeg' )
            header("Content-Type: image/jpeg");
            else if ( $type == 'png' )
            header("Content-Type: image/png");
            echo $row[0];
            ?>

            I can't see why it won't display the images!!!

            Sorry there's alot of code, but thought it would be better to post it all, so you can see what I have already done!!

            Thanks for any help!!

              Opssss, the codes appear twice.

              I am curious that why the codes are not wokring? Anyone has any idea?

                Write a Reply...