-1

I'm learning mysql and am having tremendous trouble with this code to build an image database....

I know how to create a table and I know I need longblob for images. Not a problem. Currently I'm creating via: CREATE TABLE pics ( picid int unsigned not null auto_increment primary key, filename varchar(255) not null unique, caption varchar(255) not null, pic longblob not null );

the "not null" in picid is giving me problems. Because next when I attempt to populate using this code: INSERT INTO pics values ( NULL, 'bear.jpg', 'a picture of a bear', LOAD_FILE('C:/Users/USERS_NAME/Pictures/bear.jpg') );

I get hit with the error #1048 - Column 'pic' cannot be null.

please help. I am losing my mind....

acegreene2
  • 21
  • 1
  • 2
  • 7
  • Why did you tag this with [tag:sql-server] ... the answer is you *shouldn't* be storing images in a database. – Kermit Jan 06 '14 at 21:53
  • the sql-server tag was suggested...I'm learning dude..I've been advised to use paths instead of just loading pics into the database – acegreene2 Jan 06 '14 at 21:55

2 Answers2

2

It's not the picid that's the problem. LOAD_FILE('C:/Users/USERS_NAME/Pictures/bear.jpg') most likely fails and returns NULL.

Not to mention, you shouldn't store images in a database. Images are files, and should be stored as such on the file system. The database should hold the metadata + the file's address in the filesystem.

See Effeciently storing user uploaded images on the file system for a good system to follow.

Community
  • 1
  • 1
Madara's Ghost
  • 172,118
  • 50
  • 264
  • 308
  • load_file should be returning that file though...the path isn't "empty". It is correct. – acegreene2 Jan 06 '14 at 21:56
  • @acegreene2: There's a very large gap between "should" and "is". Make sure LOAD_FILE is returning what you think it does. Also, please read the rest of the answer too. – Madara's Ghost Jan 06 '14 at 21:58
  • Thanks for the quick reply. the article you referred me to is a bit too advanced btree isn't something I have experience with. But my path uses forward slashes instead of backslashes (b/c backslashes are escape commands in mysql) should I just use double backslashes? – acegreene2 Jan 06 '14 at 22:01
  • what code/architecture would you use to build and populate this sort of database? – acegreene2 Jan 06 '14 at 22:02
0

if you still want to continue using the blob method try this tutorial http://forum.codecall.net/topic/40286-tutorial-storing-images-in-mysql-with-php/

Motxila dev
  • 99
  • 5