Retrieving BLOB Using PHP

[batstanggt]

So Ive apparently gotten images into my database and now its time to get them out. I have managed to get all the images to load as "broken images" (the little red x) but its not showing them as actually images. Can someone have a look at my script and tell me where I'm going wrong.

Theres two files first is showimages.php

<?
    include "dbconfig.php";
    $dbconn = mysql_connect($dbhost, $dbusr, $dbpass) or die("Error Occurred-".mysql_error());
    mysql_select_db($dbname, $dbconn) or die("Unable to select database");

    $query = "SELECT `picid`, `pic_name`, `pic_type`, `pic_size`, `picture`
                     FROM mypictures ORDER BY `picid`";

    $result = mysql_query($query) or die('Error, query failed');

    while($row = mysql_fetch_array($result)){
               echo "<img src=\"viewimages.php?id=$row[picid]\"/> <br/>";
    }

    mysql_close($dbconn);
?>

second is viewimages.php

<?php
if(isset($_REQUEST['picid']))
{
   // get the file with the id from database
      include "dbconfig.php";
      $dbconn = mysql_connect($dbhost, $dbusr, $dbpass) or die("Error Occurred-".mysql_error());
      mysql_select_db($dbname, $dbconn) or die("Unable to select database");

      $id    = $_REQUEST ['picid'];
      $query = "SELECT `pic_name`, `pic_type`, `pic_size`, `picture`
                       FROM mypictures WHERE picid = '$id'";

      $result = mysql_query($query) or die(mysql_error());
      list($name, $type, $size, $content) = mysql_fetch_array($result);

      header("Content-length: $size");
      header("Content-type: $type");
      print $content;

      mysql_close($dbconn);
}
?>

Any help would be greatly appreciated.

-SB

[sasa]

change $_REQUEST['picid'] to $_GET['id']

[batstanggt]

Thanks sasa for the suggestion but unfortunately im getting the same result. Any more ideas?....Anyone? lol

-SB

[litebearer]

start by checking the value(s) of $id

[batstanggt]

Hey litebearer thanks for putting your 2 cents in. How does one check the values of $id? And when in the script do I place said piece of code. Im contimplating scrapping this script as I feel I have tried almost everything. However the new script im using presents the same problem. All the info is being pulled out of the db correctly pic_name, size, etc.. But the image itself is still just a proper size outline for the image w/ the red x in the top left corner. Please help me lol. If you want ill post the new code just let me know. Ill use either or I just need something simple that works. (if anyone has any other ideas)

-SB

[PFMaBiSmAd]

What litebearer is suggesting that you do is to confirm that the <img src=".....  HTML that is being output in your page is correct and has expected ?id=x values.

 

If the `picture`column is your BLOB data, you should NOT be selecting that in your first code, because that would be retrieving all the image data for all the rows in your table, but throwing it away. That adds unnecessary processing time and consumes memory, both on the db server and in your php code.

 

You need to troubleshoot why your code is not working, because you could try dozens of different scripts and they could all have the same problem, because the problem could be something on your server or something that your editor is doing when saving the file.

 

To troubleshoot what your viewimages.php code is doing, add the following two lines of code immediately after the opening <?php tag (edit: in viewimages.php) and temporarily comment out the two header() statements -

ini_set("display_errors", "1");
error_reporting(-1);

 

Next, browse directly to the viewimages.php?id=x page, with a valid image id for x, so that you can directly see the output that is being produced.

 

 

[xyph]

Here's a quick image upload to database script. Keep in mind you're limited by MySQL's @@max_allowed_packet (defaults to around 1 meg) along with all of PHPs upload limitations.

 

It seems to act funny with animated GIFs. Why? I haven't done the research, so I'm not quite sure yet.

 

<?php

/* DATABASE DUMP

CREATE TABLE IF NOT EXISTS `img_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) CHARACTER SET latin1 NOT NULL,
  `mime` varchar(20) CHARACTER SET latin1 NOT NULL,
  `width` int(4) NOT NULL,
  `height` int(4) NOT NULL,
  `data` blob NOT NULL
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

*/

// Database
$db = new mysqli( 'localhost', 'root', '', 'db' );

$max_size = 1048576;

// Get max packets allowed by MySQL - better to hardcode this, so I commented it out
// $q = 'SELECT @@max_allowed_packet';
// $r = $db->query( $q );
// list( $max_mysql ) = $r->fetch_row();
// $r->free();
// You should also grab other PHP upload limitations, and have your largest filesize
// be the smallest value found

