Uploading picture for products table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

Uploading picture for products table

Post by mdesouza »

Hi all

I am writing a small shopping cart as part of my php project, I want to build it from scratch.

The problem is that i want to store pictures for the products in the product table.

Can someone please let me know who this is done. i cant seem to find any code on the net that does it

I want to be able to store the picture of the product in the database i guess in a blob field !!

Any Help would be appreciated .

Thks

Mike
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Hi,

Here is some code to do it (in the most basic format). It assumes you have created a MySQL table like so:

Code: Select all

CREATE TABLE `images` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `data` longblob NOT NULL,
  `width` int(10) unsigned default NULL,
  `height` int(10) unsigned default NULL,
  `mime` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;
Here is the first page - call this index.php

EDIT: If you cannot see it, it's because a post further down this page has knocked my code off to the right - just scroll your browser RIGHT to see it!

Code: Select all

<?php
	if (isset($_GET['e']))
	{
		switch ($_GET['e'])
		{
			case 1:
				$error = 'The uploaded file exceeds the upload_max_filesize directive in php.ini.';
				break;
			case 2:
				$error = 'The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form.';
				break;
			case 3:
				$error = 'The uploaded file was only partially uploaded.';
				break;
			case 4:
				$error = 'No file was uploaded.';
				break;
			case 5:
				$error = 'The image MIME type could not be determined - are you sure you uploaded a valid image file?';
				break;
			default:
				$error = 'An un-specified error occured';
		}
	}
	else
	{
		$error = false;
	}
?>
<html>
<head>
	<title>How to store an image into a MySQL BLOB field</title>
	<style>
	BODY, TD {
		font-family: Arial, Helvetica;
		font-size: 12px;
		color: black
	}
	</style>
</head>

<body>

<h1>Images <> MySQL via PHP</h1>

<p>Your web server is configured as follows:</p>

<table border=1>
<tr><td>File Uploads Enabled:</td><td><?=ini_get('file_uploads')?></td></tr>
<tr><td>Upload Max Filesize</td><td><?=ini_get('upload_max_filesize')?></td></tr>
<tr><td>Upload Temp Directory</td><td><?=ini_get('upload_tmp_dir')?></td></tr>
<tr><td>POST Max Size</td><td><?=ini_get('post_max_size')?></td></tr>
</table>

<?php
	if ($error)
	{
		echo "<p><font color=red><b>ERROR: $error</b></font></p>";
	}
?>

<p>
<form action="upload_image.php" method=post enctype="multipart/form-data">
<input type="hidden" name="MAX_FILE_SIZE" value="<?=ini_get('upload_max_filesize')?>">
Image File: <input type=file name="image" accept="image/jpeg,image/gif,image/x-png"><br>
<input type=submit value="Upload">
</form>
</p>

</body>
</html>
Here is the second script you need, call it upload_image.php

Code: Select all

<?php
	if ($_FILES['image']['error'] !== 0)
	{
		Header('Location: index.php?e=' . $_FILES['image']['error']);
	}

	$file_tmp_name = $_FILES['image']['tmp_name'];

	//	Get the dimensions of the image
	$size = getimagesize($file_tmp_name);
	$image_width = $size[0];
	$image_height = $size[1];
	$image_mime = $size['mime'];

	if ($image_mime == '')
	{
		Header('Location: index.php?e=5');
	}

	//	Swap the slashes around on a Windows server otherwise MySQL freaks
	$file_tmp_name = str_replace('\'', '/', $file_tmp_name);

	//	Save it to MySQL - normally I'd use a DB abstraction class, but here we need results fast
	mysql_connect('localhost', 'root', '') or die('Could not connect to MySQL');
	mysql_select_db('test');
	$sql = "
		INSERT INTO
			images
			(
				data,
				width,
				height,
				mime
			) VALUES (
				LOAD_FILE('$file_tmp_name'),
				'$image_width',
				'$image_height',
				'$image_mime'
			)
	";
	mysql_query($sql);
	
	if (mysql_error())
	{
		$error = mysql_error();
	}
	else
	{
		$error = false;
		$id = mysql_insert_id();
	}
	
	mysql_close();
?>
<html>
<head>
	<title>How to store an image into a MySQL BLOB field</title>
	<style>
	BODY, TD {
		font-family: Arial, Helvetica;
		font-size: 12px;
		color: black
	}
	</style>
</head>

<body>

<h1>Images <> MySQL via PHP</h1>

