Page 1 of 1

variable number of files to be saved in MySQL

Posted: Thu Sep 13, 2007 12:46 pm
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

Posted: Thu Sep 13, 2007 12:53 pm
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.

Posted: Thu Sep 13, 2007 1:00 pm
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

Posted: Thu Sep 13, 2007 3:13 pm
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.

Posted: Thu Sep 13, 2007 5:14 pm
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

Posted: Thu Sep 13, 2007 6:50 pm
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());
}

Posted: Thu Sep 13, 2007 7:52 pm
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.

Posted: Fri Sep 14, 2007 10:46 am
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