I want to add a column in my table for a user's profile pic

Question

I want to add a extra field in my database table users.

The table is currently like this:

CREATE TABLE users (  
  user_id     INT(8) NOT NULL AUTO_INCREMENT,  
  user_name   VARCHAR(30) NOT NULL,  
  user_pass   VARCHAR(255) NOT NULL,  
  user_email  VARCHAR(255) NOT NULL,  
  user_date   DATETIME NOT NULL,  
  user_level  INT(8) NOT NULL,  
  UNIQUE INDEX user_name_unique (user_name),  
  PRIMARY KEY (user_id)  
);

How will it look if I add a column for profile pic data of the user?

Thanks!

Answer 1

Basically, there's 2 options:

1. store pic-data in database
2. store picture-locations in database and picture itself on filesystem (picture location points to location on filesyste,

Option 2. is generally preferred. In that case your table becomes:

CREATE TABLE users (  
user_id     INT(8) NOT NULL AUTO_INCREMENT,  
user_name   VARCHAR(30) NOT NULL,  
user_pass   VARCHAR(255) NOT NULL,  
user_email  VARCHAR(255) NOT NULL,  
user_date   DATETIME NOT NULL,  
user_level  INT(8) NOT NULL,  
pic_location  VARCHAR(255) NOT NULL,  
UNIQUE INDEX user_name_unique (user_name),  
PRIMARY KEY (user_id)  
);

Some suggestions for the future:

• 1. Please search a bit first before asking this. Store pictures as files or in the database for a web app?
• 2. Make your question-header a question. This leads more people to actually wanting to answer you question, instead of having to click through before knowing what you want to accomplish.

Cheers, Geert-Jan

Answer 2

If you need to store the photo in the table, you need

ALTER TABLE users ADD COLUMN Photo IMAGE;