MySQL table design to upload image

Codes here !

Moderators: egami, macek, gesf

Post Reply
User avatar
New php-forum User
New php-forum User
Posts: 40
Joined: Thu Jan 23, 2003 5:15 pm
Location: Malaysia

Thu Jul 17, 2003 12:00 am

Hi guys;

I've completed my website using php+mysql. My MYSQL table (tbluser) design is as follow:

id: int(11) auto_increment
nama: varchar(100)
org: varchar(100)
email: varchar(50)
username: varchar(10)
pass: varchar(10)
picture: mediumblob <--newly added

Currently I've already got 500+ records(user) in MySQL and 'picture' column is empty. Now I'd like to let the users to upload their picture into the 'picture' column. My questions are:

1)Is it possible to upload to 'picture' column where 'id' column is already occupied?

2)Do I have to create another table to store the picture separately and how do I link user data in 'tbluser' table with newly created 'pict' table so that user data can be linked to their picture respectively.

Any advice is appreciated
Thanks and regards;

User avatar
Posts: 2257
Joined: Sat Jul 05, 2003 1:18 pm
Location: A bunk in the back

Thu Jul 17, 2003 6:55 am

wmdrumaizi wrote:1)Is it possible to upload to 'picture' column where 'id' column is already occupied?

Yes. Use MySQL's UPDATE statement.

wmdrumaizi wrote:2)Do I have to create another table to store the picture separately...

No, you don't, unless you want each user to be able to upload multiple pictures.

As a side note, it's generally discouraged to use MySQL for file storage, especially if you anticipate having many concurrent connections. Simply saving them as files in the filesystem tends to be more efficient. In this case, you'd just give each picture a unique filename and store the filename in the database.
Last edited by swirlee on Thu Jul 17, 2003 10:56 am, edited 1 time in total.

New php-forum User
New php-forum User
Posts: 191
Joined: Wed Jan 29, 2003 7:11 am
Location: UK

Thu Jul 17, 2003 10:39 am

as swirlee mentions, inserting the image binaries into the database is rarely useful and more often an unneccessary drain on connections.
remember that each image has to be called as a new http request and thus new db connection (unless all images are under 1KB in which case you can output the binary text into an img tag [with some subtle tricks])

As you have an auto-inc id field, I would advise using that to name the uploaded image and just deduce the extension during the upload script.

One idea might be to just store the image extension as your 'picture' field - maybe with 'no' as a null alternative.

if($row['picture'] !== 'no' && file_exists('folder/' .$row['id'].$row['picture']))
echo '<img src="folder/' .$row['id'].$row['picture']. " />';

if id holds 234 and the picture field holds ".png" then 234.png is output, if it holds ".jpg" then 234.jpg is output, if it holds "no" then no image shown.
avatar image based upon nasas apod (only updates if I post though)

New php-forum User
New php-forum User
Posts: 40
Joined: Fri Jun 27, 2003 10:57 pm

Sun Jul 20, 2003 10:03 pm

Hi swirlee,

First of all, thank you very much for answering my previous 2 questions on this forum. I came across this thread and I actually posted a question before of wether to store files in the filesystem or database and someone actually suggested me that it is always better to store files in the database for few reasons. After my search and advice through the net, I found out the following:

Advantage of storing files in database:
1) security is enhanced
2) file manipulation (search, editing, deletion in a database is
faster and we do not have to write any functions in php for
this since the database has all these functions)

Disadvantage of it:
1) very slow in adding new data

By the way, the files that I intended to store is xml files. And additionally I would store pictures a well.

Any advice from anyone is much appreciated.


Post Reply