Go4Expert - storing images in database

Go4Expert (http://www.go4expert.com/)

- MySQL (http://www.go4expert.com/forumdisplay.php?f=42)

- - storing images in database (http://www.go4expert.com/showthread.php?t=26536)

storing images in database

hi all,
i want to store an image in MYSQL database.please tell me whether the below sql syntax i have written is correct or not.

Code:

CREATE TABLE IF NOT EXISTS `products`(

  `serial` int(11) NOT NULL auto_increment,

  `name` varchar(20) collate latin1_general_ci NOT NULL,

  `description` varchar(255) collate latin1_general_ci NOT NULL,

  `price` float NOT NULL,

  `picture` varchar(80) collate latin1_general_ci NOT NULL,

  PRIMARY KEY(`serial`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7;

and i have inserted the values for the above table also.
below is the one.....

Code:

INSERT INTO `products` (`serial`, `name`, `description`, `price`, `picture`) VALUES

(1, 'View Sonic LCD', '19" View Sonic Black LCD, with 10 months warranty', 250, 'images/lcd.jpg'),

(2, 'IBM CDROM Drive', 'IBM CDROM Drive', 80, 'images/cdrom-drive.jpg'),

(3, 'Laptop Charger', 'Dell Laptop Charger with 6 months warranty', 50, 'images/charger.jpg'),

(4, 'Seagate Hard Drive', '80 GB Seagate Hard Drive in 10 months warranty', 40, 'images/hard-drive.jpg'),

(5, 'Atech Mouse', 'Black colored laser mouse. No warranty', 5, 'images/mouse.jpg'),

(6, 'Nokia 5800', 'Nokia 5800 XpressMusic is a mobile device with 3.2" widescreen display brings photos, video clips and web content to life', 299, 'images/mobile.jpg');

the "picture" field stores pictures in database.i have given there VARCHAR(80)
i know for storing pictures we should give BLOB data type.
but tell me whether the above syntax i have written will work or not.
if not what would be the syntax...

Re: storing images in database

No varchar(80) expects a character length of only 80. If the images character length is only 80 then yes but in most cases it will not be and will not work. Instead go with BLOB if you absolutely need to store them in the database. Its a best practice to store them in the filesystem so your not using up resources when your performing the query. A query for a fixed text length is far less on memory and the processor then one for an image files. I'd suggest that you store the link to the image in file or the directory and the file name is different columns.

Re: storing images in database

I agree with pein87.

Re: storing images in database

@ravi951 u can do as per ur wish .. using the varchar datatype for storing the image is not a prob.
But as Pein87 said it should not exceed the bound.

here is the code :

image.php

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=utf-8" />

<title>Image</title>

</head>



<body>

<FORM action="nexttpage.php" METHOD="POST" ENCTYPE="multipart/form-data">

  <P>Please browse and double-click the file to upload. Your filename must have one of the following extensions: .doc, .pdf, .ppt, .pps, .xls, .csv, .rtf, .txt, .htm, .html, .jpg, .gif, .png</p>

  <P>File name: <br><INPUT TYPE="file" name="userfile" style="width:250px;"></p>

  <P><INPUT  TYPE="submit" VALUE="Upload File"></p>



<INPUT class="inputform" TYPE=hidden name="MAX_FILE_SIZE" value="513024">



</FORM>



<div >

<form action="retrieve.php" method="post" enctype="multipart/form-data">

<h4>Enter the serial no to open the image </h4><input type="text" id="txt1" name="txt1" /><input type="submit" value="View" name="btn" />

</form>

</div>



</body>

</html>

nexttpage.php

Code:

<?php

include_once('connection.php');

ob_start();

// In PHP versions earlier than 4.1.0, $HTTP_POST_FILES should be used instead

// of $_FILES.



$uploaddir = 'C:\xampp\htdocs\sir\imagess/';

$uploadfile = $uploaddir . basename($_FILES['userfile']['name']);



echo '<pre>';

if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile)) 

        {

        

                $sqlst= "INSERT INTO  `college`.`images` (`img`)VALUES( '$uploadfile')";

                $chk=mysql_query($sqlst);

                if($chk)

                        {        

                                echo "data inserted";

                                echo "File is valid, and was successfully uploaded.\n";        

                                

                                header('location:image.php');

                        }

} else 

    echo "Possible file upload attack!\n";





//echo 'Here is some more debugging info:';

//print_r($_FILES);



//print "</pre>";



?>

Write the connection.php for ur connection
Hope i was able to help u out .
enjoy coding .. :)