How to display images in a html page retrieved from a Mysql database using php

[colcar]

Hi,

 

I'm having trouble retrieving images from a mysql db and displaying them on the same page.

 

Here is my table:

 

CREATE TABLE `dm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(64) NOT NULL,
  'model' varchar(64) NOT NULL,
  'year' varchar(64) NOT NULL,
  'price' varchar(64) NOT NULL,
  'location' varchar(64) NOT NULL,
  `ext` varchar( character SET utf8 NOT NULL,
  `image_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `data` mediumblob NOT NULL,
  PRIMARY KEY  (`id`)
);

 

I can insert them in the db and display them on a seperate html page, here is the php script that I used.

 

<?php
$db_host = 'localhost'; // don't forget to change 
$db_user = 'root'; 
$db_pwd = 'colum';
$database = 'colum';
$table = 'dmexport';// use the same name as SQL table
$password = 'kinefad';// simple upload restriction,// to disallow uploading to everyone
if (!mysql_connect($db_host, $db_user, $db_pwd))    
die("Can't connect to database");
if (!mysql_select_db($database))    
die("Can't select database");
// This function makes usage of// $_GET, $_POST, etc... variables
// completly safe in SQL queries
function sql_safe($s){    
if (get_magic_quotes_gpc())        
	$s = stripslashes($s);    
return mysql_real_escape_string($s);
}
// If user pressed submit in one of the forms
if ($_SERVER['REQUEST_METHOD'] == 'POST'){    
// cleaning title field    
$title = trim(sql_safe($_POST['title']));  
$model = trim(sql_safe($_POST['model']));
$year = trim(sql_safe($_POST['year']));
$price = trim(sql_safe($_POST['price']));
$location = trim(sql_safe($_POST['location']));
if ($title == '') // if title is not set        
	$title = '(empty title)';// use (empty title) string    
if ($_POST['password'] != $password)  // cheking passwors        
	$msg = 'Error: wrong upload password';    
else    
{        
if (isset($_FILES['photo']))        
{            
	@list(, , $imtype, ) = getimagesize($_FILES['photo']['tmp_name']);            
	// Get image type.            
	// We use @ to omit errors            
	if ($imtype == 3) // cheking image type                
		$ext="png";   // to use it later in HTTP headers            
	elseif ($imtype == 2)                
		$ext="jpeg";            
	elseif ($imtype == 1)                
		$ext="gif";            
	else                
		$msg = 'Error: unknown file format';            
	if (!isset($msg)) // If there was no error            
	{                
		$data = file_get_contents($_FILES['photo']['tmp_name']);                
		$data = mysql_real_escape_string($data);                
		// Preparing data to be used in MySQL query                
		mysql_query("INSERT INTO {$table}                                
				SET ext='$ext', title='$title', data='$data',model='$model',year='$year',price='$price',location='$location'");                
		$msg = 'Success: image uploaded';            
	}        
}        
elseif (isset($_GET['title']))      // isset(..title) needed            
	$msg = 'Error: file not loaded';
		// to make sure we've using                                            
		// upload form, not form                                            
		// for deletion           
if (isset($_POST['del'])) // If used selected some photo to delete        
{                         // in 'uploaded images form';            
	$id = intval($_POST['del']);            
	mysql_query("DELETE FROM {$table} WHERE id=$id");            
	$msg = 'Photo deleted';        
}    
}
}
elseif (isset($_GET['show']))
{    
$id = intval($_GET['show']);    
$result = mysql_query("SELECT ext, UNIX_TIMESTAMP(image_time), data                             
			FROM {$table}                            
			WHERE id=$id LIMIT 1");    
if (mysql_num_rows($result) == 0)        
	die('no image');    
list($ext, $image_time, $data) = mysql_fetch_row($result);    
$send_304 = false;    
   
if ($send_304)    
{        
	// Sending 304 response to browser        
	// "Browser, your cached version of image is OK        
	// we're not sending anything new to you"        
	header('Last-Modified: '.gmdate('D, d M Y H:i:s', $ts).' GMT', true, 304);        
	exit(); // bye-bye    	
}    
// outputing Last-Modified header    
header('Last-Modified: '.gmdate('D, d M Y H:i:s', $image_time).' GMT',            
	true, 200);    
// Set expiration time +1 year    
// We do not have any photo re-uploading    
// so, browser may cache this photo for quite a long time    
header('Expires: '.gmdate('D, d M Y H:i:s',  $image_time + 86400*365).' GMT',            
	true, 200);    
// outputing HTTP headers    
header('Content-Length: '.strlen($data));    
header("Content-type: image/{$ext}");    
// outputing image    
echo $data;    
exit();
}
?>
<html><head>
<title>D & M, Plant, Truck and Trailer Exports Administration Page</title>
</head>
<body>
<?php
if (isset($msg)) // this is special section for                 
	// outputing message
{
?>
<p style="font-weight: bold;"><?=$msg?>
<br>
<a href="<?=$PHP_SELF?>">reload page</a>
<!-- I've added reloading link, because     
refreshing POST queries is not good idea -->
</p>
<?php
}
?>
<h1>Administration Page
</h1>
<h2>Uploaded images:</h2>
<form action="<?=$PHP_SELF?>" method="post">
<!-- This form is used for image deletion -->
<?php
$result = mysql_query("SELECT id, model, title, year, price, location, image_time FROM {$table} ORDER BY id DESC");
if (mysql_num_rows($result) == 0) // table is empty    
echo '<ul><li>No images loaded</li></ul>';
else
{    
echo '<ul>';    
while(list($id, $image_time, $title, $model, $year, $price, $location) = mysql_fetch_row($result))    
{        
	// outputing list        
	echo "<li><input type='radio' name='del' value='{$id}'>";        
	echo "<a href='{$PHP_SELF}?show={$id}'>{$title}</a> – ";        
	echo "<small>{$image_time}</small> – ";
	echo "<small>{$model}</small> – ";
	echo "<small>{$year}</small> – ";
	echo "<small>{$price}</small> – ";
	echo "<small>{$location}</small></li>"; 
}    
echo '</ul>';    
echo '<label for="password">Password:</label><br>';    
echo '<input type="password" name="password" id="password"><br><br>';    
echo '<input type="submit" value="Delete selected">';
}
?>
</form>
<h2>Upload new image:</h2>
<form action="<?=$PHP_SELF?>" method="POST" enctype="multipart/form-data">
<label for="title">Title:</label><br>
<input type="text" name="title" id="title" size="64"><br><br>
<label for="model">Model:</label><br>
<input type="text" name="model" id="model" size="64"><br><br>
<label for="year">Year:</label><br>
<input type="text" name="year" id="year" size="64"><br><br>
<label for="price">Price:</label><br>
<input type="text" name="price" id="price" size="64"><br><br>
<label for="location">Location:</label><br>
<input type="text" name="location" id="location" size="64"><br><br>
<label for="photo">Photo:</label><br>
<input type="file" name="photo" id="photo"><br><br>
<label for="password">Password:</label><br>
<input type="password" name="password" id="password"><br><br>
<input type="submit" value="upload">
</form>
</body>
</html>

 

Can anyone help me? It also must be a .htm file that the script is saved as. The images also must be displayed in tables side by side.

 

Thanks

 

Colum

[Gighalen]

Well, given you don't need to use a variable to set the ID, and the page you are using to display the image is using $_GET['id'], you can just do

<img src="image.php?id=2" />

[colcar]

Hi,

 

Thanks for the reply.

 

I want to connect to the DB and extract the image...

 

The examples I used before are where i have the image displying on a different page, by passing the id number of the picture in with the url of the new page..I think!

 

How do I display the image on the same page i.e. connect to the DB, extract the image and display on html page.

 

Thanks

 

Col

[BillyBoB]

PHP GD is what you are looking for I believe. You don't have to use a .html file for displaying this image. The php manual has a lot about PHP GD http://www.php.net/gd.

[colcar]

Hi,

 

Any code examples?? Thanks for the help.

 

Col

[kev wood]

if you are storing the path to the image in the db then this is the code you will need

 

   <?php
				$rand = rand (0, 100);	
				$query = "SELECT column_name FROM table_name";
				$result=mysql_query($query);

				while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
				echo '<img src="'.$row['column_name ']."?".$rand.'"/>';
				}
	     
				?>

 

the rand function has been used here to stop any cached images showing up, when i used this code because the image being shown was always different but always had the same file name it was showing the cached versions.  the rand puts a stop to this.

 

hope this helps

[colcar]

Hi,

 

Thanks, unfortunately I am not storing the path to the image in the DB. I'm storing the image. Is there any code for this case?

 

Col

[kev wood]

it is better if you store the path to the image in the db as it will be able to load the results quicker.  i have always used this method.

 

if you would like to try it this way then put the path to the image in a variable then store the variable inside the db. 

 

is the image being stored in the db or are you trying to display the image to find this out.

[colcar]

Hi,

 

I've heard it would better, but I've already implemented the other way i.e. store the image in the DB.

 

Can you give me an example script on how to do the "store the path in the DB way"?

 

Yes I checked the image via the CLI and the image is being stored in the DB.

 

Thanks

 

Col

[colcar]

Hi,

 

Can anyone please help me with this? I am wrecking my head here trying to do it, but not getting anywhere.

 

Or has anyone a complete script that will store the path to the image in the DB? Or even modif my script posted above in order to store the path to the image in the DB?

 

Thanks in advance.

 

Col

[kev wood]

set up a variable to hold the path to the image and you must know which file the images are held in on the server.  the next step is getting the path stored with the variable.  which is done in the same way as any other variable value is set.  the syntax would look something like this

 

$image1= site1/graphics/images/image

 

then you would simply store the variable in your mysql db and display it how i showed you earlier.

[colcar]

Hi,

 

Thanks for that, but I have a form that enters in other attributes, e.g. location, price etc. Is there a way I can extract the file path from image or any other means?

 

Col

[kev wood]

when the image is being upload you need to store a copy of the file on the server and then store this path in the mysql db.  when ever i have had image uploads this is how i have tackled it.  you will need to set up some files on the server where the images are to be stored first ( or get php to do this for you) then add some code to copy the image into these folders on the server.  this is how i have done this in the past

 

$broad1name="image/".$image_name;
$broad1name2="image/thumbs/thumb_".$image_name;
$broad1name3="image/thumbs/thumb_".$image_name . $rand;
$copied = copy($_FILES['broad1_image']['tmp_name'], $broad1name);
$copied = copy($_FILES['broad1_image']['tmp_name'], $broad1name2);
$sql="INSERT INTO images_broad (broad1) VALUES ('$broad1name3')";
$query = mysql_query($sql);

 

if you want the full code for my image upload i can pm it to you if you would like then you can just pull the code apart and use what you need or use the whole form if you like.

 

the image upload form i have created stores to copies of the file on the server in different sizes.  this could just be placed on your page using an iframe.

[colcar]

Hi,

 

Yes, please if you can that would be great?

 

Thanks for the help.

 

Col