if( !empty($_FILES) && !empty($_POST['name']) ) {

if( ($img = getimagesize($_FILES['file']['tmp_name'])) !== FALSE ) {
	if( filesize($_FILES['file']['tmp_name']) <= $max_size ) {
		$q = 'INSERT INTO `img_sample` SET
			`name` = \'' . $db->escape_string($_POST['name']) . '\',
			`mime` = \'' . $db->escape_string($img['mime']) . '\',
			`width` = ' . $img[0] . ', `height` = ' . $img[1] . ',
			`data` = \'' . $db->escape_string( file_get_contents($_FILES['file']['tmp_name']) ) . '\'';
		if( $db->query($q) ) {
			$msg = 'Image uploaded successfully';
		} else $msg = 'Database query error';
	} else $msg = 'Image too large';
} else $msg = 'Please upload a valid image file';

}

if( !empty($_GET['id']) ) {

$q = 'SELECT `mime`, `data` FROM `img_sample` WHERE `id` = ' . (int) $_GET['id'];
$r = $db->query( $q );
if( $r !== FALSE && $r->num_rows > 0) {
	$d = $r->fetch_assoc(); $r->free();
	header( 'Content-type: ' . $d['mime'] );
	echo $d['data'];
}

} else {

// List messages
if( !empty($msg) )
	echo '<h3 style="text-align:center">'.$msg.'</h3>';

// List images
echo '<div style="margin:0 auto;width:300px;"><ul>';
$q = 'SELECT `name`, `id`, `width`, `height` FROM `img_sample`';
$r = $db->query( $q );
if( $r !== FALSE && $r->num_rows > 0 ) {
	while( $d = $r->fetch_assoc() )
		echo '<li><a href="?id='.$d['id'].'">'.htmlspecialchars($d['name']).
			'</a> ('.$d['width'].'x'.$d['height'].')</li>';
	$r->free();
} else echo '<li>No images in database</li>';
echo '</ul></div>';

// HTML FORM FOR UPLOADING
echo <<<HEREDOC
<div style="text-align:center;">
<form method="post" action="#" enctype="multipart/form-data">
<label for="file">Choose an image to upload</label>
<input type="file" id="file" name="file"><br />
<label for="name">Give the image a name</label>
<input type="text" name="name" id="name"><br />
<input type="submit" value="Upload">
</form></div>
HEREDOC;

}

?>

 

Hope that helps.

[batstanggt]

PFMaBiSmAd, Thanks for such a detailed explaination. I checked to make sure that there was an ?id=number in the img tag. It does so no solution there. Then Inserted the 2 lines of code and now it only shows a white page with Resource Id #5 printed on the top left of the page. I dont really understand what you mean about not Selecting the 'picture' from the table in showimages.php. Isnt that where the image is passed from to viewimages.php?

-SB

[PFMaBiSmAd]

The following is your query in the first piece of code, that is producing the <img ...> tag -

$query = "SELECT `picid`, `pic_name`, `pic_type`, `pic_size`, `picture`
                     FROM mypictures ORDER BY `picid`";

 

The only piece of information you are using in the first piece of posted code is the picid column. That's the only column you should be selecting in the query. You both don't need the `picture`data in that piece of code (you need it in the viewimages.php code) and I already explained the downside of selecting the `picture`data in the first piece of code.

 

If you are getting "Resource Id #5" displayed by the viewimages.php code, that means you are either echoing the $result variable or you somehow inserted a result resource name into your `picture` column.

 

Post your actual code for viewimages.php (if it is different from the first post in this thread) and post the code you used to insert the actual image data into the `picture` column. What do you see in the `picture` column when you examine the database table directly using your favorite database management tool (phpmyadmin or similar)?

[batstanggt]

Hey PFMaBiSmAd I think you hit the nail right on the head. Judging by the fact that the picture column just says [bLOB-14B]  or when i select view blob image contents it says Resource Id #5 or #6. Either way 14 chars which is the same as 14B right? So I deffinitely think I inserted the resource id and not the actual image into the database as you suggested. Here is the upload script. Think you may be able to spot where I went wrong ?