<?php
	if ($error)
	{
		echo "<p><font color=red><b>ERROR: $error</b></font></p>";
	}
	else
	{
?>

<p>
Image Uploaded Successfully
</p>

<p>
Image Data:
</p>

<table border=1>
<tr><td>Width:</td><td><?=$image_width?></td></tr>
<tr><td>Height:</td><td><?=$image_height?></td></tr>
<tr><td>Mime Type:</td><td><?=$image_mime?></td></tr>
<tr><td>Image:</td><td><img src="show_image.php?i=<?=$id?>" width="<?=$image_width?>" height="<?=$image_height?>" alt="This came from MySQL!"></td></tr>
</table>

<p>
<a href="index.php">Upload another image</a>
</p>

<?
	}
?>

</body>
</html>
And finally the third script (to display the image) - call it show_image.php

Code: Select all

<?php
	$id = false;
	if (isset($_GET['i']))
	{
		$id = $_GET['i'];
	}
	
	//	If the ID isn't numeric or is less than 1 (impossible) then stick in a default image
	if (!is_numeric($id) || $id < 1)
	{
		Header("Content-type: image/gif");
		fpassthru('default_image.gif');
		exit;
	}
	else
	{
		mysql_connect('localhost', 'root', '') or die('Could not connect to MySQL');
		mysql_select_db('test');
		$sql = "
			SELECT
				*
			FROM
				images
			WHERE
				id = '$id'
		";
		$result = mysql_query($sql);
	
		$image_mime = mysql_result($result, 0, 'mime');
		$image_data = mysql_result($result, 0, 'data');

		Header("Content-type: $image_mime");
		echo $image_data;
		exit;
	}
?>
Save these three scripts out, create your table (MySQL 3.x is fine) and away you go. Post here if you have any questions.
Last edited by launchcode on Thu May 20, 2004 12:14 pm, edited 1 time in total.
mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

Post by mdesouza »

Hi rich

Thanks for your help will give it a go tonight.


Michael
mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

Uploading picture

Post by mdesouza »

Rich

