Page 1 of 1
storing images in myql
Posted: Sat May 04, 2002 8:48 am
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

Posted: Sat May 04, 2002 10:48 am
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.
Posted: Sat May 04, 2002 1:36 pm
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

images on server
Posted: Sat May 04, 2002 3:42 pm
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?
images in mysql
Posted: Sat May 04, 2002 4:35 pm
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
Posted: Sat May 04, 2002 5:16 pm
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().
images in mysql
Posted: Sat May 04, 2002 6:13 pm
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]
Posted: Sat May 04, 2002 7:12 pm
by dusty
everything seems fine to me, could you post a little more of your code and give the exact error.
Posted: Sat May 04, 2002 7:34 pm
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
Posted: Sat May 04, 2002 9:13 pm
by jason
Just a reminder. If MySQL isn't working, you can easily get the reason why using mysql_error();
Posted: Sat May 04, 2002 10:08 pm
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>";
}
?>
suggestion
Posted: Fri May 10, 2002 4:31 pm
by gotDNS
don't put images in a DB, just put the path(s) in a table and call them where needed.
Posted: Fri May 10, 2002 8:03 pm
by dusty
already stated

Posted: Mon May 13, 2002 10:39 pm
by volka
only for marking as noticed and then forget about it: use
bool move_uploaded_file ( string filename, string destination)
instead of copy