<!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=utf-8" />
<title>Untitled Document</title>
</head>
<body>
  <h2>Please Choose a File and click Submit</h2>
  
  <form enctype="multipart/form-data" action="newestpictureupload.php" method="post">
  
  <input type="hidden" name="MAX_FILE_SIZE" value="99999999" />
  
  <div>Title: <input name="pic_name" type="text" /></div>
  
  <div>Description: <input name="pic_desc" type="text" /></div>
  
  <div>Picture to Upload<input name="picture" type="file" /></div>
  
  <div>yes or no question </div>
  
  <div>Yes: <input name="group" type="radio" value="y" /> No: 
  <input name="group" type="radio" value="n" /> </div>
  
  <div><input type="submit" value="Submit" /></div>
  </form>
  

Thats the uploadimage.html.

Now Heres the newestpictureupload.php.

<?php
ini_set('display_errors', 'On');ini_set('display_startup_errors', 'On');error_reporting(-1);
/*** check if a file was submitted ***/
if(!isset($_FILES['picture']))
    {
    echo '<p>Please select a file</p>';
    }
else
    {
    try    {
        upload();
        /*** give praise and thanks to the php gods ***/
        echo '<p>Thank you for submitting</p>';
        }
    catch(Exception $e)
        {
        echo '<h4>'.$e->getMessage().'</h4>';
        }
    }
?>
<?php
/**
*
* the upload function
* 
* @access public
*
* @return void
*
*/
function upload(){
/*** check if a file was uploaded ***/
if(is_uploaded_file($_FILES['picture']['tmp_name']) && getimagesize($_FILES['picture']['tmp_name']) != false)
    {
    /***  get the image info. ***/
    $size = getimagesize($_FILES['picture']['tmp_name']);
    /*** assign our variables ***/
    $type = $size['mime'];
    $imgfp = fopen($_FILES['picture']['tmp_name'], 'rb');
    $size = $size[3];
    $pic_name = $_POST['pic_name'];
$pic_desc = $_POST['pic_desc'];
$six = $_POST['group'];
    $maxsize = 99999999;


    /***  check the file is less than the maximum file size ***/
    if($_FILES['picture']['size'] < $maxsize )
        {
        /*** connect to db ***/
        $dbh = new PDO("mysql:host=localhost;dbname=mygreensky", 'root', '04mustanggt');

                /*** set the error mode ***/
                $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            /*** our sql query ***/
        $stmt = $dbh->prepare("INSERT INTO mypictures (pic_type , picture, pic_size, pic_name, pic_desc, six) VALUES ('$type' , '$imgfp', '$size', '$pic_name', '$pic_desc', '$six')");

        /*** bind the params ***/
        $stmt->bindParam(1, $type);
        $stmt->bindParam(2, $imgfp, PDO::PARAM_LOB);
        $stmt->bindParam(3, $size);
        $stmt->bindParam(4, $pic_name);
	$stmt->bindParam(5, $pic_desc);
	$stmt->bindParam(6, $six);

        /*** execute the query ***/
        $stmt->execute();
        }
    else
        {
        /*** throw an exception is image is not of type ***/
        throw new Exception("File Size Error");
        }
    }
else
    {
    // if the file is not less than the maximum allowed, print an error
    throw new Exception("Unsupported Image Format!");
    }
}
?>
</body></html>

Thanks so much for all the help so far.

-SB

[batstanggt]

Dont mean to bump the thread too soon but I'm way to deep into this now to let it die. Anybody able to help? Above is my upload script. I beleive that is the culprit which is causing the actual text "Resource Id #5" or "Resource Id #6" to be inserted into the BLOB field instead of the image which Im trying to upload.

-SB

[PFMaBiSmAd]

fopen, in the following line of code, only opens the file for reading. $imgfp contains a file handle (resource.)

 

$imgfp = fopen($_FILES['picture']['tmp_name'], 'rb');

 

You need to actually read the contents of the file into a variable. See - fread or you could just use file_get_contents to open/read the file using one statement.

[batstanggt]

Sick! Thanks PFMaBiSmAd were deffs on the right track I think. So the question is now do I just replace the fopen() with fread()/ file_get_contents() or is there more to it than that? I think once I get this straightened out Ill be good to go so bear with me for just a lil longer. Anyone can feel free to chime in as well  lol.

-SB

[xyph]

Try it, then ask. Making mistakes is the first step to learning.

[batstanggt]

OK so i scrapped that garbage object style php script (PDO crap). Got a new one works now. Im all for making mistakes and learning but a general idea is always helpful because I didnt even know where to insert those functions especially considering the php manual doesnt show an example in this context. Thanks to everyone for your help.

-SB