how to insert and retrieve picture from database in PHP

[khialbadshah]

Hi

I want to know how to insert picture and how to retrieve it from a table in PHP.
Please help me.. Thanks in advance.....

[diafol]

Hi

I want to know how to insert picture and how to retrieve it from a table in PHP.
Please help me.. Thanks in advance.....

PHP tables? that's a new one on me. Are you talking about MySQL or HTML?

[veedeoo]

@khialbadshah

You mean a BLOB? right? The cons outweighs the pros with this approach. Are sure you still want to go ahead? You will be exerting more memory than having the images stored in the directory (the conventional way). This method normally applies to images of high value copyrighted images.

[momo219]

You should consider storing the reference to the image in the database, but store the image on the filesystem

Storing images in the database is a bad idea in my opinion, keep the database slim!

[veedeoo]

@khialbadshah,

ok here it is I tested this and modify the script a little. This may not be suitable for production site without proper evaluation of the script's security vulnerability.

here we go let's have some fun it is Sunday night, so who cares :)

Step One: go to this site , and download the attached file, unzipped or untar..

Step Two: direct your browser to your phpMyAdmin, create a new database, name this database as test.

Import the following codes to the newly created "test" database above.

CREATE TABLE IF NOT EXISTS `images` (
  `id` int(11) NOT NULL auto_increment,
  `image` blob NOT NULL,
  `title` varchar(255) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`id`)
) 

Whatever you do or modified, this is the most important  [B]`image` blob NOT NULL,[/B] . It has to be like that and nothing else.

Step Three: With your favorite php editor, open the insert.php. Update the database credentials to your database credentials.

$username = "root";
$password = "bananaman";
$host = "localhost";
$database = "test";

, and then just mid-part of the page, find these codes about line 33 down to line 40

// Create the query and insert
// into our database.
$query = "INSERT INTO tbl_images ";
$query .= "(image) VALUES ('$data')";
$results = mysql_query($query, $link);

// Print results
print "Thank you, your file has been uploaded.";

change the above codes to these

// Create the query and insert
      // into our database.
      $query = "INSERT INTO images ";
      $query .= "(image) VALUES ('$data')";
      $results = mysql_query($query, $link);
$query = mysql_query("SELECT * FROM images ");
while($img_id  = mysql_fetch_array($query))
{
echo '<p><a href="show.php?id='.$img_id['id'].'">image '.$img_id['id'].'</a></p>';
	
}

save your changes above.

STep Four: open show.php. just like the insert.php above, we must update our database credentials. Please do so by providing your own database credentials, and the find on or about line 18

$query = mysql_query("SELECT * FROM tbl_images WHERE id='".$id."'");

change the above codes to this

$query = mysql_query("SELECT * FROM images WHERE id='".$id."'");

Upload the updated files along with the add.html to your xampp or server. Direct you favorite browser to add.html, upload an image having only the extension of jpeg. Once the upload is successful you will be redirected to insert.php, just follow the link to view the image saved in your database. To prove that your image is not coming from any directory, just right click in view image info. The source of the image should be something like this show.php?id=SomeId

[momo219]

@OP - how many images do you think the application will have? and what limitations are you considering on the filesize? Many / Any?

[cwarn23]

@khialbadshah

You mean a BLOB? right? The cons outweighs the pros with this approach. Are sure you still want to go ahead? You will be exerting more memory than having the images stored in the directory (the conventional way). This method normally applies to images of high value copyrighted images.

Agreed, as you all should know mysql loads itself into memory as a service called mysqld where all of the mysql_query's are processed through. However, whenever you do a mysql query, it needs to go into a que then get processed one at a time from that que. A bit like the line at a shopping market. However, the more mysql query's you use, the longer that que will become and the slower the page will take to load. Also if you have a too longer que then the mysql service will crash or in the case of a shopping market the till runs out of change. So if for instance you have a page with 20 pictures and you have 30 people viewing the page within 10 seconds that is 600 mysql query's that the mysql service has the process. All a hacker has to do is load the same page in a few tabs a few times during the same second and the mysql service will crash with the server slowing down.