variable number of files to be saved in MySQL
Moderator: General Moderators
variable number of files to be saved in MySQL
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
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
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
The best way to accomplish to normalize your database, therfore you would end up something similiar to
Moved to Databases.
Basically, you simple enter as many documents as you wish belonging to the user by associated the user_id with his database.table: documents
id | user_id | document_name | path
Moved to Databases.
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
What he means is that instead of a user event of uploading, say 4 files in two different events being stored as this:
You store it as this:
the 32, 33, etc on the second example are just an artificial primary key, they have no other meaning.
Code: Select all
dave | file1 | file2| file3| null | null | null | null | null | null | null
dave | file4 | null | null | null | null | null | null | null | null | nullCode: Select all
32 | dave | file1
33 | dave | file2
34 | dave | file3
35 | dave | file4Hi,
Thanks.
Oh I can do that but current my question is slightly different. I am talking about two different users
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
And for roger it would have to look like
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
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 | nullthe database command would look something like
Code: Select all
insert into A values ('dave','file1',file2',file3','','','','','','')Code: Select all
insert into A values ('roger','file1',file2','','','','','','','')Thanks for your help
-Nayeem
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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.
But still, using my proposed table structure you could easily loop your files and insert your filenames into the database.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.
Code: Select all
foreach ($file as $filename) {
mysql_query('INSERT INTO files SET `username` = \''. $username .'\', filename = \''. $filename .'\'') or die(mysql_error());
}- iknownothing
- Forum Contributor
- Posts: 337
- Joined: Sun Dec 17, 2006 11:53 pm
- Location: Sunshine Coast, Australia
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.
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.
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')