i current have the following table
CREATE TABLE products (
product_id int(4) DEFAULT '0' NOT NULL auto_increment,
category_id int(4) DEFAULT '1' NOT NULL,
product_name varchar(50) DEFAULT '' NOT NULL,
product_description varchar(240),
product_image blob,
PRIMARY KEY (product_id)

and the reason i was asking was that i need to create some sample data for the table , so I guess the only way to do so is to do as u suggested

Mike
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

It won't take you more than 5 minutes to copy out the scripts I posted and create a test table - when you do that you'll get a very good and immediate understanding of how the process works, from which you should be able to adapt it to your own needs very quickly.

BTW a BLOB field will only hold an image up to a maximum of 64KB in size.
mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

Picture upload

Post by mdesouza »

Hi rich

thanks for the scripts it just what i want , the only problem i cant display the image from the db , I get the X in the image.

is there something that needs configuring
All the details are displayed except the image


Width: 200
Height: 67
Mime Type: image/jpeg
Image:

Upload another image

thks

Mike
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

There is a password/user/table in the show_picture script you'll need to make sure you have modified.
mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

Post by mdesouza »

rich

all the username, password and databases are correctly set, its ouputs all the other data except the picture. the picture is stored in the database

hmmmm..!!
well will try to figure it out

thanks for all your help.

Mike
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Are you sure it entered the database correctly? I.e. does it appear if you use something like MySQL Front and view the BLOB field?
mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

Post by mdesouza »

Rich

Exported the data from the images table. the image is definetly stored in the database. Cant seem to figure out why it does not output the image. in the table.

Mike



CREATE TABLE `images` (
`id` int(10) unsigned NOT NULL auto_increment,
`data` longblob NOT NULL,
`width` int(10) unsigned default NULL,
`height` int(10) unsigned default NULL,
`mime` varchar(40) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

#
# Dumping data for table `images`
#

INSERT INTO `images` VALUES (1, 0xffd8ffe000104a4649460001020100c800c80000ffc0001108004300c803012200021101031101ffdb0084000503030403030504040405050506070d0807070707100b0c090d131014131210121215171e1915161c1612121a231a1c1f20212221141925272420271e212120010505050706070f08080f201512151520202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020ffc401a20000010501010101010100000000000000000102030405060708090a0b100002010303020403050504040000017d01020300041105122131410613516107227114328191a1082342b1c11552d1f02433627282090a161718191a25262728292a3435363738393a434445464748494a535455565758595a636465666768696a737475767778797a838485868788898a92939495969798999aa2a3a4a5a6a7a8a9aab2b3b4b5b6b7b8b9bac2c3c4c5c6c7c8c9cad2d3d4d5d6d7d8d9dae1e2e3e4e5e6e7e8e9eaf1f2f3f4f5f6f7f8f9fa0100030101010101010101010000000000000102030405060708090a0b1100020102040403040705040400010277000102031104052131061241510761711322328108144291a1b1c109233352f0156272d10a162434e125f11718191a262728292a35363738393a434445464748494a535455565758595a636465666768696a737475767778797a82838485868788898a92939495969798999aa2a3a4a5a6a7a8a9aab2b3b4b5b6b7b8b9bac2c3c4c5c6c7c8c9cad2d3d4d5d6d7d8d9dae2e3e4e5e6e7e8e9eaf2f3f4f5f6f7f8f9faffda000c03010002110311003f00fb22496381434922a02700b1c0cd39486195208f6af98ff6b6f10dcdbf8ff47b18a58624b5d2cce1dd11cab49215e37821788b93d4f1c81d7c6e4f8a971a521275eb6523b456b13b7d3e44ae9a787528f3362e68ad19fa03457c0169fb4ef89343ca69f35e4f8600e4b5b81f82be0fe55d0e8bfb6e78bf4f256f74e69941e765d2c83f278f3ff8f53786ecc7cd1ee7dbd457cafa1fedeba63623d5b4d78db8e4c0c3f542ff00c857a0f87bf6bbf026bef1471dd5b2c8e70631788ae3e8926c63f97e759bc3d443493d99ecf4573da0fc43f0ef88e68edacf5044ba90652de60639187aa83f78639cae4574359ca2e2ecd034d6e14514548828acad4fc5ba068cfe56a1acd85b49ff003ce49d439ff80e7358779f193c1964a59b566931ff003ceda4607f1db8fd6ae34e72d90d45bd91d8d15e6f3fed17e0c83857bc93e8235ffd09c5509bf69df09c44858273838e6e201fc9cd52a151f40e567abd15e38dfb56f8500f96d653c7fcfc47fe34d1fb57785f00fd8dc0ff00af85a7f57a9d82c7b2d15e3abfb55f84f80d115ffb6c0ff215343fb52783a66c6e23fedb28fe7814fead53b058f5ca2bcdad3f688f08dda1644bd7c2176f255262aa3a9c23138fc2bd0ac2fadf53b1b7bdb4904b6f7112cb138046e5619079f6359ce9ce1f120b344f4514540828a28a0028a28a00f8abf6c5b66b8f8b77025f9f165098f79c855dbd07b64138f526bc224b42e184463519219c83c73ffeaafa57f6cad1669be21d95c5bc459a6d3147040390ee3fad7868f07ddcdb8ccf0c2add07df6ebe8303f5aef8caf156ec5ca9f3ea8e49e16899f2de6e31cf4e7f0a65bda5c5d4fe45b44d34c0e02463247be7a01ee715de41e0cd3a33ba6592e89c643b6d5ffbe47f5ad58ad62820f2a28a28a3c7dc8c0551f955233f62ce474af01285126a736eff00a77b76c28ff79fa9ff0080e3ea6ba08349d3ad61f2adb4eb58a33c9020539fa92093f89abfe49071ce68f2b2075c8e39abba4254596bc3baa7fc23f34518322e9e64532dba37cb1f3feb231fc0ebd415c671839cd7d4bf0e3e2dcf15a5be9fe2ab949904ed64baa81b552652479338e8ac460abfdd607b106be516877a1571c1e302bd6be1a683e2ff00167872e87875adde27b646bf4ba4478a76d9f2a80dd646c0e78195524838359d54aa46dd8de10e55caf63eacaf37fda07e2627c3af0618ede575d4f51dd15b2c4d875518deea7b11b9541ecd229af2cf087c5bf1f5969a965a66ada1dcc36c4c1f66d434f97cdb62a7051c090302bc8c738c63b015e79f14b53f10ea9aff00dabc5b7827d42e244d850a88161c33c6b06d257cbce39cb1247cc49181cd4e92e6d5e83549ad7a1ce4fad6a13ee5f3beccacb831da6624c7a71f337d58926a9322b7de058e7193cff3a9fcb0bf2e7a7073434471ebc71c577f398ba7296e40117b2f6feed2e0a1e8474a9523c2f3d7e94e58b3e87f9d0a44fb121058ae0b122955c81b41e3a549e5927eef34d113020939e3d3a51ce25458d691870589cf1d294938c8c9c73cd396100e40c8ebd29fe5f03820e3bd1cc83d8b2032cb6ec2e6d5da2ba83f79048a30c8e39047e3c63b8e0f5afb23e0978e74cd6fc09a740f751c1710dbab2c52c803792c3746c33d405207b1535f1ec71ed9031e39e2bbff0001aadef8434a5bb89649638da15674070b1c8f18ebd384a99c1555ca694e9b5747d21a87c70f04595fcfa75beab36a97b0005a0d2eca6bc24f181ba352b9e40e48c12338aa73fc59d5ee562fec8f02ea44b4de531d4ef60b455c2ee27e5691b81c9ca8c715e4be1bb663e268da39de27b781da108f821c90a0afb8dd91feee7b5769a719aeed59649d8b340230121c461cecdc591b2c1413dbef919e01c579f89a7ec64a2bb1b2a697f5fd797e26ccbe3ef1bcf3f9a3fe114d32d8eefddbf9f7522f508188f2f058827a70bc9ab5ff00094f8ce28c19f53f0da391c6ed32541facf58ed6f3b4bf69586586211bb113300a31c6ede7ef2e4723b838e6ba5d23c246fe7fb40b65b68cb1633bc63cc7c9c92a0f4c9f60067804573bbf433968f445ef0878935cd52e8db6a96fa5c898245cd9bc8a33e9b1d7f50c6baac7b5711a0dca5dfc4ebdb2b0675b3d2b4b41300d90f2cce4ae7d4848c9ff00818f6aedf1ed551d510d5b73c27f6b6d14bd9f87b5a545c453cb69210393bd43afe03cb6fcebe7c68b0786afb27e33f8664f167c34d66c2088cb7490fda6dd02e4b49190e147bb6ddbff0002af8ff62ca15d7946008fa56f19d91e960e0aa42dd8ac63c70075a4d870392055bf2463bf1dbd691a2dca30d8a6aa1d7f562a2423a04e9d297cae3b806acf9440ebd3da97cb1d0823b1a7ed01614ab2c21636e00e0f1f857b5fecebafdae97e34d4bc3b2c88716b6f05b40cd96918efded1a81f776c5972dd8263b83e456967f6abdb68150c9e6cc8bb3a6ee7a7e3d3f1afab7e18fc3f8bc2f0d8bc9656d6fa9c36bbb5199211e64f3498621a41c305fba39380ab8c0e29ca6fd9bb77ff008739715154b438af8f9f0e1342d424f88fa45abc90aa8fedcb48933951f76ed476641f7bfd919e369cf8cfc65b68b5af86ba6ebe58cf2dab8f3844361dac58641ec082dcf5e33eb8fb6c804608e2b85f1bfc21f0febbf0f35af0ce93a3d869ff006e899e316f0ac43cd00ec3c0e076f604e2b08bb493feb6b1c74eb38c791ec7c3de09d6db5ab16b5b8565bcb255472dd655c70ff5c8c7e23d6b7c423b0e9d8564f84b4b874e9ae62fb1adbdda8f2ee9181deae8d8239e8339e38ed5bfe4e3a73c57439b5a1e953c35e372a7927206d1f8d38441b200f4ab262c7181c74a0464f0051ed342beaa5510119c8f7fad061039001ed567cb38e697cae3201147b40faa95c42a07031c5021cfd2acf94072076a5f2f39e471d7d28f681f552b888019048f5aeffe1be917ba9f87ec6db4fb39eee72f73208a142c76fdae6e4fa0cf73ea2b8a48433aa851d46315f467ec99a6187e1bff0069c83125d4eeab91fc01ddc7e66463574eb72de5e5faa39f114bd9c39883c27f0c3c4ebadc17d79a6dbc16e9192d1dd4ebfbc390429d9b881c7e95e81a7f815e15433df0428323ecd100558800fccd9dc38e322baaa2b9ab4fdb4b9a48e0f692e9fd6ffe66669fe19d2f4d7124368ad28e449293230fa139c7d0628f15ebd1785bc31aa6b732868ec2d24b92b9c6ed8a5b1f8e31f8d69d7987ed313eadff000ac5f4ed1b48bad56e352be82d1a182376214b6ec9dbc852caaa4f03e6a84ba1176dea57fd97b48bc83e1b8d7b55732ea5ae5c35d4f2b31666550234c9fa213ff02af56e3d2b33c25a043e15f0be97a1dbaa2c761691db8d8300ed5009fc719fc6b528b09eaee15e1df14ff66f7d42e67d6bc12d6f0dc48c5e6d2e73b61909ea636ff9664fa60afd2bdc68a69d8d29559d297341d8f8635ab5bef0bddb5a788b4abfd1ae146e22ea13b08c91912282a41c7b54704b05caee826825cf4f2e40c4fe00d7dcd736905ec261b98239a26ea9220653f81ae435af81bf0e7c41319aff00c1da4bc87ab470f947ff001cc52e58beb6febe47a94f36b6938fdc7c986da53922360bfee9e94c942db26e9d96240324bb6d007d4d7d2773fb2a7c379642f0d85fda83fc10deb851f81cd16ffb2cf80ad2412449a92b8e8de72ee1f43b7349435dcdff00b5a95be167987c00f069f13f8e6d352911fec9a681779208c9ff00966791c658657d4231e98cfd4d591e17f08e8fe0dd3cd8e8d662de266df23162ef2363ef3312493f53ed5af5a49ad1476478f89aeebcf9985145150607cbbfb48f8017c15e2a7f16da44cba36b0c0df90095b6b819fde1f4561927d304f001cf9b346508c80323208e411d883debeddd7343b0f11e9571a5ea56eb3dadc2ed74271f4208e841c1047422be7bd67f645d5b4eb894784f5fb586d33ba3827dc8a39271b02b283eebb47fb22abe25a6e7ad81c7c69c7d9d4d8f2600003d09c67d682a01fba07a577127ecf5f156ddd80d3348ba51d365d0427f361fcaaacdf04be29dbf27c1b0c83a7eeb528bfc6a3966bfe1d1ea2c5e165f68e3f8c738c01eb4f11824902ba74f83ff0013b383e06907fdbfc757ed3e057c4f9b04f85ed20079064d463247e468e598feb785fe64716b19e3031c50a99180b927dabd2f4cfd9a7e205fe4dddd687a628e80ee998fe5c575ba27eca364a51b5ff12df5e023f790dba8857db047f5069a84bab5fd7a194f30c2c7677f91e053890c82d6d943dec80ac7083c82470cdfdd51d4938000afaf7e0c68a9a07c2ed02c1036d8ed548665c3383f758fd5714be1cf83de0bf0bc491d96856ac5460b4ca1f71f520fcb9f702bafabba51b2d4f1f198b5886b955920a28a2a0e10a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2800a28a2803ffd9, 200, 67, 'image/jpeg');
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

From my script remove everything before the mysql_connect line, other than $id = $_GET['i'] (also the final curly brace too).
mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

Post by mdesouza »

Hi rich

still cant get it to work, It could be something that i am doing, this is the code u came me for show_image with the bits u asked me to take out, Yr code seems to have dissapered from the post, not sure why.

thks again

Mike



<html>
<head>
<title>Untitled web-page</title>
</head>
<body>
<?php
$id = false;
if (isset($_GET['i']))
{
$id = $_GET['i'];
}
mysql_connect('localhost', 'root', 'password') or die('Could not connect to MySQL');
mysql_select_db('mazdarx8');
$sql = "
SELECT
*
FROM
images
WHERE
id = '$id'
";
$result = mysql_query($sql);

$image_mime = mysql_result($result, 0, 'mime');
$image_data = mysql_result($result, 0, 'data');

Header("Content-type: $image_mime");
echo $image_data;

?>
</body>
</html>
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Whoa!! Dude you ADDED loads of html to my original script :) You should have left the script exactly as it was - not added HTML around it, that is what is breaking it. No wonder it didn't work :)

Try again ;)

Also - the reason my code has "vanished" is because you made that post with the binary data and it has pushed all the contents along to the right! scroll your browser window right.. or ideally please edit your post with the binary data and remove it.
mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

great stuff !!!

Post by mdesouza »

Hey rich

your the man !!
how stupid of me .. the script works perfect.

shame your script dissapered , cos it would have helped a few other developers


thks

Mike :D
mdesouza
Forum Newbie
Posts: 9
Joined: Thu May 20, 2004 6:14 am

can u repost

Post by mdesouza »

Rich

can u repost the scripts !

as its very useful

Thks

Mike
Post Reply