MySQL Image insert

Question

G'day I was just wondering how I could insert an image to a database. I've created a table that has already existing data for every column, and I've added an "Image" column, how would I insert an image to a particular row?

Read This before you go ahead : http://stackoverflow.com/questions/5613898/what-about-storing-images-in-sql-server — wcraft, Feb 01 '13 at 02:00

score 2 · Answer 1 · answered Feb 01 '13 at 01:42

2

You do not want to do it this way. Rather, use the filesystem for what it's good for -- storing data. Have the names for the image and what they correspond to in your SQL tables.

answered Feb 01 '13 at 01:42

hd1

33,938
5
80
91

Bruno Croys Felthes · Answer 2 · 2013-02-01T02:23:32.273

0

Try this code:

CREATE TABLE Employees
(
  Id int,
  Name varchar(50) not null,
  Photo varbinary(max) not null
)


INSERT INTO Employees (Id, Name, Photo) 
  SELECT 10, 'John', BulkColumn 
  FROM Openrowset( Bulk 'C:\photo.bmp', Single_Blob) as EmployeePicture

edited Feb 01 '13 at 02:23

answered Feb 01 '13 at 01:50

Bruno Croys Felthes

1,183
8
27

What do I write after FROM? – JamsHack Feb 01 '13 at 02:05
`OPENROWSET(BULK N'C:\Image\image.jpg', SINGLE_BLOB) AS import` . All the 3 lines can be at one line... you just need to change the image path `C:\Image\image.jpg` – Bruno Croys Felthes Feb 01 '13 at 02:07
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(BULK N'E:\TheClash.jpg', SINGLE_BLOB)' at line 3 – JamsHack Feb 01 '13 at 02:07
I didnt test, try this and say me if it works: `INSERT INTO YourTable (YourVarbinaryColumn) VALUES ((SELECT * FROM OPENROWSET(BULK 'E:\Imagem.jpg', SINGLE_BLOB) AS A))` – Bruno Croys Felthes Feb 01 '13 at 02:13
Still the same error. Does is matter if the column is set to 'Blob'? – JamsHack Feb 01 '13 at 02:16
I found another question like your: http://stackoverflow.com/questions/416881/insert-picture-into-sql-server-2005-image-field-using-only-sql look at the correct response... try it... – Bruno Croys Felthes Feb 01 '13 at 02:22
I've found a way in PHP MyAdmin to upload an image using a form. But thank you anyway. – JamsHack Feb 01 '13 at 04:53
Great, so, post your answer and mark it as the best answer :D – Bruno Croys Felthes Feb 01 '13 at 11:08

score 0 · Answer 3 · answered Feb 01 '13 at 01:51

0

Here's the PHP way:

$fileblob =file_get_contents("path/to/imagefile");
$cleandata=addslashes($fileblob);


$sql="update mytable set blobcolumn='$cleandata' where pkcol=$mypkvalue";

mysql_query($sql);

answered Feb 01 '13 at 01:51

Jimzie

737
5
6

score 0 · Answer 4 · answered Feb 01 '13 at 01:53

hd1 has a good point; the filesystem is better at handling image objects - and it will be faster for users to retrieve and cache the object.

However to answer it you need to store it in a blob ddatatype. (Binary large object). Remember to escape the source before importing it into your database, and un-escaping it on retrieval.

INSERT INTO table (blobcolumn) VALUES (...blob contents here...);

MySQL Image insert

4 Answers4