Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Storing Images in MySQL with PHP

mysql

  • Please log in to reply
4 replies to this topic

#1 Tongleang

Tongleang

    CC Lurker

  • New Member
  • Pip
  • 4 posts
  • Programming Language:C
  • Learning:C

Posted 11 October 2012 - 11:01 PM

Hi.
I want to show image on browser. I have code below:
*** Create database and table**
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// Create database
if (mysql_query("CREATE DATABASE my_db",$con))
{
echo "Database created";
}
else
{
echo "Error creating database: " . mysql_error();
}

// Create table
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE tbl_image
(
	id tinyint(3) unsigned NOT NULL auto_increment,
	image blob NOT NULL,
	PRIMARY KEY (id)
)";
// Execute query
mysql_query($sql,$con);

mysql_close($con);
?>
***File insert.php**
<?php
// Create MySQL login values and
// set them to your login information.
$username = "root";
$password = "";
$host = "localhost";
$database = "my_db";

// Make the connect to MySQL or die
// and display an error.
$link = mysql_connect($host, $username, $password);
if (!$link) {
		die('Could not connect: ' . mysql_error());
}

// Select your database
mysql_select_db ($database);

// Make sure the user actually
// selected and uploaded a file
if (isset($_FILES['image']) && $_FILES['image']['size'] > 0) {

		 // Temporary file name stored on the server
		 $tmpName = $_FILES['image']['tmp_name'];
		
		 // Read the file
		 $fp	 = fopen($tmpName, 'r');
		 $data = fread($fp, filesize($tmpName));
		 $data = addslashes($data);
		 fclose($fp);
		

		 // Create the query and insert
		 // into our database.
		 $query = "INSERT INTO tbl_image ";
		 $query .= "(image) VALUES ('$data')";
		 $results = mysql_query($query, $link);
		
		 // Print results
		 print "Thank you, your file has been uploaded.";
		
}
else {
print "No image selected/uploaded";
}

// Close our MySQL Link
mysql_close($link);
?>

*****file add.html**
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[url="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd%22>"]http://www.w3.org/TR...nsitional.dtd">[/url]
<html xmlns="[url="http://www.w3.org/1999/xhtml%22>"]http://www.w3.org/1999/xhtml">[/url]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
	<form enctype="multipart/form-data" action="insert.php" method="post" name="changer">
	<input name="MAX_FILE_SIZE" value="102400" type="hidden">
	<input name="image" accept="image/jpeg" type="file">
	<input value="Submit" type="submit">
</body>
</html>

*****file showimage.php****
<?php

$username = "root";
$password = "";
$host = "localhost";
$database = "my_db";

mysql_connect($host, $username, $password) or die("Can not connect to database: ".mysql_error());

mysql_select_db($database) or die("Can not select the database: ".mysql_error());

$id = $_GET['id'];

if(!isset($id) || empty($id) || !is_int($id)){
		 die("Please select your image!");
}else{

$query = mysql_query("SELECT * FROM tbl_image WHERE id='".$id."'");
$row = mysql_fetch_array($query);
$content = $row['image'];

header('Content-type: image/jpeg');
		 echo $content;
}
?>
When I run it. It's show error message as below.
Notice:Undefined index: id in D:\PHP\PHP Learn By Myselt\showimage.php on line 12

How to reslove this problem. Please help me.
  • 0

#2 lintwurm

lintwurm

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 261 posts
  • Programming Language:C, Java, C++, C#, PHP, (Visual) Basic, Python, Visual Basic .NET

Posted 12 October 2012 - 12:50 AM

You don't send the id variable in the GET part, meaning that that index is not valid.
You need to send the ID of the picture through to this script.
My suggestion for a workaround is sending the id as part of the user session. That way you can get the ID by using
$id = $_SESSION['id'];

BUT you HAVE to set the id in the session. Otherwise the exact same problem will remain.

TL;DR: You didn't set the variable id in the POST part of the request. (Atleast that is what I think, but I could be wrong. Haven't worked in PHP in a while.)
  • 0
No harm was done to the world in sending this message, but a few million electrons have been SERIOUSLY inconvenienced...

#3 Colanth

Colanth

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 165 posts
  • Location:Fayetteville, NC, USA
  • Programming Language:PHP, (Visual) Basic, JavaScript, Visual Basic .NET, Pascal, Logo, Assembly, VBScript, Others

Posted 14 October 2012 - 04:30 PM

And aside from that, NEVER store images in the database. Store the image as a file. Store the path and name of the file in the database. As good as MySQL is, you're crippling it by storing large BLOBs, compared to storing char strings.
  • 0

#4 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts
  • Location:Quebec City
  • Programming Language:Java, C++, C#, PHP, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 14 October 2012 - 05:04 PM

And aside from that, NEVER store images in the database. Store the image as a file. Store the path and name of the file in the database. As good as MySQL is, you're crippling it by storing large BLOBs, compared to storing char strings.


Can you explain why you say this?
The overhead of using blob is not as much as people think.
There is advantage of using blob... for replication, easier backup (only backup 1 file), security against ftp hack, maybe he can't change the directory permission so can't write on it and of course transactions. What if you delete a record on the database then delete the file but you don't have the permission or something went wrong... it's hard to rollback unless you store it in the database.

There are pro and cons on both technics, you have to decide what is the best for your system... but don't say NEVER store image in the database without explaining yourself.
  • 0

You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#5 Colanth

Colanth

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 165 posts
  • Location:Fayetteville, NC, USA
  • Programming Language:PHP, (Visual) Basic, JavaScript, Visual Basic .NET, Pascal, Logo, Assembly, VBScript, Others

Posted 15 October 2012 - 12:05 PM


And aside from that, NEVER store images in the database. Store the image as a file. Store the path and name of the file in the database. As good as MySQL is, you're crippling it by storing large BLOBs, compared to storing char strings.


Can you explain why you say this?
The overhead of using blob is not as much as people think.

That depends on the database, the connection method, the file size, many things. But it's always many times the overhead of retrieving a filename.

There is advantage of using blob... for replication

Trivial to replicate a directory tree.

easier backup (only backup 1 file)

Terrible idea. The entire server should be incrementally backed up.

security against ftp hack

Move the files out of the FTP path.

maybe he can't change the directory permission so can't write on it

If he can't create a directory he can write to he shouldn't be mucking around in the server.

and of course transactions.

Why do you need a transaction to open and read a file? You can still do a transaction on creating, saving, updating and selecting the filename.

What if you delete a record on the database then delete the file but you don't have the permission or something went wrong.

Rollback.

it's hard to rollback unless you store it in the database.

You roll the deletion of the filename back, you don't roll the deletion of the file back (since it didn't get deleted).

There are pro and cons on both technics

But the cons almost always outweigh the pros.

In theory, theory and practice are the same. In practice they're different.
  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download