storing images in myql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Denisem
Forum Newbie
Posts: 20
Joined: Sat May 04, 2002 8:48 am
Location: VA

storing images in myql

Post by Denisem »

Can anyone tell me how I store images in a mysql table? I have a jpg image that I need to call for a shopping cart for example. how do I store and then retrieve it?

Thanks,
Denise :roll:
dusty
Forum Contributor
Posts: 122
Joined: Sun Apr 28, 2002 9:52 pm
Location: Portsmouth, VA

Post by dusty »

storing images in mysql isn't recommended. you're better off storing the pictures on the server and linking to them in the db.
User avatar
sam
Forum Contributor
Posts: 217
Joined: Thu Apr 18, 2002 11:11 pm
Location: Northern California
Contact:

Post by sam »

First you have to have a blob table in the database...
to add the image from an upload...

Code: Select all

$file = $HTTP_POST_FILESї'userfile']ї'tmp_name'];
$fp = fopen($file,"r");
$raw = fread($fp,filesize($file));
fclose($fp);

$sql = "INSERT INTO images VALUE('".$file."','".$raw."')";
if(mysql_query($sql)){
   echo "Image ".$file." was inserted into the database";
}
Next it is a matter of retreving the image...

Code: Select all

$sql = "SELECT blob_field FROM images WHERE name='".$name."'";
$result = mysql_query($sql);
list($raw) = mysql_fetch_assoc($result);
mysql_free_result($result);

header("content-type: image/gif
");
echo $raw;
And that should do it for you... Dusty is correct there are very few reasons to store images in a database (unless your oracle then you can do what ever you want, theresite is made entirely with a database, including the coding all done with 9i).

Cheers Moe :mrgreen:
Denisem
Forum Newbie
Posts: 20
Joined: Sat May 04, 2002 8:48 am
Location: VA

images on server

Post by Denisem »

Wow - you guys are awesome. I am a newbie, so bare with me here:-)

Do I need a separate "blob" table, or can this be incorporated into my product table? Do I then need some form to input files into the blob table or am I better off doing something else?
Denisem
Forum Newbie
Posts: 20
Joined: Sat May 04, 2002 8:48 am
Location: VA

images in mysql

Post by Denisem »

dusty wrote:storing images in mysql isn't recommended. you're better off storing the pictures on the server and linking to them in the db.
Dusty,

This sounds liek an easier solution but how exactly would I go about doing that? For example, in my products table I have a column called image. Could I just store the image name there i.e. picture.jpg?

If so, how do I enter the info into the table? I tried using the regular insert into table, but got an error. I assume it was with the picture.jpg line.

Denise
dusty
Forum Contributor
Posts: 122
Joined: Sun Apr 28, 2002 9:52 pm
Location: Portsmouth, VA

Post by dusty »

enter the path/to/image into the image column.

Code: Select all

if(copy($image, "$dir/" . $image_name)) {
  mysql_query("INSERT INTO table VALUES('$image_name');
  unlink($image);
}
is a little snipplet you can work with.
the copy function is used to upload the image
the query inserts the image's name into the table
the unlink deletes the temp file

you'll probably want to make some additions such as check if the file exists and what not before the copy().
Denisem
Forum Newbie
Posts: 20
Joined: Sat May 04, 2002 8:48 am
Location: VA

images in mysql

Post by Denisem »

dusty wrote:enter the path/to/image into the image column.
Dusty - I can't even sucessfully enter the path into the table. I get "you have an error in your syntax, etc.

If i type, insert into table products values (NULL, "Diamond ring", "1 carat ring", 2200.00, "picture");
the data is inserted. However, the "picture" data is totally fictitious.

Yet, if I enter - insert into table products values (NULL, "Diamond ring", "1 carat ring", 2200.00, "picture.jpg"); I get an error. Seems like it doesn't like the jpg extension.

Any ideas on what I am doing wrong? I know it must be something real stupid:-)[/quote]
dusty
Forum Contributor
Posts: 122
Joined: Sun Apr 28, 2002 9:52 pm
Location: Portsmouth, VA

Post by dusty »

everything seems fine to me, could you post a little more of your code and give the exact error.
User avatar
sam
Forum Contributor
Posts: 217
Joined: Thu Apr 18, 2002 11:11 pm
Location: Northern California
Contact:

Post by sam »

Code: Select all

mysql_query("INSERT INTO table VALUES('$image_name'); you forgot to
close the mysql_query funciton...

Code: Select all

mysql_query("INSERT INTO table VALUES('$image_name')");
And Denisem use single quotes ' inside of the mysql query and double quotes " for the mysql function...

Cheers Sam
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

Just a reminder. If MySQL isn't working, you can easily get the reason why using mysql_error();

Code: Select all

echo mysql_error();
Denisem
Forum Newbie
Posts: 20
Joined: Sat May 04, 2002 8:48 am
Location: VA

Post by Denisem »

dusty wrote:everything seems fine to me, could you post a little more of your code and give the exact error.
Ok - you guys are truly awesome.

I have entered the image paths into the table,but am unable to pull them up and view. This is the code I am using.

<?

@ $db = mysql_pconnect("localhost", "em680a10", "password");

if (!$db)
{
echo "Error: Could not connect to database. Please try again later. ";
exit;
}


mysql_select_db("em680a10");

$query = "select * from products";

$result = mysql_query($query);

$num_results = mysql_num_rows($result);

echo "<P> <strong> Number of entries in products: </strong>

".$num_results."</p>";

for($i=0; $i<$num_results; $i++)

{

$row = mysql_fetch_array($result);

echo "<P><strong>Name : </strong>";

echo htmlspecialchars( stripslashes($row["name"]));

echo "<br><strong> Description: </strong> ";

echo htmlspecialchars (stripslashes($row["description"]));

echo "<br><strong> Price: </strong> ";

echo htmlspecialchars (stripslashes($row["price"]));

echo "<br><strong>Image: </strong>";

echo htmlspecialchars (stripslashes($row["image"]));

echo "</p>";

}

?>
User avatar
gotDNS
Forum Contributor
Posts: 217
Joined: Tue May 07, 2002 5:53 pm
Location: West Chester, PA

suggestion

Post by gotDNS »

don't put images in a DB, just put the path(s) in a table and call them where needed.
dusty
Forum Contributor
Posts: 122
Joined: Sun Apr 28, 2002 9:52 pm
Location: Portsmouth, VA

Post by dusty »

already stated :?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

only for marking as noticed and then forget about it: use
bool move_uploaded_file ( string filename, string destination)
instead of copy
Post Reply