variable number of files to be saved in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nayeemmz
Forum Commoner
Posts: 32
Joined: Fri Jun 29, 2007 7:19 pm

variable number of files to be saved in MySQL

Post by nayeemmz »

Hi,

I am saving a file in the database and it is working.

I want to save multiple files in the database. The catch is that I could have any number of files between 1 and 9 to be saved depending on the user's choice.
Since I have a single table where I want to save all these files so obviously the table needs to have 9 columns . However, some of those columns could be null .
Let me describe using an example:

User A choses the number of files to be uploaded = 2
Therefore, in the database I need to save only 2 files with 7 other columns null.

Another user B chooses to upload 5 files
Therefore in the database I will need to save 5 files with 4 other columns null

What is the best way to do it?

I know I could do it using if else statements based on the number of files to be uploaded, but I wanted to do it in a better way.

Some help/pointers would be appreciated.

Thanks

-Nayeem
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

The best way to accomplish to normalize your database, therfore you would end up something similiar to
table: documents

id | user_id | document_name | path
Basically, you simple enter as many documents as you wish belonging to the user by associated the user_id with his database.

Moved to Databases.
nayeemmz
Forum Commoner
Posts: 32
Joined: Fri Jun 29, 2007 7:19 pm

Post by nayeemmz »

Thanks.

However I don't understand how to achieve that.

Do I need to do anything to move this post to the databases
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

What he means is that instead of a user event of uploading, say 4 files in two different events being stored as this:

Code: Select all

dave | file1 | file2| file3| null | null | null | null | null | null | null 
dave | file4 | null | null | null | null | null | null | null | null | null
You store it as this:

Code: Select all

32 | dave | file1
33 | dave | file2
34 | dave | file3
35 | dave | file4
the 32, 33, etc on the second example are just an artificial primary key, they have no other meaning.
nayeemmz
Forum Commoner
Posts: 32
Joined: Fri Jun 29, 2007 7:19 pm

Post by nayeemmz »

Hi,
Thanks.

Oh I can do that but current my question is slightly different. I am talking about two different users

Code: Select all

dave | file1 | file2| file3| null | null | null | null | null | null | null
roger | file1 | file2 | null | null | null | null | null | null | null | null
Since dave and roger are uploading two different number of files, I am wondering how to insert a variable number of files into the database because in one

the database command would look something like

Code: Select all

insert into A values ('dave','file1',file2',file3','','','','','','')
And for roger it would have to look like

Code: Select all

insert into A values ('roger','file1',file2','','','','','','','')
Since there have to be two insert statements I wanted to know if there is a way to have a single insert statement to take care of all the cases.

Thanks for your help

-Nayeem
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I'm a bit confused, I thought you asked what is the best way to store the files (since you ignored our advice I'll assume I was incorrect). I still insist you change your table structure, however.
Since there have to be two insert statements I wanted to know if there is a way to have a single insert statement to take care of all the cases.
But still, using my proposed table structure you could easily loop your files and insert your filenames into the database.

Code: Select all

foreach ($file as $filename) {
   mysql_query('INSERT INTO files SET `username` = \''. $username .'\', filename = \''. $filename .'\'') or die(mysql_error());
}
User avatar
iknownothing
Forum Contributor
Posts: 337
Joined: Sun Dec 17, 2006 11:53 pm
Location: Sunshine Coast, Australia

Post by iknownothing »

nayeemmz,

With the way you are doing it, you could simply put variables into the VALUES section, if they are empty, then it will be inserted NULL.

eg.

Code: Select all

insert into A values ('roger','$file1','$file2','$file3','$file4','$file5','$file6','$file7','$file8','$file9')
However, what Jcart is showing you is how you really should be doing it, it will make INSERTING, SELECTING, DELETING and UPDATING of single files and/or all files for a user much easier in the future, not to mention users adding more than the available columns of files if possible.
nayeemmz
Forum Commoner
Posts: 32
Joined: Fri Jun 29, 2007 7:19 pm

Post by nayeemmz »

Thankyou Jcart and iknownothing,

I am sorry I didn't ignore your advice. It was my mistake may be I didn't put question right.
However, your approach seems to be much better and I shall try to design my table structure that way.

Thankyou very much all.

-Nayeem
Post Reply